Reputation: 1129
I have a combo box which shows two bits of data, a name and it's unique ID number. I have set it up so there are two columns displayed when the user clicks on it prior to selecting the record. I am trying to source the value in the combo box to use in an update query. The below string works fine to source the name (text) and make an update but what I cant effectively do is source the unique ID number. I have tried by putting '.value in front put id does not help.
This works fine to update the text value of the control 'cboTeams':
st_Sql = "UPDATE tblStaticDataDepartments01 SET tblStaticDataDepartments01.MacroProcesso = [Forms]![frmStaticDataDepartments01]![cboTeams] WHERE (((tblStaticDataDepartments01.MacroProcesso)is null))"
Application.DoCmd.RunSQL (st_Sql)
This DOES not work when trying to retrieve the numerical value:
st_Sql = "UPDATE tblStaticDataDepartments01 SET tblStaticDataDepartments01.IDMacroProcesso = [Forms]![frmStaticDataDepartments01]![cboTeams].[Value] WHERE (((tblStaticDataDepartments01.MacroProcesso)is null))"
Application.DoCmd.RunSQL (st_Sql)
Upvotes: 0
Views: 2352
Reputation: 1129
st_Sql = "UPDATE tblStaticDataDepartments01 SET tblStaticDataDepartments01.IDMacroProcesso = '" & Form_frmStaticDataDepartments01.cboTeams.Column(1) & "' WHERE (((tblStaticDataDepartments01.MacroProcesso) is null))"
Application.DoCmd.RunSQL (st_Sql)
Upvotes: 0
Reputation: 6082
It sounds like your combo box has 2 columns, like this:
with the column count set to 2:
and the bound column is set to the 2nd column (or whatever column has text value):
When you use .Value
, it will get the value of the bound column only. To get the value of a different column, you can use .ItemData(colNum)
, like this:
st_Sql = "UPDATE tblStaticDataDepartments01 SET tblStaticDataDepartments01.IDMacroProcesso = " & Forms("frmStaticDataDepartments01").cboTeams.ItemData(0) & " WHERE (((tblStaticDataDepartments01.MacroProcesso) is null))"
The columns are zero-based, so to retrieve the first column you use ItemData(0)
, second column use ItemData(1)
, and so on. This is kind of confusing because the property page of the "Column Count" setting is 1-based.
Microsoft's examples make it look like you can use the ItemData function using expression syntax like this, but this did NOT work for me in my copy of Access 2003. However, the code I gave you should work fine.
st_Sql = "UPDATE tblStaticDataDepartments01 SET tblStaticDataDepartments01.IDMacroProcesso = [Forms]![frmStaticDataDepartments01]![cboTeams].[ItemData](0) WHERE (((tblStaticDataDepartments01.MacroProcesso) is null))"
You can retrieve the value of ANY column and row (not just the selected row) using the Column function, in case you ever have that need. See https://msdn.microsoft.com/en-us/library/office/ff192660.aspx
Upvotes: 3