hi all,

i am wondering how to pass the id of a combo box item to an sql statement.

I have a cb populated with a list of departments, pulled from the departments table. i want to display the name of the department in the cb, but i want also to be able to pass the id of the selected item so that i can update that particular entry.

could someone help me out with this? i have the cb filled with the data, I used the wizard to get it to pull both the id and the dept name from the table, and hide the id, but i dont know how to reference the id from within the cb.

thanks

Patrick

Visit my site: http://www.drybonesuk.com

__________________
Visit my site: http://www.drybonesuk.com

There are at least two ways to do this:

1. In the query for the recordsource of the form or report that you are opening from the form with the combo box, add criteria to the department field in the query like -

[Forms]![frmYourFormName].[cboDeptComboBox]

   When the form or report opens, the query will pull the bound field from your combo box.

2. Use the following code for the button that opens your form or report:

Dim stDocName As String
Dim lgDeptID As Long (or integer)
Dim stLink As String

lgDeptID = Me.cboYourComboBoxName
stLink = "[DeptID] = " & lgDeptID '(since this is numeric and not a string, single quotes are not needed here)

stDocName = "YourFormOrReportName"
DoCmd.OpenFormORReport stDocName, , stLink '(in the WHERE parameter)

HTH


mmcdonal

P.S. If you were passing a string to the query, you would use the following syntax:

"[Department] = " & "'" & stDeptName & "'"


mmcdonal

Scenario 1:

1) Your combobox has two columns.
2) First column has the ID; its width is zero (invisible)
3) Second column has the dept. name; it's not invisible
4) The bound column is the first one, i.e. ID.
5) Your combobox name is cboMyComboBox.

In Scenario 1, the bound column is the first one, i.e. Column(0). So, to reference the ID is simply: Me.cboMyComboBox, because Me.cboMyComboBox defaults to Me.cboMyComboBox.Column(0) anyway.

Scenario 2:

1) Your combobox has two columns.
2) First column has the ID; its width is zero (invisible)
3) Second column has the dept. name; it's not invisible
4) The bound column is the SECOND one, i.e. Dept.
5) Your combobox name is cboMyComboBox.

In Scenario 2, the bound column is the second one, i.e. Column(1). So to reference the ID is Me.cboMyComboBox.Column(0), because Me.cboComboBox defaults to Me.cboMyComboBox.Column(1).

Using Me.cboComboBox without the Column() property always refers to the bound column.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division