Parameter query as forms record source (1 Viewer)

[mcdhappy80]

I have a query that is basically the same for several of my applications, the only thing that differs is value in the WHERE clause. I know that several applications can use this query if I put it in code library and make a reference but here's what troubles me:

I would like to create that query as an database object (not to recreate its record set via VBA again and again), open it as a record set with the

Dim rs As Dao.Recordset
Set rs = CurrentDb.OpenRecordset("qryQuery1")

command (not to use OpenRecordset (strSQL)), but before I open its RS to be able to supply that parameter value trough VBA.
Can this be done and how?

Thank You

 

[vbaInet]

Not entirely sure what you're after but this might help:

dim db As dao.database
dim qdf As queryDef

set db = currentDb
set qdf = db.createQueryDef("New Query Name here", "SQL here")
[COLOR=Blue][B]qdf.Parameters(0) = "David"[/B][/COLOR]

Or

[COLOR=Blue][B]qdf.Parameters![FieldName] = "David"[/B][/COLOR]

 

[DCrake]

Don't know if this is exactly what you are implying but here is a link to a sample that may help you. Remember to read the documentation though.

 

[mcdhappy80]

I've solved the problem guys, thanks for Your replies, I will post how I did it later and maybe that will cast some light on what I was reffering thank You again.

 

[vbaInet]

Great!! And thanks for proposing to share your solution.

 

[mcdhappy80]

Here is it:

'*** CREATE PARAMETERED QUERYDEF (Query) ***
Dim db As DAO.Database
Dim qdf As QueryDef

Set db = [B]CodeDb [/B]' Because this code runs from Library Reference
' Otherwise [B]db [/B]would be equal to [B]CurrentDb[/B], when the code resides in running
' application

Set qdf = db.QueryDefs("qryDataForLinking")

qdf.Parameters("app") = app
qdf.Parameters("ext") = ext
qdf.Parameters("user") = user

'Debug.Print qdf.Parameters("app").Value
'Debug.Print qdf.Parameters("ext").Value
'Debug.Print qdf.Parameters("user").Value
'*******************************

Also in the Design View of the query You need to define parameters in the Show\Hide section. As You can see here I have three parameters app, ext, and user.
In the query itself You enter the parameter(s) as follows:

WHERE (((tblApplication.txtApplication)=[B][app][/B]) AND ((tblAppExtension.txtEks)=[B][ext][/B]) AND ((tblUser.txtUser)=[B][user][/B]))

Pay attention that there are no single quotation (') marks although data type of all three parameters is text.

What I am achieving now with this, that I can have one query in Database Library (several other applications reference to it), that is used as a data source for the same form each application has, only the filter (parameter) which is passed to query (QueryDef) differs for each application.
Plus, I don't have to hard code SQL strings through VBA because each change on them (in case when you have 15 apps, and same SQL string in each) takes a lot of time to modify later (but I guess all the experienced programmers knew that by now ).

 

[Funkbuqet]

What do you have to do to get the parameter query with the parameters specified to open a report based on that query? I am using the below code and when the report opens it still prompts me to enter the ID parameter instead of accepting the one I have in the code. When I bug check it and hover over the parameter set it says "qdf.Parameters("[ID]") = "4" (Which is the correct value of Me.ComplaintReportID).

Private Sub e_mail_Click()
    
Dim db As DAO.Database
Dim qdf As QueryDef
    
Set db = CurrentDb
Set qdf = db.QueryDefs("ComplaintReportQuery")

qdf.Parameters("[ID]") = Me.ComplaintReportID

DoCmd.OpenReport ("ComplaintReportingReport")

End Sub

What am I doing wrong? any insight would be helpful. Thank you.

 

[Guus2005]

I was trying to get an answer to the same question.
I know that what you are trying doesn't work because a parameter is a parameter is a parameter.
Filling the parameter using a querydef object doesn't work because the query the report is connected to is not the same. Perhaps in name but not in memory.

You could do a replace on the parameter "ID" in de SQL string and save the string back into the original query, thus loosing the original.

dim strsql as string
dim qdf as querydef

set qdf = currentdb.querydefs("ComplaintReportQuery")

strsql = replace(qdf.sql,"[ID]",Me.ComplaintReportID)

qdf.sql = strsql
qdf.close

Loosing the original is ofcourse not what you want but i think you can figure that one out.

I want to run a parameterized query and get the result in a datasheet.
I'll continue my search.

HTH

 

[mcdhappy80]

What do you have to do to get the parameter query with the parameters specified to open a report based on that query? I am using the below code and when the report opens it still prompts me to enter the ID parameter instead of accepting the one I have in the code. When I bug check it and hover over the parameter set it says "qdf.Parameters("[ID]") = "4" (Which is the correct value of Me.ComplaintReportID).

Private Sub e_mail_Click()
    
Dim db As DAO.Database
Dim qdf As QueryDef
    
Set db = CurrentDb
Set qdf = db.QueryDefs("ComplaintReportQuery")

[B]qdf.Parameters("ID") = CInt(Me.ComplaintReportID) ' if IDs data type is integer[/B]

DoCmd.OpenReport ("ComplaintReportingReport")

End Sub

What am I doing wrong? any insight would be helpful. Thank you.

First loose the square brackets [] around the ID parameter, then if the data type of the ID is integer You shouldn't write "4" but just 4.

The parameter name of any query should be entered as I suggested.
I've put the value in the CInt() function because I assumed that the type of parameter is integer.

Now In order to open a report that is based on parametered query You need to set the parametered query as and record source in the reports On Open event, here is a piece of example:

Set qdf = db.QueryDefs("frmPitanje_RecordSource")
qdf.Parameters("PitanjeID") = CInt(slucajniBr)

Set Me.Recordset = qdf.OpenRecordset()