Pass parameters to Query via QryDef (1 Viewer)

[tmyers]

I have been reading how I can pass parameters to a query using VBA but am struggling to find an example I fully understand.

So say I have a form that when opened by one user, the parameter for a yes/no field is false but for another I would want it to be true, how would I change that on the go? Would I set a parameter via the parameter window and add that field as a yes/no? Would I make the criteria for that field a global variable that during my forms on open event determines if it should be true or false? Or is there another method I am not thinking of? The next step would be how would you do the same for a form whos record source is a query that was built in the form and not a separately saved query?

I am just having a hard time wrapping my head around it.

 

[Gasman]

Here is a snippet by @moke123 from another forum.

Const Sql_Insert As String = _ 
          "Insert into tblPeople" & _ 
          "(FirstName,MiddleName,LastName,Suffix) " & _ 
          "Values(p0,p1,p2,p3)"


     With CurrentDb.CreateQueryDef("", SQL_Insert)
          .Parameters(0) = Me.FirstName
          .Parameters(1) = Me.MiddleName
          .Parameters(2) = Me.LastName
          .Parameters(3) = Me.Suffix
     .Execute dbFailOnError
     .Close
End With

Search here for P0 or QueryDef and you should find plenty of examples.

 

[tmyers]

I see and I am searching through the forum and reading everything I can find.

Out of curiosity though, using just a yes/no field as my criteria in a WHERE clause, would it be easier to create a function that I can flip back and forth via VBA depending on various conditions? Main reason I ask is I do tend to use yes/no fields a lot and the main way I flip between current records and historical records is changing the criteria for the yes/no between true and false. For that particular instance and at a glance, it would seem that would be the less cumbersome way to do it. However the moment any other data type is being used QueryDef seems to be the only option correct?

 

[Gasman]

All of those things really. There are generally more ways to skin a cat in computer programming, whilst some might be better than others.
If you had a TempVar that held True of False, then you could just use WHERE fieldname = & TempVar("YN") (Untested)
That way nothing to change in the query once created.

 

[theDBguy]

The question is what kind of query is it and what you want to do with it? For example, is it a SELECT query and are you trying to open a recordset?

 

[jdraw]

TMyers,

Please provide some context. What is the purpose of the YN field and who/how would it get valued? If it is to restrict some users from certain features, then what "rules" apply?
Can you lead us through a description of how and when the values would be used?

 

[tmyers]

JDraw, the purpose of the YN field to show if the action has been completed or not. So if the WHERE for the Completed is set to false, then you are looking at active task whereas when it is set to true, you are looking at history/completed task.

The exact case for me is a user who is actively working on the task vs those who want to view it once it is done. So for one user the form opens and shows all records where completed is false and as they are set to true, disappear. The opposite would be for the second user where they would see all task where completed is set to true and none that are set to false.

Long story short, this is the first time I have really tried an honest attempt at reusing the same form but displaying different records depending on user.

 

[Gasman]

So either give them a popup form to select which to see, then set the TempVar and open the main form, OR if they are allowed to see both, you could just have an option group to filter Active or Completed? as perhaps someone today is working on Active and tomorrow it might be Completed, or they might need to switch from one to the other? Then you could just requey the form instead of setting a filter.

As I said, lots of ways to skin the cat.

 

[Pat Hartman]

Users would NEVER open a query directly. Users interact with forms and reports. It is not safe for them to open queries directly. Assuming the query does not have hard coded criteria, you would use the WHERE argument of the OpenForm method when you open the form.
One way is to have a control on the form where the user chooses True or False. We need more context to give a more specific answer. You obviously do not want to hard-code user IDs in the query so you need to put the "open" command in context.

DoCmd.OpenForm "yourformname", , , "YourField = " & Me.txtTrueFalse

 

[tmyers]

After reading more while I was on vacation last week I was able to get the form to open in the different states using Pats example but modified a wee bit. I added an unbound control that is not visible to the user that is set to true or false when the form is opened based on the login of the person opening the form then made that control the criteria of the query to determine if it is looking for records that are true or false (so either complete or not complete respectfully).