Run update Query based off listbox selection

Montez659

Well-known Member
Joined
May 4, 2005
Messages
918
Not sure if this is the correct route to go, but I shall explain.

I have a tbl of contacts, and each contact is either Included or Excluded (from reporting, not from my social life!) the Included/Excluded field is Yes/No. That table is broken down into two queries (probably only need one, but I do what I know how to do). Those queries populate a list box for each. When a user double clicks on a name in either box, I want it to drop from the current box and populate the other box. That part can be easily done by requerying. However, I am having trouble figuring out the syntax for the update query to change the record in the db.

I would prefer to do it via query, but VBA isn't a problem either. Just figured it to be easier with a query but I don't fully know the syntax.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Something like this should work

Code:
Public Sub UpdateTable(id As Long, IsExcluded As Boolean)
Dim sql As String
sql = "UPDATE MyTable SET Exclude = " & IsExcluded & " " & _
      "WHERE ContactID=" & id & ");"  '  [COLOR=red] <--- Change table and field names to suit
[/COLOR]CurrentDb.Execute sql
End Sub

You'd call it from behind each listbox's After Update event like this

Code:
Private Sub MyListBox_AfterUpdate()
Call UpdateTable(Me.MyListBox, True) ' <--- or False as the case may be
End Sub

Or something like that.

hth,

Rich
 
Upvote 0
Rich,

It looks like your code is good and should work, but I am getting a type mismatch on
Code:
Call UpdateTable(Me.lstIncluded, False)

I am not as well versed on the SQL string, although I can work with it, so maybe the problem is there.
Code:
 Public Sub UpdateTable(id As Long, IsExcluded As Boolean)
Dim sql As String
sql = "UPDATE Contacts SET Excluded = " & IsExcluded & " " & _
      "WHERE ID=" & id & ");"  '   
CurrentDb.Execute sql
End Sub
My table is Contacts, column is Excluded, and ID is primary.
 
Upvote 0
I think the error is coming from the fact that Me.lstIncluded returns a Name and not the id. Name is in second column but displays and id is in first column that is hidden.

EDIT: That is exactly where the problem was, and corrected by Me.lstIncluded.Column(0). Thanks for the help!

For anyone else that may stumble upon this, there is an extra parenth in his code. Remove that and it should work fine!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top