Have a query prompt for input on different fields based on which button selected on

[Fred Chamberlin]

Using Access 2007, I have a form with 3 buttons. The OnClick event sets a global variable and each of them opens another form whose record source is the following query.

SELECT <several fields here> 
 
FROM ([tblEmployee Information] INNER JOIN [tblEmployee Training] ON [tblEmployee Information].ID=[tblEmployee Training].ID) INNER JOIN qryLawsonQueryByArea ON [tblEmployee Information].ID=qryLawsonQueryByArea.ID
 
WHERE Iif(CurrentTrainingType()=1,(qryLawsonQueryByArea.[Next First Aid/CPR]<=Date()+[How Many Days Out?]),Iif(CurrentTrainingType()=2,(qryLawsonQueryByArea.[Next Recert]<=Date()+[How Many Days Out?]),(Date()+[How Many Days Out?])))
 
ORDER BY qryLawsonQueryByArea.Name;

The function CurrentTrainingType returns the value of the global variable set in the OnClick event procedure.

Instead of getting only 1 prompt when any of the buttons is clicked, based on that variable, the query prompts for all 3.

Any ideas how to rectify?

[ADezii]

Just subscribing for now, must put thinking cap on! (LOL)

[ADezii]

1. Prompt the User in some manner in order to obtain the Number of Days Out. For this Demo, I used the InputBox() Function, but you can just as easily use a Text Box, Combo Box, etc. (Line #7).
2. Perform Validation on the Input (Line #10, 11), making sure that:
   1. Is is Not Null
   2. It is Numeric
   3. It is within a Specified Range
3. Define your SQL String based on the Input Parameter (Number of Days Out) (Line #14 - 19).
4. We will dynamically be creating a Query named qryTest, but we must first see if it already exists. If it exists, then DELETE it (Line #22 - 27).
5. Create a Query whose Name is defined by a Constant (conQUERY_NAME), and whose SQL was already defined in a String strSQL (Line #30 - 32).
6. The anticipation is killing me, so let's Open the Query and see the Results (Line #35)!

Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim qdfNew As DAO.QueryDef
Dim varDaysOut As Variant
Const conQUERY_NAME As String = "qryTest"       'Substitute if you like
 
varDaysOut = InputBox("Enter the Number of Days Out", "Days Out Prompt")
 
'The Number of Days out cannot be NULL, must be Numeric, and cannot be < 1 or > 50
If IsNull(varDaysOut) Or Not IsNumeric(varDaysOut) Or varDaysOut < 1 Or _
                         varDaysOut > 50 Then Exit Sub
 
'Define the SQL String
strSQL = "SELECT <several fields here> FROM ([tblEmployee Information] INNER JOIN " & _
         "[tblEmployee Training] ON [tblEmployee Information].ID=[tblEmployee Training].ID) " & _
         "INNER JOIN qryLawsonQueryByArea ON [tblEmployee Information].ID=qryLawsonQueryByArea.ID " & _
         "WHERE Iif(CurrentTrainingType()=1,(qryLawsonQueryByArea.[Next First Aid/CPR]<=Date()+" & _
         varDaysOut & "),Iif(CurrentTrainingType()=2,(qryLawsonQueryByAre a.[Next Recert]<=Date()+" & _
         varDaysOut & "),(Date()+" & varDaysOut & "))) ORDER BY qryLawsonQueryByArea.Name;"
 
'If the Query conQUERY_NAME exists, DELETE it
For Each qdf In CurrentDb.QueryDefs
  If qdf.name = conQUERY_NAME Then
    CurrentDb.QueryDefs.Delete conQUERY_NAME
      Exit For
  End If
Next
 
'Dynamicall Create the Query conQUERY_NAME
With CurrentDb
  Set qdfNew = .CreateQueryDef(conQUERY_NAME, strSQL)
End With
 
'Open the Query conQUERY_NAME, and see the Results
DoCmd.OpenQuery conQUERY_NAME, acViewNormal, acReadOnly

P.S. - Obviously, I have no way of actually testing the SQL Statement, so I'll leave that up to you. The Logic, however, has been tested and does appear to be sound.

[Fred Chamberlin]

It works great. Thanks for your help.

[ADezii]

You are quite welcome, Fred.