MS Access VBA – SQL Update Where Clause Not Working

ms access

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.

Best Answer

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

Related Solutions

VBA for MS Access – How to Maximize Access Application Main Window

I've spend a while battling with this problem too, reading various blogs and posts. I've found a very simple way to achieve the desired outcome in VBA using just one line of code. In the VBA editor, place this code AFTER your On Load code:

[Your On Load code]

KeyCode = 16

This code achieves the equivalent of pressing F11, ie maximising the view of the currently open window.

VBA Access – Efficiently Replacing Values in Large Lists

I think I can guess why your VBA is only changing around 150 records instead of all of them, but more importantly, you need to know that the specific method you're using--changing all the fields using VBA--can lead to incorrect data in your table. And, you can accomplish what you want much more easily with a SQL query ... even one made using the query designer. But for now I'll stick with plain SQL. There are three possibilities and corresponding approaches:

1 You just have to change a limited number of different sizes from mm to European. In that case use the switch function, e.g.:

update BS_2704
set Sizes = switch(
  Sizes = 150, 24
, Sizes = 160, 25
, ...
, Sizes = 325, 50
, true, Sizes
)

2 You can use a mathematical formula to convert from your mm size to your European measure, e.g. like http://en.wikipedia.org/wiki/Shoe_size#Europe:

update BS_2704
set Sizes = Sizes * 3 / 20

3 You can't use a mathematical formula, and you have such a large number of conversion mappings that putting them all in a single switch function is unwieldy: in this case you create a new table, e.g. named tblSizeMappings, with the mm sizes and the corresponding European sizes:

SizeMM SizeEuropean
   150           24
   160           25
   325           50

Then, do an update query like above but using the tblSizeMappings table to get the European sizes:

update BS_2704
inner join tblSizeMappings
on BS_2704.Sizes = tblSizeMappings.SizeMM
set BS_2704.Sizes = tblSizeMappings.SizeEuropean

Best Answer

Related Solutions

VBA for MS Access – How to Maximize Access Application Main Window

VBA Access – Efficiently Replacing Values in Large Lists

Related Question