Two Combo boxes to query one subform

[liltechy]

I have a main form that has two combo boxes and a subform. When I select the IO number in cbo1 it pulls all the records for that IO number in my subform. Based on the vendor numbers in the subform (pulled by the IO number)I then want cbo2 to look at the vendor number field in the subform and put in the drop list only the vendor names that have vendor numbers listed in the subform. Then when I select a vendor in cbo2, I need the subform to requery with that vendors invoices only. I have had no success in achieving this. Please, please help, any help would be appreciated.

 

[LogicalmanUS]

liltechy,

No panic. Just split the problem into it's logical steps, and code from there.

First. Populate the SubForm using the selection from cbo1: Create a RecordSource for the subform, based on the selection of cbo1, using the 'After Update' Event.
You can reference the subform using the subform control. e.g. If you were to have a main form frmMain, and a subform control sfmSub1, then you may use the syntax of :

mySQLStatement = "SELECT * FROM mytable WHERE IO = " & me.cbo1.Coloumn(0) & ";"
me.sfmSub1.Recordsource = mySQLStatement

Second. You need to populate the cbo2 using the Vendor Names from the SubForm: On the face of it, this is wrong. remember the subform derives it's recordsource from cbo1, so use the same source.
mySQLStatement = "SELECT VNo, VName FROM mytable WHERE IO = " & me.cbo1.Coloumn(0) & " GROUP BY VNo, VName; "
Me.cbo2.rowsource = mySQLStatement

Third. Populate the subform using the selection from cbo2: Again, as per the first part, we use the same syntax:

mySQLStatement = "SELECT * FROM mytable WHERE VNo = " & me.cbo2.Coloumn(0) & ";"
me.sfmSub1.Recordsource = mySQLStatement

And there you have it, just about. Above is the simple syntax, you will need to put the meat on the bones, so to speak.

If you need to reference the main form from the subform, you may use a Form Object, referencing the Main form:
Dim aForm as Form
set aForm = Forms!frmMain

Or you may reference it using the relational syntax: Me.Parent.{controlName}.


Hope this assists,

Logicalman

 

[liltechy]

Where do I put these SQL statements?

liltechy

 

[liltechy]

I got the first and second part to work OK with your assistance, but am I supposed to put the third part in the rowsource where the first part already is?

liltechy