Dynamically assigning SQL statement to RowSource

[missinglinq]

This works fine when placed in the RowSource Property of a combobox, but what modifications to I need to assign it dynamically to the same combobox?

SELECT [Doctors2].[MDName], [Doctors2].[MDAddress]
FROM Doctors2
WHERE ((([Doctors2].[MDName]) Like ("*" & [Forms]!Doctors2![MDName] & "*") Or ([Doctors2].[MDName]) Like ([Forms]![Doctors2]![MDName] & "*") Or ([Doctors2].[MDName]) Like ("*" & [Forms]![Doctors2]![MDName]) Or ([Doctors2].[MDName]) Like [Forms]![Doctors2]![MDName]));

Thanks for looking at it!

Linq

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[maxhugen]

Its unclear why you need to 'dynamically' assign that query to the combo's rowsource???

One thing I can point out is that the first LIKE condition you are using also covers the next 3 LIKE conditions, so they are unnecessary. The following does the same as your query:

SELECT [Doctors2].[MDName], [Doctors2].[MDAddress]
FROM Doctors2
WHERE [Doctors2].[MDName] Like '*" & [Forms]!Doctors2![MDName] & "*'";

(Note the use of the single quotes withing the LIKE string.)

If you need to requery this combo when [MDName] changes in the form, use the After Update event of the [MDName] control, eg:

Private Sub MDName_AfterUpdate()
    myComboBox.Requery
End Sub

HTH

Max Hugen
Australia

 

[simon551]

you can also do a myComboBox.rowSource="myQuery

 

[missinglinq]

Thanks, Max, you're right about the one Like statement serving all purposes! The SQL was boilerplate I picked up online and I didn't really examine it closely. As to requerying, I said that the SQL "works fine when placed in the RowSource Property of a combobox" but that was testing purposes; the combobox is used for multiple purposes and starts out with no Rowsource, hence the need to source it dynamically. Sorry I wasn't clear.

Thanks to you, too, Simon!



The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

You wanted this ?

Me![combo name].RowSource = "SELECT MDName,MDAddress FROM Doctors2" _
 & " WHERE MDName Like '*" & Forms!Doctors2!MDName & "*'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886