dynamic query 1

[strantheman]

I want my query to work for any one of my search forms. Im using 2 variables on my forms to tell the query which column to search by, and where to get the user defined criteria from. theField, and theCriteria

SELECT [itemid], [lastmodified], [accession], [name], [description]
FROM item
WHERE [forms]![searchitem].[theField] Like "*" & [Forms]![searchitem].[theCriteria] & "*"
ORDER BY [forms]![searchitem].[theField] DESC;

How do I get access to evaluate [forms]![searchitem].[theField] as a field name in the table, and not as a string of text? Right now, if theField is "accession" instead of searching through the accession column, it compares the string "accession" to my search criteria.

I hope im being clear. I'd like to think that there's some sort of evaluate() function I can wrap theField in so that access recognizes it as a field in my table and not as a string.

thanks in advance. this will make it so I dont have to write a seperate query for every type of search one might perform.

 

[FoxProProgrammer]

Interesting question. I haven't seen this done before, but that certainly doesn't mean that it isn't possible. You might try putting brackets around [forms]![searchitem].[theField].

SELECT [itemid], [lastmodified], [accession], [name], [description]
FROM item
WHERE & "[" & [forms]![searchitem].[theField] & "]" & Like "*" & [Forms]![searchitem].[theCriteria] & "*"
ORDER BY [forms]![searchitem].[theField] DESC;

You probably don't need to concatenate the brackets, but I did it that way to make it stand out. If that doesn't work, you might try using a Select Case structure that picks the correct Query depending on the field. I realize that this is hard coding the field names in the SQL, but it is close to doing what you want and seems a bit simpler.

 

[Steve101]

Strantheman,

You basically need to (a) construct the entire query string as a variable, and then (b) assign the variable to the recordsource of the form. Looking at your SQL however, I think that you are not clear on how to express the syntax of controls on the form in the SQL.

I will assume that the form is called frmSearchForm, and that it contains two "unbound" controls called txtTheField, and txtTheCriteria. I will also assume that the same form contains the other fields which are bound to the underlying table called tblItem:

There are several approaches to this problem; I'll be illustrating only one of them:

(a) Add a button to your form; lets call it btnRun.

(b) In the OnClick event of the button, construct your SQL recordsource string as follows:

sq = "SELECT itemid, lastmodified, accession, name, description " & _
"FROM tblItem " & _
"WHERE " & Forms!frmSearchForm!txttheField & " Like '*" & _
Forms!frmSearchForm!txttheCriteria & "*' " & _
"ORDER BY " & Forms!frmSearchForm!txtTheField DESC; "

(b) Assign the constructed sql string to the RecordSource property of the form, as follows (ie. add the following line after the code above:

Forms!frmSearchForm.RecordSource = sq

(c) Save and open the form; enter VALID data into the two unbound fields, click the button and ensure that it behaves as expected.

Notes:
(a) You will need to add some form of error handling to this routine (when the basics are working, to ensure that you cater for the situation where invalid entries are made to the two parameters.

(b) Note the syntax with respect to the use of explanation marks and full stops. Full stops are used to designate "methods" or "properties"; Explanations are used to qualify controls on forms.

(c) You could achieve the same functionality by dynamically populating the various OrderBy, Filter and AllowFilters properties of the form.

(d) Ind design view, you might want to assign the tblItem table as the initial recordsource of the form; This way, all of the data is selected when the form is initially opened.

Hope that this helps,
Cheers,
Steve

 

[strantheman]

hi Foxpro and Steve101

Thanks for your replies!

Foxpro: Yes I too believe its got to be possible. I used to this this sort of dynamic SQL all the time in web languages like PHP and Cold Fusion. Access however seems to have its own way of doing everything, so im just not privvy to the right stuff as of yet.

Your concatenation idea looked good but there were syntax errors when I opened the report. Having a & directly after the WHERE threw a syntax error, as well as removing concatenation and just using WHERE [[forms]![searchitem].[theField]]

Steve101: I created the button and added the SQL as you instructed. I added another textbox for debugging so that I could see that the SQL was being constructed properly. The SQL looked perfect, and when I tested it seperately in a query it worked great. I should've known Code view was the way to go! Thanks so much.

However, now that it is the RecordSource of my form, how do I get my listreport report to use it as the Recordsource? This button calls a report called listreport to display the search results. Right now my listreport report has a property called Record Source and is set to a query. How can I set this property = to my new dynamic recordsource from the form?

You probably don't need to see it, but heres the final code within the onClick of my button:

Dim sq As String
Dim stDocName As String

Rem - the two dynamic pieces of this SQL. udname is the textbox the user fills in, and for each search button these are the only two parameters I need to change.
theField = "name"
theCriteria = udname

Rem - the dynamic code from Steve101
sq = "SELECT itemid, lastmodified, accession, name, description " & _
"FROM item " & _
"WHERE " & Forms!searchitem!theField & " Like '*" & _
Forms!searchitem!theCriteria & "*' " & _
"ORDER BY " & Forms!searchitem!theField & " DESC; "
Forms!searchitem.RecordSource = sq

Rem - my debug textbox displays the result
theSQL = sq

Rem - this calls the report that will display the search results
stDocName = "listreport"
DoCmd.OpenReport stDocName, acPreview

thanks again, this isnt exactly how I envisioned it since ill need to add this entire block of code to each search button, and if I ever change my query ill have to go edit each button, but its a lot closer than I was before.

 

[Steve101]

Strantheman,

I think that if you proceed as follows, you can achieve the sort of generic solution you're looking for:

(a) Create an unbound control on the form; lets call it sq; Make it visible for now, so that you see that it contains what you expect, but in production, you'll probably want to hide it.

(b) Modify the existing button click code to assign the SQL expression being built to this control; ie.

Forms!SearchItem!sq = "SELECT itemid, lastmodified, accession, name, description " & _
"FROM item " & _
etc etc

(c) In the OnOpen event of the report, add the following code:

Dim R as Report
Set R = Me
R.RecordSource = Forms!SearchItem!sq

This will populate the recordsource of the report with the contents of the form' fields sq field, and essentially apply the query as the record source for the report.

A couple of other points:

(d) At the top of your code, with the other declarations, you might want to declare an instance of the form itself:

Dim F as Form
Set F = Forms!SearchItem

After this within the procedure, instead of referring to the form as Forms!SearchItem, you can just use the declared variable; for example Forms!SearchItem!theField becomes F!theField.

(e) You can indeed adapt this code to being entirely generic, so that you do not have to repeat the code within each form. A couple of 'pointers' for your consideration:

(i) Add an additional control to the form, which controls a list of reports from which the user can select (ie. same single form services multiple reports)

(ii) Consider moving and adapting any 'common' code from within the button, to a global procedure within a module, and then call this procedure from within the button code.

There are other more advanced options as well, but this ought to suffice for now,

Good luck,
Steve

 

[strantheman]

Steve101 that was exactly what was needed. I used the debug textbox I had on searchitem form and had my report access that as the record source.

It works great! You earned your star buddy. thanks a lot!

Now, you mentioned storing this "sq = " portion of my code in a global procedure. How do I do that?

 

[Steve101]

Strantheman,

Go to the Modules tab of the main database window, and create a New Module. Within this module, insert a new Procedure, with a Form as an argument. An example which you'll have to adapt will suffice:

Function GenerateSQL(F as Form) AS String

F!sq = "SELECT * " & _
"FROM " & F!YourTable
"WHERE " & F!YourCriteriaField & " = '" & _
F!YourCriteriaValue & "'"
GenerateSQL = F!sq
End Function

Save this module; the name in this example isnt important, but lets call it modExample.

A couple of points to note about the above function:

(a) It takes the input form as an argument; hense it can be used against more that one form.

(b) It returns the SQL as its function name (a characteristic of any function; to return a value). This isnt strictly necessary here; you could be geenrating F!sq as the 'output' value anyway, but as with all things in life, there are options.

(c) To call this function from the report, you'd do the following:

(i) The OnClick code on the form would open the report
(ii) The OnOpen event on the report would have something like:

Dim R as Report: Set R = Me
R!RecordSource = GenerateSQL(Forms!SearchItem)

(d) And that should do the trick. There are probably better adaptations of the above, so that you dont need to hardcode the formname into the report's event ... food for thought for the next version of improvement,

Have fun,
Regards,
Steve