Filtering two subforms through the main form

[Constantine AI]

Hi can anybody help?

I have a main form with customer details on that links to a sales order head subform through the customer ID. The sales order head subform is then linked to the sales order line through the order no. I need to find a specific sales order number through the main form and once this number is chosen it needs to dispalay the relevant customer and order line details.

I have the following code but it filters the customer Id instead of sales order no, ive tried changing the code around to filter the sales order number but it doesnt work, can anybody help:

Dim strSQL As String
If IsNull(Me.cboSh owOrder) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "tblCustome r"
Else
strSQL = "SELECT DISTINCTROW tblCustomer.* FROM tblCustomer " & _
"INNER JOIN tblSalesOrderHe ad ON " & _
"tblSalesOrderH ead.CustomerID = tblCustomer.Cus tomerID " & _
"WHERE tblCustomer.Cus tomerID = " & Me.cboShowOrder & ";"
Me.RecordSource = strSQL
End If

If I cant filter from the main form then can i filter in the sales order subform which still allows me to display customer and order line details?

[JConsulting]

Hi can anybody help?

I have a main form with customer details on that links to a sales order head subform through the customer ID. The sales order head subform is then linked to the sales order line through the order no. I need to find a specific sales order number through the main form and once this number is chosen it needs to dispalay the relevant customer and order line details.

I have the following code but it filters the customer Id instead of sales order no, ive tried changing the code around to filter the sales order number but it doesnt work, can anybody help:

Dim strSQL As String
If IsNull(Me.cboSh owOrder) Then
' If the combo is Null, use the whole table as the RecordSource.
Me.RecordSource = "tblCustome r"
Else
strSQL = "SELECT DISTINCTROW tblCustomer.* FROM tblCustomer " & _
"INNER JOIN tblSalesOrderHe ad ON " & _
"tblSalesOrderH ead.CustomerID = tblCustomer.Cus tomerID " & _
"WHERE tblCustomer.Cus tomerID = " & Me.cboShowOrder & ";"
Me.RecordSource = strSQL
End If

If I cant filter from the main form then can i filter in the sales order subform which still allows me to display customer and order line details?

Isn't this more of a Go To Record situation, vs a filter situation? Seeing as how your customer is already selected and all. If I understand this right, you may or may not be on the customer record where the Order Number exists? For the sake of arguement, let's say its not. You would first have to use the Order number and pull the CustomerID foreign key number from it. Then you set your main form's recordsource to that customer. Easy enough. Now that you're on the right customer, you use the same logic that a Combo box uses to find a record, to find the right orderID in your subform

Set rs = Me.MySubform.Re cordset.Clone
rs.FindFirst "[OrderID] = " & Me![MyCombo]
Me.MySubform.Bo okmark = rs.Bookmark

then requery the sub-subform.

Hope this helps!
J

[Constantine AI]

Thank you very much for your help, it does work, I just need to do some tweaking to it now.

Thanks again