Combobox filtered on 2 unbound text boxes

[Geordie2008]

Hello's

I have a form with 2 unbound text boxes.

First Name (txt_First_Name)
Last Name (txt_Last_Name)

I have a combo box on the same form which I would like to be filtered by the values entered into the 2 unbound boxes.

So
:

First Name: M
Last Name: Jones

Should populate the combo box with:

Mark Jones
Michael Jones

etc.

I used the following on the "row source" of the comboBox properties:

SELECT Tbl_MAIN_Staff_Details.ID, Tbl_MAIN_Staff_Details.Last_Name, Tbl_MAIN_Staff_Details.First_Name FROM Tbl_MAIN_Staff_Details ORDER BY [Last_Name] WHERE ((Tbl_MAIN_Staff_Details.Last_Name like me.txt_Last_Name AND Tbl_MAIN_Staff_Details.First_Name like me.txt_First_Name

Can someone advise what Im doing wrong? Tis not happening for me!

Thanks,
Mandy

 

[ajetrumpet]

SELECT Tbl_MAIN_Staff_Details.ID, Tbl_MAIN_Staff_Details.Last_Name, Tbl_MAIN_Staff_Details.First_Name FROM Tbl_MAIN_Staff_Details ORDER BY [Last_Name] WHERE ((Tbl_MAIN_Staff_Details.Last_Name like me.txt_Last_Name AND Tbl_MAIN_Staff_Details.First_Name like me.txt_First_Name

First of all, the me. is incompatible syntax in SQL. You need to replace that with forms!formname!

Also, replace the like keyword with an equal sign, unless you are still going to use the First Name initial. And if you are, then the syntax should be this:

Tbl_MAIN_Staff_Details.First_Name like forms!formName!txt_First_Name & "*"

 

[Geordie2008]

The following code works:

SELECT Tbl_MAIN_Staff_Details.ID, Tbl_MAIN_Staff_Details.Last_Name, Tbl_MAIN_Staff_Details.First_Name FROM Tbl_MAIN_Staff_Details WHERE ((Tbl_MAIN_Staff_Details.Last_Name Like forms!test!txt_Last_Name And Tbl_MAIN_Staff_Details.First_Name Like forms!test!txt_First_Name));

When I try and add in the wildcard "*",

SELECT Tbl_MAIN_Staff_Details.ID, Tbl_MAIN_Staff_Details.Last_Name, Tbl_MAIN_Staff_Details.First_Name FROM Tbl_MAIN_Staff_Details WHERE ((Tbl_MAIN_Staff_Details.Last_Name Like '" & forms!test!txt_Last_Name  & "*' And Tbl_MAIN_Staff_Details.First_Name Like '" & forms!test!txt_First_Name & "*'"));

this one above doesn't work.

SELECT Tbl_MAIN_Staff_Details.ID, Tbl_MAIN_Staff_Details.Last_Name, Tbl_MAIN_Staff_Details.First_Name FROM Tbl_MAIN_Staff_Details WHERE ((Tbl_MAIN_Staff_Details.Last_Name Like forms!test!txt_Last_Name  & “*” And Tbl_MAIN_Staff_Details.First_Name Like forms!test!txt_First_Name & “*”))

The one above gives me the follwoing error:

This expression is typed incorrectly of it is too complex to be evaluated.

Can anyone help me with this please?
Thanks,
Mandy

 

[Geordie2008]

Does anyone know how to move a post to another forum? Think this might fit better in the VBA one?

Thanks,
Mandy

 

[Geordie2008]

all sorted (my quotes were wrong):

SELECT Tbl_MAIN_Staff_Details.ID, Tbl_MAIN_Staff_Details.Last_Name, Tbl_MAIN_Staff_Details.First_Name FROM Tbl_MAIN_Staff_Details WHERE ((Tbl_MAIN_Staff_Details.Last_Name Like forms!test!txt_Last_Name & "*" And Tbl_MAIN_Staff_Details.First_Name Like forms!test!txt_First_Name & "*"))