RunSQL: Using passed parameters in a where clause

[Petemush]

I'm currently having a problem with the DoCmd.RunSQL command in VBA. I've written a function that has a number of parameters and then runs a piece of SQL selecting records from a table using a Where clause where each record found should have field values that match the corresponding parameters passed to the function. Unfortunately I can't get it to do that and it either has a syntax error or runs and requests the parameters from the user.

Example Code:

Public Function Find_Query(CCode, CName As TextBox, _
Csource As ComboBox)

DoCmd.RunSQL "SELECT [Course Code], [Course Title]," & _
"[Course Source] INTO [tblTempFind]" & _
FROM [tblCourses]" & _
"WHERE ((([Course Code])=CCode) AND" & _
"(([Course Title])=CName) AND" & _
"(([Course Source])=CSource));"
End Function

 

[oharab]

What is it you want the function to return?
The runSQL command runs action queries, such as Make Table or DELETE.
Also, when you call the function, you are passing textbox and combo box as parameters, then using them in your query. I assume you are trying to pass the value of the text & combo boxes, in which case you will have to change your parameters to text and text or number (depending on what you want to pass)
Let me know what it is you are trying to do with this function & I will try to help you more.

ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------

 

[Petemush]

Thanks Ben,

I originally was passing the values as Strings, Currency and Integer etc but had type mismatch errors, even when sending [Field Name].Value to the Function.

What I'm trying to do with this function is (and I hesitate to explain because I fear that there's a much simpler,built in way to do it and I'll now appear dumb!) create a table containing the records that match all the values that a user has input on a form.

I.e. A form pops up with checkboxes representing the fields, the user clicks the ones they want to enter values for, those fields become enabled and the user enters data in them all. The user then clicks a find button and all the records with matching data appear to cycle through. I've been ultra lazy and not investigated filters so that's why I fear this all pointless!

Thanks

Pete

 

[Quehay]

Suggestion: Use a list box to hold the field names. Why? This will allow you to have a code routine iterating through the List.ItemsSelected array. You'll probably still have to hard code the fields in the list, since you're probably exposing choice on just a few of the fields in the recordset.

Then, rather than building a form recordset based on the filter fields and their values, build a listbox that shows the records that show the filtered recordset. This allows for simultaneous visual comparison of records rather than having to click back and forth from record to record in the form.

Then in the results listbox set the click event to go to that particular record. (Or if you have lots of records make the form.RecordSource property = to &quot;SELECT * where KeyField =&quot; forms!form!lstLookup. This technique could be used for the form in general, i.e., don't bind the form to an entire table if you don't need it; this doesn't make a difference with < 2000 records but it does with big tables.

*I'm assuming that you know how to do these things--I'll gladly elaborate on specifics if needed.