Pass a parameter when calling a stored query... without the prompt.

[pyrrhus_finch]

I this is a simplifed version a saved query called "qryTest"

PARAMETERS [gUserID] Long;
SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');

UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');


how to i call the result from code or from the query manager and include the parameter so there is no prompt that comes up?


like
Me.ListNew.RowSource = "qryTest " & gUserID &";"
or
Select * from qryTest , 31 - where 31 is the value fed to the parameter


??? does anyone know? I know it can be called in APS like this:

Dim Conn
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open strConn ' strconn is my connection string
set rsP=createobject("adodb.recordset")
conn.qryTest 31
.....

I can't believe I can't do it from the query string!
Heeeellpppp!

 

[andrew93]

I haven't read your post in detail (maybe I should?) but the 'dlookup' function comes to mind - it can look up values from another 'domain' (e.g. query) using criteria. If you look it up in your VB help file (via Alt-F11) then it might answer your question.
Andrew

 

[neileg]

I've no experience of this but here's a couple of ideas.

Where is the variable and how is it collected? If you have a login form, you could keep this form open but hidden and refer to the control on the form.

Or you can build a string SQL query and concatenate your variable with the rest of the string.

 

[pyrrhus_finch]

I just want to call the following stored Query and feed in the required parameter.

PARAMETERS [gUserID] Long;
SELECT [Id],'Complaint' AS IncidentType FROM tblComplaints WHERE tblComplaints.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Complaint');

UNION ALL SELECT [Id],'Fall' AS IncidentType, FROM tblFalls tblFalls.[Id] IN (Select Incident_ID from tblNotification where Dept_ID IN (Select Dept_ID from tblUserDepts Where User_Id = [gUserID] ) AND Incident_Type = 'Fall');

Like: Select all from qryTest where gUserID = '31';

So the promt for gUserID does not appear.

That's it.

Using that type of query in my code the gUserID is a global variable from the login... but that's beside the point right now.
I should be able to to this in the Qry manager from a simple select statement... No?
I can't believe something so simple seems impossible. Just to feed the required parameter into the inital call.