1

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,

Richard.

  • First, It more of a VBA question as compare to MS Access. – aasim.abdullah Oct 30 '14 at 14:28
3

Its happening because variable name is not correctly placed inside update code and as both column name and variable name are same so according to your current code you are updating all the records.

Try this

sqlcmd = "UPDATE 1_tbl_Memberships SET [1_tbl_Memberships].Member =
No WHERE [1_tbl_Memberships].GroupMembershipID = "& GroupMembershipID &""

http://msdn.microsoft.com/en-us/library/office/aa212163(v=office.11).aspx

  • Problem solved! Many thanks, and also for the hyperlink. Richard. – Richard Morris Oct 30 '14 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.