change subform record source from main form combo

[Delid4ve]

Trying for ages to get this right but keep getting errors.

Main Form: Suppliermaster
Sub Form: SupplierSub
Queries for sub form record source:
OFN_DocumentsMaster , Refrigerants_Documents_Master

Main form Combo Name: CylinderType
List Options: "OFN", "Refrigerants"

When selecting one of the two options listed in the main form combo, i would like for it to update the subform record source with the appropriate query and then requery the data.

Been attempting this for hours now but cannot seem to get the syntax right. Been trying to do this as a 'case' statement as i also need to add in additional bits and pieces as well as changing the record source.

If anyone could help me please, im not great with vba.

 

[Minty]

That age old question - show us what you have tried so far?

 

[RuralGuy]

Have you tried just using a TabFormControl and just switching tabs?

 

[Delid4ve]

Right, so scrap that idea, ive changed to filtering the query. Got the afterupdates working for a couple of combos see below, what id like to do now though is cascade the filtering. How?

Ive had a read about but cannot find anything specific as everything i search relates to cascading combos for selections, not filtering.

The two I need to cascade are:


Private Sub Cylinder_Type_AfterUpdate()
Select Case Me.Cylinder_Type.Value
Case "OFN"
Me.SupplierSub.Form.Filter = "(Documents_Master.RefrigerantType) = 'OFN' "
Me.SupplierSub.Form.FilterOn = True
Case "Refrigerants"
Me.SupplierSub.Form.Filter = "(Documents_Master.RefrigerantType) not like 'OFN' "
Me.SupplierSub.Form.FilterOn = True
End Select
End Sub

Private Sub SupplierName_AfterUpdate()
Me.SupplierSub.Form.Filter = "[SupplierName] = " & Chr(34) & Forms!SupplierMaster.SupplierName & Chr(34)
Me.SupplierSub.Form.FilterOn = True
End Sub

 

[Delid4ve]

Just an update on how i fixed it for any future searches:

Set all the filtering on 1 combo and the other combos just called the event procedure for that combo:

Private Sub Cylinder_Type_AfterUpdate()
Select Case Me.Cylinder_Type.Value

Case ""
Me.Cylinders_SupplierDetails.Form.RecordSource = "SELECT Documents_Master.* " & _
"FROM Documents_Master " & _
"WHERE Documents_Master.SupplierName = " & Chr(34) & Forms!Documents_SupplierDetails.Supplier & Chr(34)

Case "Recovery/Reciever"
Me.Cylinders_SupplierDetails.Form.RecordSource = "SELECT Documents_Master.* " & _
"FROM Documents_Master " & _
"WHERE Documents_Master.SupplierName = " & Chr(34) & Forms!Documents_SupplierDetails.Supplier & Chr(34) & _
"AND Documents_Master.RefrigerantType like 'Recovery'" & _
"or Documents_Master.RefrigerantType like 'Reciever' " & _
"AND Documents_Master.SupplierName = " & Chr(34) & Forms!Documents_SupplierDetails.Supplier & Chr(34)
Case "OFN"
Me.Cylinders_SupplierDetails.Form.RecordSource = "SELECT Documents_Master.* " & _
"FROM Documents_Master " & _
"WHERE Documents_Master.RefrigerantType like 'OFN' " & _
"AND Documents_Master.SupplierName = " & Chr(34) & Forms!Documents_SupplierDetails.Supplier & Chr(34)

Case "Refrigerants"
Me.Cylinders_SupplierDetails.Form.RecordSource = "SELECT Documents_Master.* " & _
"FROM Documents_Master " & _
"WHERE Documents_Master.RefrigerantType not like 'OFN'" & _
"AND Documents_Master.RefrigerantType not like 'Recovery'" & _
"AND Documents_Master.RefrigerantType not like 'Reciever' " & _
"AND Documents_Master.SupplierName = " & Chr(34) & Forms!Documents_SupplierDetails.Supplier & Chr(34)

Case "All"
Me.Cylinders_SupplierDetails.Form.RecordSource = "SELECT * " & _
"FROM Documents_Master " & _
"WHERE Documents_Master.SupplierName = " & Chr(34) & Forms!Documents_SupplierDetails.Supplier & Chr(34)
End Select
End Sub

Private Sub Supplier_AfterUpdate()
Call Cylinder_Type_AfterUpdate
End Sub