when I try to change the Rowsource in a combo box I get type mishmatch

[lazaridisaris]

Hi all,

I have a combo box that has as recordsource
"SELECT End_pro.[a-a_End_pro], [End_APO_name] & "-" & [End_STO_Pro_name] AS [Apo pros], End_pro.End_STO_Pro_name FROM End_pro;"
in VBA code I search for a specific string which I pass to a variable named TmpVar.

and then I

MyCMB.rowsource = "SELECT End_pro.[a-a_End_pro], [End_APO_name] & "-" & [End_STO_Pro_name] AS [Apo pros], End_pro.End_STO_Pro_name FROM End_pro WHERE End_pro.End_STO_Pro_name = " & TmpVar & ";"

And I get Run-Time error '13' Type Mismatch

What I want to acomplish is to filter the results of the combo box so the users dont by mistake select some thing that is not correct.
If there is a different way to do that it would be very appriciated.

ps
My Combo box has as control source a field

Thanks in advance

Aris

 

[Remou]

I think that End_STO_Pro_name is text, so you will need single quotes:
[tt]MyCMB.rowsource = "SELECT End_pro.[a-a_End_pro], [End_APO_name] & "-" & [End_STO_Pro_name] AS [Apo pros], End_pro.End_STO_Pro_name FROM End_pro WHERE End_pro.End_STO_Pro_name = '" & TmpVar & "';"[/tt]

 

[lazaridisaris]

Thanks for you time but

I already tried that and also the & chr(34) & TmpVar & chr(34)
but still getting the type mismatch error

regards

 

[PHV]

MyCMB.rowsource = "SELECT [a-a_End_pro],[End_APO_name] & [!]'-'[/!] & [End_STO_Pro_name] AS [Apo pros],End_STO_Pro_name FROM End_pro WHERE End_STO_Pro_name=[!]'[/!]" & TmpVar & "[!]'[/!]"


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lazaridisaris]

Thanks PHV

That did the job.

It worked fine

Thanks again

Aris