Update listbox error

[yeppy12]

I am trying to update a listbox based on another listbox. I have the following code, but I am not sure why it is not working. Ideally, I want to be able to make a selection from the Region listbox and have the CTB listbox update with the relevant choices. Any help is appreciated.

Private Sub Region_Click()
Dim myRegion As String: myRegion = Me.Region.Value
Me.CTB_Name.RowSource = SELECT [CTB query].CTB_ID, [CTB query].NAME, [CTB query].STATE FROM [CTB query] ORDER BY [NAME], [STATE], [CTB_ID];

 

[wazz]

WHERE [CTB query].Region = Me.Region.
- you won't need the dim statement.
- Region has to be in the SELECT part of the statement. (maybe you are using STATE? i.e., "WHERE [CTB query].State = Me.Region"). in any case, you are missing the WHERE part of the statement).

- recommend turning the SQL statement into a saved query(def) and making that the rowsource of (Me.CTB_Name). then, on (Private Sub Region_Click()): Me.CTB_Name.Requery.

 

[yeppy12]

Thanks. Everything seems to work except that a dialog box comes up asking for the region every time I click on a choice in the region list. Any idea as to why that is happening?

 

[wazz]

probably a mismatch of terms between your field names in the table and field names in your query(ies). make sure all the names are the same. also, if your "region" is actually a state change the name of your listbox to lstState. it doesn't really matter but be consistent it will help to avoid confusion later (and now )