VBA not working for saved query parameters (1 Viewer)

[cheekybuddha]

If that is not possible, fine, I'll figure out alternatives.

Use a form for display.

You can set the form's recordset to your parameterised qdf rst

 

[tvanstiphout]

So did adding the defined parameters help?
Adding line breaks makes it much easer to see what you are doing.

PARAMETERS [parExamFreq] Text ( 255 ), [parExamPeriod] Text ( 255 );
SELECT tblEquipment.EquipmentID
FROM tblEquipment
WHERE ((([parExamFreq])="Weekly")
  AND (((DatePart("yyyy",[tblEquipment].[StartOfServiceDate])*1000)+DatePart("ww",[tblEquipment].[StartOfServiceDate]))
       <=fExamPeriod([parExamPeriod],"Weekly")))
  OR ((([parExamFreq])="Bi-Weekly")
 AND (((DatePart("yyyy",[tblEquipment].[StartOfServiceDate])*1000)+Round((DatePart("ww",[tblEquipment].[StartOfServiceDate])\2)+0.04))
    <=fExamPeriod([parExamPeriod],"Bi-Weekly")))
  OR ((([parExamFreq])="Monthly")
 AND (((DatePart("yyyy",[tblEquipment].[StartOfServiceDate])*1000)+DatePart("m",[tblEquipment].[StartOfServiceDate]))
    <=fExamPeriod([parExamPeriod],"Monthy")))
  OR ((([parExamFreq])="Quarterly")
 AND (((DatePart("yyyy",[tblEquipment].[StartOfServiceDate])*
    1000)+DatePart("q",[tblEquipment].[StartOfServiceDate]))<=fExamPeriod([parExamPeriod],"Quarterly")))
GROUP BY tblEquipment.EquipmentID;

ps.
You can get rid of the function call
fExamPeriod([parExamPeriod],"Weekly")
by passing it's value as a parameter.

Job security: if the people upstairs ever define a new exam period, I will need to update and test all related queries, and who knows what else...

 

[Ken Sheridan]

What Tom says, 'Job security: if the people upstairs ever define a new exam period, I will need to update and test all related queries, and who knows what else...' underlines the importance of following Codd's Rule #1, and always storing data as values at column positions in rows in tables. As an example the following image is the model for a simple medical treatments scheduling database. You'll see the the intervals (d,m,ww,yyyy) and the interval values from each start date are stored in columns in the tables:

This makes it very easy to compute the treatment dates per patient per treatment in a query:

SELECT
    PatientTreatments.PatientID,
    PatientTreatments.StartDate,
    Treatments.Treatment,
    DateAdd([Interval], [IntervalValue], [StartDate]) AS TreatmentDates
FROM
    (
        Treatments
        INNER JOIN PatientTreatments ON Treatments.TreatmentID = PatientTreatments.TreatmentID
    )
    INNER JOIN TreatmentSchedule ON Treatments.TreatmentID = TreatmentSchedule.TreatmentID
ORDER BY
    PatientTreatments.PatientID,
    DateAdd([Interval], [IntervalValue], [StartDate]);

 

[dalski]

So how do I set the parameters so that when I have (as an example) DoCmd.OpenQuery "QueryName", acViewNormal it does not prompt for the parameters?

That is the question I'm trying to get answered.

If that is not possible, fine, I'll figure out alternatives. I'm not just trying to solve this problem, I'm trying to learn the ways to do things.

Persevere, you're close. I struggled with this also.

You're wrong on below, but logical so don't beat yourself. Pretty sure I did exactly that when experimenting also.

qdf.Parameters(sPar1).Value = sFreq

It's possible. If your intent is to set the parameter in VBA & run the query (you will not be prompted for parameters because you have set them in code) you have 3 options to set the parameter in code. 1 can take a variable & 3 referencing a literal. With 1 if your field has a special character you must enclose the field name in square brackets also [ ]. 2nd is via index nr, not recommended because could change if you add/ remove parameters later. Obvs a variable can be used for the criteria, for clarity:

qdf.Parameters!paramName = "strCriteria"
qdf.Parameters(0) = "strCriteria"
qdef.Parameters("paramName") = "strCriteria"

Then execute the query through the qdef object:

qdf.Execute

Well done, you are closing the querydef & recordsets after use, often overlooked & very important. As pointed out you've opened the query more than once, so if the parameters were set correctly you've then wiped them away. For now get rid of all the looping... & concentrate on just setting the parameters & execute the query. Then play around more with that jazz...

 

[RonPaii]

Persevere, you're close. I struggled with this also.

You're wrong on below, but logical so don't beat yourself. Pretty sure I did exactly that when experimenting also.

qdf.Parameters(sPar1).Value = sFreq

It's possible. If your intent is to set the parameter in VBA & run the query (you will not be prompted for parameters because you have set them in code) you have 3 options to set the parameter in code. 1 can take a variable & 3 referencing a literal. With 1 if your field has a special character you must enclose the field name in square brackets also [ ]. 2nd is via index nr, not recommended because could change if you add/ remove parameters later. Obvs a variable can be used for the criteria, for clarity:

qdf.Parameters!paramName = "strCriteria"
qdf.Parameters(0) = "strCriteria"
qdef.Parameters("paramName") = "strCriteria"

Then execute the query through the qdef object:

qdf.Execute

Well done, you are closing the querydef & recordsets after use, often overlooked & very important. As pointed out you've opened the query more than once, so if the parameters were set correctly you've then wiped them away. For now get rid of all the looping... & concentrate on just setting the parameters & execute the query. Then play around more with that jazz...

The second opening of query did not wipe out the parameters, it is completely independent of the first. The only way to set parameters of a query opened with docmd is form variables or temp variables.