Using Code to Modify Query Parameters

  • Thread starter Thread starter Amy E. Baggott
  • Start date Start date
A

Amy E. Baggott

I have a query that generates a list of companies that are missing one or
more elements of their application (as indicated by fields the user checks to
indicate if we have received a deposit or a signed copy of the app). The
query is set up to pull the exhibitor if DepositReceived or SignedReceived is
false. However, when we send out reminders, we send different messages
depending on whether they are missing Deposit, Signature, or Both. I'd like
to set up a dialog box so that the user can select which group to run using
an option group. However, I'd like to know if there's any way I can put
instructions in the code behind the form to run qryIncompleteApps where
DepositReceived is False and SignedRecieved is True if they select 1, the
opposite if they select 2 or DepositReceived and SignedReceived are both
False if they select 3. Is there a way to do this using the same query for
all three options, or will I need to use three separate queries?
 
One way to do it is to have two hidden textboxes on the form that represent
the values for DepositReceived and SignedReceived. When you go to run the
query, first fill in the values for those two hidden fields with the values
you want based on your option group, something like

Select Case optionFieldName
Case 1
txtDepositReceived = 0
txtSignedReceived = -1
Case 2
txtDepositReceived = -1
txtSignedReceived = 0
Case 3
txtDepositReceived = -1
txtSignedReceived = -1
Case Else
' whatever you need to do if they're both 0
End Select

In the criteria for DepositReceived in the query, put
Eval("Forms!frmWhatever!txtDepositReceived"), in the criteria for
SignedReceived in the query, put
Eval("Forms!frmWhatever!txtSignedReceived"). You just need to make sure that
an option is selected before executing the query. That's typically the way I
handle those situations. There are also ways to programatically define
parameters using a Command object, or you can define the SQL text in VBA,
modifying the WHERE clause based on the option selected.

Jim B
 
That worked! I can't believe it was that simple. I'd been plowing through
my Big Book of Code looking for a way to modify the query in VBA when all I
had to do was modify the form. <slaps forehead> I guess I do have a tendency
to overthink things.

Thanks!
 
Back
Top