Double Click Listbox Record to Run Update Query on selected Record

[mikerea90]

Hey all,

Does anyone know how to run an update query on a specific record in a list box upon double-clicking on that record?

Thanks

 

[pbaldy]

Presuming it's single select and the bound column of the listbox is a numeric ID field:

CurrentDb.Execute "UPDATE...WHERE IDField = " & Me.ListboxName

 

[mikerea90]

Okay so I have this:

Private Sub List5_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "QRY_TRANSACTION_INDA"
stLinkCriteria = "[ItemID] = " & Me.List5.Column(0)
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

But what should I put in the update query to insure only the double-clicked record is updated, not everything in the list box?

 

[pbaldy]

That bears no resemblance to what I posted, and you can't use OpenForm to run an action query. I guess the direct answer to your question is that you'd put a reference to the listbox in the criteria.

 

[mikerea90]

My apologies, I was just trying to show you what I was working on, but if you say that that does not work, let me try to do it your way. I appreciate your help. Here is the code for that:

Private Sub List5_DblClick(Cancel As Integer)

CurrentDb.Execute SingleMove

SingleMove = Update TBL_FFE Where "[ItemID] = " &Me.List5.Column(0)

End Sub

 

[mikerea90]

I am getting errors, I have looked for an example for this code, but I have not used this code before. Thanks.

 

[pbaldy]

You can use your saved query if you want, after adding the criteria. You would have to use DoCmd.OpenQuery instead of CurrentDb.Execute. The problem with what you have there is no SET clause. The syntax of an UPDATE query is:

UPDATE TableName
SET FieldName = DesiredValue
WHERE FieldName = Criteria

You need to add the SET part of that.

 

[mikerea90]

I've changed it to the following, but I am still getting an error.

Private Sub List5_DblClick(Cancel As Integer)
CurrentDb.Execute
Update TBL_FFE
Set [Group] = "A"
Where "[ItemID] = " & Me.List5.Column(0)

End Sub

 

[pbaldy]

The quotes in my first post aren't optional. Sorry I missed that in your other post. Try this:

CurrentDb.Execute "Update TBL_FFE Set [Group] = 'A' Where [ItemID] = " & Me.List5.Column(0)

 

[mikerea90]

Alright great, that works. I just ran into a little problem though - if the user clicks on a part of the list box without a record (just empty space), an error appears asking if the user would like to stop the script because Access cannot find anything for [ItemID]. Is there anything I can add to this code to prevent this?

 

[pbaldy]

Personally I would eliminate the empty records from the listbox, but you could wrap the Execute in an If/Then:

If IsNumeric(Me.List5.Column(0)) Then

 

[mikerea90]

Works great. Thanks again for your help!

 

[mikerea90]

One more question - if I had a highlighted record in a listbox, could I click a button and cause the same update to occur?

 

[pbaldy]

No problemo, glad we got it sorted out.

 

[pbaldy]

One more question - if I had a highlighted record in a listbox, could I click a button and cause the same update to occur?

Sure, but again the listbox would have to be single select. If it's multiselect, you'd have to add code to go through the selected items.

 

[mikerea90]

How do you make it multiselect?

 

[mikerea90]

Haha nvm - I'll look for it.