Query based on combo box selection

[david.paton]

Hi,

I have a form that has a query embedded on the form in a sub form. I then have a combo box on the form that I want to filter the query results depending on the town I select from the combo box.

In the query, I have the TownID from the town table, with the criteria =[forms]![frmLegateeList]![cboTown] with cboTown being the combo box that has allows the selection of a town. I also have the information I need to have shown relating to the record I wish to select the town for.

When I try and run the query, i get no results.

I also got help with this code as I can't code, that is behind my combo box:

Private Sub cboTown_AfterUpdate()
If IsNull(cboBox) Then
Me.FilterOn = False
Else
Me.Filter = "[town]='" & Me.cboBox & "'"
Me.FilterOn = True
End If
End Sub


How can I get it working?

Thanks,
Dave

 

[bob fitz]

What is the Row Source property of the combo box

 

[david.paton]

This is the row source:

SELECT [Town].[TownID], [Town].[TownName] FROM [Town] ORDER BY [TownName];

 

[bob fitz]

Can you show us the SQL of the query used in the sub form.

What is the purpose of the code you posted

 

[Minty]

This part of your code

Me.Filter = "[town]='" & Me.cboBox & "'"

Is trying to match the TownID to the Town field. I think you need to use

Me.Filter = "[town]='" & Me.cboBox.column(1) & "'"

To refer to the second column that has your actual town string in it. Assuming that the ID is the bound column.
Columns in combo boxes are numbered from (0) upwards.

 

[david.paton]

I have just realised that I am making this database more complex than it ought to be and I don't need to create that bit I was asking about. Thanks anyway guys.

Dave