Filters on combo boxes

[alatre2000]

Ok ....
I tried searching here .. the first word I have for the results is EEPS! lol... This is a very POPULAR site

OK ..My access days are waaay back.. I have tables built this way

Customer (fields cust_id, name, etc)
Machines (fields cust_id, mach_id, location, etc)
Intervention (fields mach_id, caller, etc)

Now its a 1 to many relationship on cust_id (customer to machine) and 1 to may on mach_id from machines to intervention

I am filling an intervention form, and i use combo boxes to select customers and machine that required intervention. My prob is that i want a filter on the machine combo box selection that filters based on the cust_id of what i selected in customer. Also I want all fiels to autorefresh as soon as i modify my selection....

What's the best way? Please remember, i am quite rusty on sql and expressions... I believe criterias have to be specified but i am drawing a blank

Thx

 

[ChipsIT]

Hi

To filter the records in the Machines combo;

Sub cboCustomer_AfterUpdate

Dim strSQL As String

  strSQL="SELECT Machines.Mach_ID FROM Machines" _ 
    & "WHERE Machines.Cust_ID=" & me.cboCustomer
  cboMachine.Rowsource=strSQL
  cboMachine.Requery

End Sub

To amend the record source on the form;

Sub cboMachine_AfterUpdate

Dim strSQL As String

  strSQL="SELECT Intervention.* FROM Intervention " _
    & "WHERE Intervention.Mach_ID=" & me.cboMachine
  Me.Recordsource=strSQL
  Me.Requery

End Sub

OR to filter the form;

Sub cboMachine_AfterUpdate

  Me.Filter = "[Mach_ID]=" & me.cboMachine
  Me.FilterOn=True

End Sub

I assume your IDs are numeric, otherwise you must add single quotes around them.

Hope this assists.

Steve