Need to update subform based on combobox selection in main form (1 Viewer)

[accvbalearner]

Hi All, I need a little bit of help on a form that I am working on.

I have attached a sample database so that you can see the code that I am using or trying to use anyway.

Here is what I am trying to do:
I have a form with a subform in it. What I am trying to do is filter the subform based on the selection in two combo boxes on the main form, the code I am using is building an SQL Statement to run against the table to filter the subform, or it's supposed to anyway. This code is working fine in another database where I am able to select multiple items in each of seven separate list boxes, after each selection the subform is filtered.

The problem that I have in the attached database is that it doesn't select any data after updating one or both of the combo boxes. The subform is blank, but it shouldn't be.




Here is what I need to happen.....

1. When the user selects Facility TEST, then the subform should show all items in C01-CodeDict that have a PlantCode = 'TEST'.
2. If the user also selects a CodeType of System, then the subform should show all TEST Facilities with a CodeType of System.
3. If the User only selects a CodeType of System, then the subform should show all items in C01-CodeDict that have a CodeType = 'System'
4. If no selections are made, the subform should be blank.

The purpose of the form is to allow a user to enter in new codes for many facilities, systems, etc. When a user selects a facility or code type, I want them to see the codes already created to give them idea's for how to create a new code, or they may see that the code they are going to create already exists. I already have a set of code in place and working that will handle any duplicates after they have completed filling out the main form, that is the purpose of the APCode field in the C01-CodeDict table.

Thanks in advance for help that you can provide!

 

[informer]

Hi,

To populate a subform, you need to create dynamically a query which will be set to the subform recordset as follow

 qry = "SELECT Field... FROM .... WHERE 1 " 

If Me.ListBox1.ListIndex <> -1 then
  qry = qry & " AND field1 = ‘" & Me.listbox1.value & "’ "
end if

If Me.ListBox2.ListIndex <> -1 then
  qry = qry & " AND field2 = " & Me.listbox2.value ‘ no singles quotes if it’s a number
end if

 Me.Mysubform.Form.Recordsource = qry
 Me.Mysubform.Requery

 

[CJ_London]

umm, think you are 5 years too late!

 

[informer]

You’re right.