Subform results

[terryvanduzee]

Hello



I have a form that has 2 comboboxes. These 2 cbo's are suppose to be the criteria for my subform.

The subform record source is as follows:

SELECT tbl_CurrentInv.Itemname, tbl_CurrentInv.Itemsize, tbl_CurrentInv.Itemvendor, tbl_CurrentInv.itemBay, tbl_CurrentInv.ItemAsOfDate, tbl_CurrentInv.ItemDateArrived, tbl_CurrentInv.Item_OddBox, tbl_CurrentInv.Item_OddMatt, tbl_CurrentInv.ID FROM tbl_CurrentInv WHERE (((tbl_CurrentInv.Itemname)=Forms!frm_Test!Combo0.value) And ((tbl_CurrentInv.Itemsize)=Forms!frm_Test!Combo2.value));

Combobox 0 is itemname and Combobox 2 is itemsize

the SQL was created through access based on the query screen. I typed the Forms!frm_Test!Combo0.value etc in the query screen.

I have verified that the table contains the data I am querying for, but no records show up in my subform. I have tried requerying the subform but still no records.

Any suggestions?

Thank you
Terry

[terryvanduzee]

I did resolve this.
Just incase someone needs to do the same thing as I needed to do, I will post the code.
I was trying to display results in a subform based on 2 different comboboxes in the main form.

The problem was that when I tried getting the "Text" value from the cbo's, I was only picking up one value, because in order to use the "Text" value the focus needed to be put on that particular Combobox before getting the value.

So, I decided to assign the cbo1 text value to a variable, then setfocus to cbo2 and assign its text value to another variable.

I placed the code on the 2nd Combobox so that it will not run until the 2nd cbo is changed.

Then I used the variables in the SQL statement and then assigned the sql statement to the recordsouce property. Works like a charm

If anyone finds anything that can be tweaked in this code, "Please" let me know.

Private Sub cbo_Matt_Size_AfterUpdate()
Dim strSQL As String
x = Forms!frm_Mat_Name_Main.cbo_Matt_size.Text
Forms!frm_Mat_Name_Main.cbo_Matt_Name.SetFocus
y = Forms!frm_Mat_Name_Main.cbo_Matt_Name.Text

strSQL = "Select * " & _
"From qry_Curr_Inv_AllFields " & _
"Where (qry_Curr_Inv_AllFields.ItemName Like '*" & y & "*'" & ") " & _
"And (qry_Curr_Inv_AllFields.Itemsize= '" & x & "') " & _
"And (qry_Curr_Inv_AllFields.Item_sold=false);"

Me.Refresh
Me.Form("frm_CurrentInv_subform").Form.RecordSource = strSQL
Me.Form("frm_CurrentInv_subform").Form.Requery

End Sub

Thank you
Terry