In a continuous form, can I have a combo box use a different query depending on a field or text box value within its own record?

Question

Edit: for those wondering, it's apparently impossible to have the same combo box for two different records in a continuous form refer to two different queries to populate its list.

I have a continuous form that has maybe 5 records. There is a combo box, Laborer1, and would like the dropdown to be different for each form, depending on some other factors. I managed to put the exact query I want for each record's combo box as a text field within that record. What is the next step? All I can manage at this point is apply the query of one record to all combo boxes, but I want each combo box to use its "own" query.

Thanks!

Answer 1

Yes, you can do it, but there's a tradeoff: inactive records may have a value which doesn't fit within the current rowsource for the combobox. When that happens, you'll get a blank combobox, instead of having it show the current value. If you activate the record, the value will appear again, but it's not a fantastic user experience.

That said, one option would be to handle it in the Form_Current event. Since you're already storing the rowsource in a database field, the code for this is really short:

Private Sub Form_Current
  Laborer1.RowSource = ReferenceField.Value
  Laborer1.Requery 'I don't believe you need this, but you might.
End Sub

Answer 2

I think I have an answer for continuous forms. When u click on combobox other combos turns blank but when u select everything looks normal.

I worked with my tables so sorry for a different example.. Anyway I think It is an answer..

I have 2 combobox and when u select the country the city combo shows only the selected Country's Cities.

This code is for Country Combo

Private Sub Country_Change()
Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset


Set dbs = CurrentDb

'Open a table-type Recordset
Set rsTable = dbs.OpenRecordset("Cities", dbOpenTable)

'Open a dynaset-type Recordset using a saved query
Set rsQuery = dbs.OpenRecordset("Select * FROM Cities WHERE Country='" + Country + "'", dbOpenDynaset)
'We created a recordset filtered and set it to city.recordset
Set city.Recordset = rsQuery
city.Requery
End Sub

And this 2 sub is for City Combo..

Private Sub City_Change()
Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset

Set dbs = CurrentDb

'Open a table-type Recordset
Set rsTable = dbs.OpenRecordset("Cities", dbOpenTable)

'Open a dynaset-type Recordset using a saved query
Set rsQuery = dbs.OpenRecordset("Select * FROM Cities", dbOpenDynaset)

Set city.Recordset = rsQuery
'We created a recordset NOT filtered and set it to city.recordset and after that we focus on a different control to be sure that our city combobox work correctly
city.Requery
Kimlik.SetFocus
End Sub

Private Sub City_Click()

Dim dbs As DAO.Database
Dim rsTable As DAO.Recordset
Dim rsQuery As DAO.Recordset


Set dbs = CurrentDb

'Open a table-type Recordset
Set rsTable = dbs.OpenRecordset("Cities", dbOpenTable)

'Open a dynaset-type Recordset using a saved query
Set rsQuery = dbs.OpenRecordset("Select * FROM Cities WHERE Country='" + Country + "'", dbOpenDynaset)
'We created a recordset filtered and set it to city.recordset
Set city.Recordset = rsQuery
city.Requery
End Sub

And here is my Access file