vba to show if query has parameter

[Davyhunn]

Hi Guys,

Im pretty new to this site so please forgive me if I dont do things how they are suposed to be done.

I am wondering if there is a was to know if a specific field in a query has a parameter. The reason I am wanting to know this is because I have a form with a combo box that lists all queries (query names) and would like to add a subform which would show the relevant text box's for those parameters.

I am planning on showing/hiding each text box with as if statment that ends with .visable = true/false.

lets say for example the query ("qryTest") has 3 fields FirstName, LastName, Age with Age having the parameter forms!menu!age>=21. i would only like visable the text box for "Age" on the subform and have the other fields invisable.

I think I can work out the coding for everything i need apart from the initial vba to work out if the query field has a parameter.

Please help anyway you can or if there is a beter way of doing what i want.

Thanks

 

[plog]

I don't think you can get it in one easy function. I think you have to extract down to it.

Dim qd_DataSources
Dim str_sql As String
Set qd_DataSources = CurrentDb.QueryDefs("YourQueryNameHere")
str_sql = qd_DataSources.SQL

The above code snippet will get the SQL of a query ("YourQueryNameHere"). From there you can parse the SQL to find the WHERE clause, then you can parse that to find any parameters.

 

[richsql]

... how about


Dim qdf As DAO.QueryDef
For Each qdf In CurrentDb.QueryDefs
If qdf.Parameters.Count > 0 Then

Debug.Print qdf.SQL

End If
Next qdf


You could then do as plog suggests

 

[CJ_London]

if the idea is that your user completes a field in a form to be used by the query, then there are no parameters as such, they are criteria.

Parameters will appear in a query as

[COLOR=red]PARAMETERS [enter name] Text ( 255 ), [enter ID] Long;
[/COLOR]SELECT *
FROM resWeb
WHERE (((resWeb.ResID)=[enter ID]) AND ((resWeb.ResGroup)=[enter name]));

In a query which references a form your query would look like

SELECT *
FROM resWeb
WHERE (((resWeb.ResID)=[COLOR=red]forms!myform!txtID[/COLOR]) AND ((resWeb.ResGroup)=[COLOR=red]forms!myform!txtResGroup[/COLOR]));

So if the former style you would reference the querydef parameters and if the latter you would need to scan the sql for something beginning with forms!

The former style as suggested by rich is an easier way to go but for you to decide

 

[jdraw]

Here's a small routine, that may help with what you are trying to do.

'---------------------------------------------------------------------------------------
' Procedure : qdfParm
' Author    : mellon
' Date      : 10/02/2015
' Purpose   : Routine to review all query defs in this database. Looking specifically for
' "Parameters *". If found print the qdf name , the count of parameters involved and SQL to the immediate window.
' After processing display count of total parameter queries in the database.
'---------------------------------------------------------------------------------------
'
Sub qdfParm()

          Dim db As DAO.Database
          Dim qdf As DAO.QueryDef
          Dim cnt As Integer       'count of queries with parameters
10        cnt = 0
20        On Error GoTo qdfParm_Error

30        Set db = CurrentDb
40        For Each qdf In db.QueryDefs
50            If qdf.SQL Like "Parameters *" Then
60                cnt = cnt + 1
70                Debug.Print "===== " & qdf.name & " == Count =" & qdf.Parameters.Count & vbCrLf & vbTab & qdf.SQL
80            End If
90        Next
100       Debug.Print "Total number of parameter queries in  this database is " & cnt
110       On Error GoTo 0
120       Exit Sub

qdfParm_Error:

130       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure qdfParm of Module Module3"
End Sub

Good luck with your project.

 

[richsql]

Actually there is no need to parse the where clause you can do it all with the querdef object

Dim qdf As DAO.QueryDef
Dim intCount As Integer
 
For Each qdf In CurrentDb.QueryDefs
          If qdf.Parameters.Count > 0 Then

                   For intCount = 0 To qdf.Parameters.Count - 1

                          Debug.Print qdf.Parameters(intCount).Name

                   Next

          End If
Next qdf

 

[Davyhunn]

Thanks for all your help and ideas. I have mashed a couple of them together (from plog and richsql) and have come up with the coding below. I am now having trouble with having a string as the objects name. Any suggestions with the highlighted code please?

Dim qdf As DAO.QueryDef
Dim intCount As Integer
Dim myqry As String
Dim showfield As String

myqry = Forms![menu]![MenuQuery]
Set qdf = CurrentDb.QueryDefs(myqry)

If qdf.Parameters.Count > 0 Then

For intCount = 0 To qdf.Parameters.Count - 1

Debug.Print qdf.Parameters(intCount).Name
showfield = qdf.Parameters(intCount).Name
showfield.Visible = True
​

Next
​

End If

edit:
Just to add I'm getting an "invalid qualifier" error message for "showfield".

 

[CJ_London]

you have showfield dimmed as a string - it needs to be the name of your control (and not it's control source)

try

me(showfield).visible=true