Can queries get field names from forms? 2

[347pg]

My form has controls that run queries when clicked. I would like to cut down the number of queries by passing the name of a field to the query, i.e. I'd like to run the same query for multiple controls but varying the field it uses as criteria based on the form control. I tried to pass the name via a text box. It works to a point but asks me for the parameter, which I defined in the VBA code (so it's not passing it). I've tried using IIf as well which does works, but only for one field. I tried using 'else' and 'or' with the IIF in the field line of queries, but got an error.
Any suggestions?

 

[bubba100]

Have you tried something along the following?

Dim dbCurr As Database
Dim qryStart As QueryDef
Dim prm As Parameter
Set dbCurr = CurrentDb()
Set qryStart = dbCurr.QueryDefs("YourQuery")
For Each prm In qryStart.Parameters
prm.Value = Eval(prm.Name)
Next prm

 

[347pg]

Bubba

Here is my code
When I click on the War button, Me.txtSearchFieldName is set to War.


Private Sub chkTrigWar_Click()

DoCmd.SetWarnings False

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQL As String
Set db = CurrentDb

Me.txtSearchFieldName = "War"

DoCmd.OpenQuery "uqryMobButton_all"

DoCmd.Close acQuery, "uqryMobButton_all", acSaveYes

DoCmd.SetWarnings True

End Sub


Once my query (uqryMobButton_all) runs, I have a field set to [tblSearchDBMob].[Forms]![frmDispMobInfo]![txtSearchFieldName].

Instead of setting the field to War in the tblSearchDBMob table, Access asks me for the [tblSearchDBMob].[Forms]![frmDispMobInfo]![txtSearchFieldName] parameter.
Is it impossible to pass a variable taht will take place of a field name in a query? I just want to pass one field name when a button is pushed and another single field name when another button is pushed.

Thanks

 

[MajP]

If I understand correctly I do not think there is a way to do that. This is what I understand. You want to pass a field name to a query so if you have the fields "War" and "Battle" you would like to alter the query for example


Select War from tblTable where War = "Some Value"
and next time
Select Battle from tblTable where Battle = "Some Value"

Passing in values to search is simple, but changing the field names is not. You will have to build the queries in code

You can call functions from query, but even if I built a function that returns a field name:

public fldName as string

Public function getFldName() as string
getFldName = fldName
end function

I can not use this in a query such as

Select getFldName() from tblTable.

Actually it will return a query, but not what you think. It will be a query were all records have the field name in it.

 

[MichaelRed]

creating the "query" via code should not be all that difficult

e.g.:

Dim dbCurr As Database
Dim qryStart As QueryDef
Dim prm As Parameter
Dim strSQL as String
Set dbCurr = CurrentDb()
Set qryStart = dbCurr.QueryDefs("YourQuery")

strSQL =  "Select " & Chr(34) & prm & Chr(34) & " " _ & "From [COLOR=red][b]tblName[/color][/b];"

docmd.runsql strSQL

but a couple of assumptions ...

[tab]the tblName (in red needs to be replaced by Y O U R query recordset source (assuming it is a simple table)

[tab]the query is an action query, so you need to 'replace' (or otherwise ammend) the 'Select' keword with the appropiate action clauses (Insert into; Update; ...)

a relatively easy way to do the whole thing would be to copy the existing query (SQL) into your procedure with the appropiate assignment statement (strSQL = ...) and modify the lilttle bit to have the field name being selected set to the parameter

then, again this would probably be easier on all if you had included the complete problem in your posts (including the query's SQL)

on the other hand what you do with the query results may pose additional interesting issues



MichaelRed

 

[MajP]

Also why are you concerned about the number of queries? If you are worried about performance and or size of the database, the number of queries is not an issue. And stored queries are more efficient due to jet optimization.

 

[347pg]

Thanks for all the help. I'm not overly concerned about the amount of queries, it's just that they were so similar except for the one field, that I thought I might be able to re-cycle one query over and over again. But I didn't know about stored queries being more efficient. That makes me feel better. I was thinking of copying the SQL code to the vba editor as MichaelRed suggested, but now I think I'll leave it as is.
Thanks again.

 

[MajP]

Stored queries are more efficient than queries written in code because when the query compiles JET optimizes the query. However, for most queries you may never see any performance difference. Sometimes on a complex query you will notice the difference. So if it saves you a lot of development time then write the query in code, but if you can copy and paste a lot of queries and change a field then you are just as well off.