I am having trouble with an MS-Access VBA sql update command. I have a form which has a combobox of GroupMembershipIDs, and a button. I am trying to write code which will iterate through the combobox and update the 'Member' field (Yes/No) for all the selected records.
Here is my code:
Private Sub btnRemoveFromGroup_Click() Dim stDocName As String Dim VarItem As Variant Dim sqlcmd As String For Each VarItem In Me.List12.ItemsSelected GroupMembershipID = Me.List12.Column(0, VarItem) MsgBox GroupMembershipID 'That is OK sqlcmd = "UPDATE 1_tbl_Memberships SET [1_tbl_Memberships].Member = No WHERE ([1_tbl_Memberships].GroupMembershipID = GroupMembershipID)" 'Something not right here DoCmd.SetWarnings True MsgBox sqlcmd 'This looks OK DoCmd.RunSQL sqlcmd Next VarItem DoCmd.SetWarnings False Me.List12.Requery End Sub
I have put in a couple of message boxes to help me troubleshoot. The error I get is that the 'where' clause does not seem to be working. When I run this code, I am warned that I am about to update xxx number of records (ie all of them), as if the where criteria is being ignored.
Could someone point me in the right direction? - Where am I going wrong!
Thanks in advance,