Pass through values to listbox on click

[10kz]

I've recreated an example of a navigation form with 3 subforms (CatA, CatB, CatC). When a certain subform is selected I want the the listbox in the selector subform to update to only show that type of category items.

I've set a on click event when CatA is selected in the navigation form that the following get triggered:

me.[NavigationSubform].Forms![CatA]![selector].recordsource = "SELECT Values.ID, Values.State FROM [Values] WHERE (((Values.Category) = "a"))GROUP BY Values.ID, Values.State;

However I'm getting an syntax error. Any ideas? I've attached a copy of the example.

 

[Galaxiom]

me.[NavigationSubform].Forms![CatA]![selector].recordsource

should be

me.[NavigationSubform].Form.[CatA]![selector].recordsource

 

[isladogs]

As well as the change noted by Galaxiom, you need to change "a" to 'A' and add a space before GROUP BY

me.[NavigationSubform].Form.[CatA]![selector].recordsource = "SELECT Values.ID, Values.State FROM [Values] WHERE (((Values.Category) = 'A')) GROUP BY Values.ID, Values.State;

However, I've tried all of that and it still errors ...!

I hate navigation forms for this sort of reason ....

Suggest you check Allen Browne's website for managing subform controls in code or in your case sub subforms:
http://allenbrowne.com/casu-04.html

 

[Galaxiom]

Tyr this:

Me.[NavigationSubform].Form.[selector].Form.recordsource = "SELECT Values.ID, Values.State FROM [Values] WHERE (((Values.Category) = 'a'))GROUP BY Values.ID, Values.State;"

 

[isladogs]

Hi

Just to say I'd also tried Galaxiom's 2nd bit of code (with the 'A' and space added) before answering the OP the first time:

Private Sub NavigationButton9_Click()
Me.NavigationSubform.Form.Selector.Form.RecordSource = "SELECT Values.ID, Values.State FROM [Values] WHERE (((Values.Category) = 'A')) GROUP BY Values.ID, Values.State;"
End Sub

It gives error 2465 - application-defined or object-defined error

The problem is with the notation before the record source but I couldn't pin down the solution

 

[10kz]

I've tried both snippets of code but nothing happens on the click event.

 

[isladogs]

That was precisely my point but you should have got an error...

Personally I would replace the navigation controls & just use a combo box to filter the results in the listbox.
Much simpler to manage

 

[static]

Private Sub NavigationButton9_Click()
    fltr "a"
End Sub

Private Sub NavigationButton11_Click()
    fltr "b"
End Sub

Private Sub NavigationButton13_Click()
    fltr "c"
End Sub


Private Sub fltr(cat As String)
    Me.[NavigationSubform]![selector]![list4].RowSource = "SELECT distinct ID, State FROM [Values] " & _
        "WHERE Category = '" & cat & "'"
End Sub