Run update Query based off listbox selection

[Montez659]

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.

 

[revans]

Something like this should work

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

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

Or something like that.

hth,

Rich

 

[Montez659]

Rich,

It looks like your code is good and should work, but I am getting a type mismatch on

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.

 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.

 

[Montez659]

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!