CODEWITHSUNDEEP
vbams-accessms-access-2010
user3661576
user3661576

Reputation: 51

Update combobox based on another combobox through query

I have several combo boxes (CB) populating from my access database. They all pull from the same table and use the same hidden primary key from the table. When I select a value from one of the CB's I would like to have the others updated with the associated value based on the matching primary key.

Is this possible?

I've been trying to use variations of the following for a while now with no success:

Dim strSQL As String
strSQL = "SELECT gsr_id FROM task WHERE task_wid = " & Me.cboTask.Column(0)
Me.cboGSRTask.Section = CurrentDb.OpenRecordset(strSQL)

Debug.Print "SELECT gsr_id FROM task WHERE task_wid = " & Me.cboTask.Column(0)

Thank you.

Upvotes: 0

Views: 201

Answers (1)

Andre
Andre

Reputation: 27634

If you open a recordset, you need to read a value from it, you can't use the recordset as value. I guess what you are looking for is:

Dim strSQL As String
Dim RS As Recordset

strSQL = "SELECT gsr_id FROM task WHERE task_wid = " & Me.cboTask.Column(0)
Set RS = CurrentDb.OpenRecordset(strSQL)

' This assumes that gsr_id is the bound column of cboGSRTask
Me.cboGSRTask = RS!gsr_id
RS.Close

Or instead all of the above, using DLookup() :

Me.cboGSRTask = DLookup("gsr_id", "task", "task_wid = " & Me.cboTask.Column(0))

Or it might be even easier to add gsr_id to the row source of cboTask (as a column with width = 0) and use that column to assign to cboGSRTask.

Upvotes: 1

Related Questions