cascading combos requery

[Delid4ve]

keep short and simple, struggling to find a definative way to input this on web..

i have a combo that is bound to a set list, each selection sets a number of other combos visibility to either true or false.
moving records this does not happen.
i know i need it to requery but where and how?
i believe this needs to go into the main forms current event but how

main code for the combo is:

Private Sub DocumentType_AfterUpdate()
Select Case Me.DocumentType.Value
Case "Collection Note"
Me.CollectionSupplier.Visible = True
Me.CollectionBranch.Visible = True
Me.CollectionEmployee.Visible = True
Me.CollectionDate.Visible = True
Me.ReturnSupplier.Visible = False
Me.ReturnBranch.Visible = False
Me.ReturnEmployee.Visible = False
Me.ReturnDate.Visible = False
Me.QtyUsedRecovered.Visible = False
Me.VehicleID.Visible = False
Forms![Record].[Record_Cylinders]![QtyWasteTransferred].ColumnHidden = True
Forms![Record].[Record_Cylinders]![QtyDisposed].ColumnHidden = True
Forms![Record].[Record_Cylinders]![DisposalType].ColumnHidden = True
Forms![Record].[Record_Cylinders]![QtyNet].ColumnHidden = False
Forms![Record].[Record_Cylinders]![TareWeight].ColumnHidden = True
Case "Returns Note"
Me.CollectionSupplier.Visible = False
Me.CollectionBranch.Visible = False
Me.CollectionEmployee.Visible = False
Me.CollectionDate.Visible = False
Me.ReturnSupplier.Visible = True
Me.ReturnBranch.Visible = True
Me.ReturnEmployee.Visible = True
Me.ReturnDate.Visible = True
Me.[QtyUsedRecovered].Visible = False
Me.VehicleID.Visible = False
Forms![Record].[Record_Cylinders]![QtyWasteTransferred].ColumnHidden = True
Forms![Record].[Record_Cylinders]![QtyDisposed].ColumnHidden = True
Forms![Record].[Record_Cylinders]![DisposalType].ColumnHidden = True
Forms![Record].[Record_Cylinders]![QtyNet].ColumnHidden = False
Forms![Record].[Record_Cylinders]![TareWeight].ColumnHidden = True
Case "Consignment Note"
Me.CollectionSupplier.Visible = False
Me.CollectionBranch.Visible = False
Me.CollectionEmployee.Visible = False
Me.CollectionDate.Visible = False
Me.ReturnSupplier.Visible = True
Me.ReturnBranch.Visible = True
Me.ReturnEmployee.Visible = True
Me.ReturnDate.Visible = True
Me.[QtyUsedRecovered].Visible = False
Me.VehicleID.Visible = True
Forms![Record].[Record_Cylinders]!QtyWasteTransferred.ColumnHidden = False
Forms![Record].[Record_Cylinders]!QtyDisposed.ColumnHidden = True
Forms![Record].[Record_Cylinders]!DisposalType.ColumnHidden = True
Forms![Record].[Record_Cylinders]!QtyNet.ColumnHidden = True
Forms![Record].[Record_Cylinders]!TareWeight.ColumnHidden = True
Case "Waste Producer Returns"
Me.CollectionSupplier.Visible = False
Me.CollectionBranch.Visible = False
Me.CollectionEmployee.Visible = False
Me.CollectionDate.Visible = False
Me.ReturnSupplier.Visible = True
Me.ReturnBranch.Visible = True
Me.ReturnEmployee.Visible = True
Me.ReturnDate.Visible = True
Me.[QtyUsedRecovered].Visible = False
Me.VehicleID.Visible = False
Forms![Record].[Record_Cylinders]!QtyWasteTransferred.ColumnHidden = True
Forms![Record].[Record_Cylinders]!QtyDisposed.ColumnHidden = False
Forms![Record].[Record_Cylinders]!DisposalType.ColumnHidden = False
Forms![Record].[Record_Cylinders]!QtyNet.ColumnHidden = True
Forms![Record].[Record_Cylinders]!TareWeight.ColumnHidden = True
Case "Engineers Log"
Me.CollectionSupplier.Visible = True
Me.CollectionBranch.Visible = True
Me.CollectionEmployee.Visible = True
Me.CollectionDate.Visible = True
Me.ReturnSupplier.Visible = True
Me.ReturnBranch.Visible = True
Me.ReturnEmployee.Visible = True
Me.ReturnDate.Visible = True
Me.[QtyUsedRecovered].Visible = True
Me.VehicleID.Visible = False
Forms![Record].[Record_Cylinders]!QtyWasteTransferred.ColumnHidden = True
Forms![Record].[Record_Cylinders]!QtyDisposed.ColumnHidden = True
Forms![Record].[Record_Cylinders]!DisposalType.ColumnHidden = True
Forms![Record].[Record_Cylinders]!QtyNet.ColumnHidden = False
Forms![Record].[Record_Cylinders]!TareWeight.ColumnHidden = False
Case Else
End Select
End Sub

 

[Delid4ve]

Ignore me

Was just a case of copying the whole code into the forms current event. and seems to work.

Is there a cleaner way to do this?

 

[arnelgp]

before the "End Sub" statement, insert Me.Repaint

so you already resolved it! you don't need to copy all the code into your Form_Current proc, just call the DocumentType_AfterUpdate event again.

Private Sub Form_Current()
Call DocumentType_AfterUpdate
End Sub

 

[Delid4ve]

before the "End Sub" statement, insert Me.Repaint

Just tried that, didnt work

 

[Delid4ve]

before the "End Sub" statement, insert Me.Repaint

so you already resolved it! you don't need to copy all the code into your Form_Current proc, just call the DocumentType_AfterUpdate event again.

Private Sub Form_Current()
Call DocumentType_AfterUpdate
End Sub

Private Sub Form_Current()
Call DocumentType_AfterUpdate
Call CollectionSupplier_AfterUpdate
Call ReturnSupplier_AfterUpdate
End Sub

This works - thanks

One further issue i have:

On navigating records i get a syntax error missing operator in query expression. It looks like its for any record that has no had a value in the second combo selected (where the data is missing).

Code for the 1st combo is:
Private Sub CollectionSupplier_AfterUpdate()
Me.CollectionBranch.RowSource = "SELECT BranchID, BranchName " & _
"FROM Suppliers_Branches " & _
"WHERE SupplierID = " & Nz(Me.CollectionSupplier) & " "
End Sub

It works for input, just the browsing. Why, ive tried reomiving the nz, enclosing fields in [], what am i missing here?

 

[arnelgp]

"WHERE SupplierID = " & Val(Me.CollectionSupplier & "")

 

[Delid4ve]

Worked perfect.

Thank you very much