Combining Enter Perimeter Values for a Button

[Stevestan]

I have a button in a form which runs four queries after a user presses it. Two of the queries ask for a start date to be entered so that it can look up specific records. The start dates required for both quieres will always be the same, but for the queries to correctly search the records, the start state must be entered; thus the user gets 2 consective prompts when he or she presses the button. Is there a way to combine the two prompts so the user doesn't have to enter the same thing twice in a row? Thanks in advance.

[ADezii]

Have the User enter the Start Date on the Form and eliminate the Prompts altogether. In the Queries, set the Criteria of the [StartDate] Field to:

Forms![<Form Name>]![StartDate]

[Stevestan1]

That sounds like a good idea. I have a lot of other buttons on the form and I don't want to change the interface integrity of it by putting in boxes where you can directly enter text into. Is there a way to combine the 2 search prompts any other way?

[NeoPa]

Steve,

1. It is possible. It is not recommended, but it is possible using InputBox().
2. The most user friendly option is certainly to use a form, even if you have to open a new one specially. When using InputBox() it is necessary to modify the SQL used. This can involve creating temporary QueryDefs just to run the query. Messy!
3. I notice you are using a different and new user name. While this is acceptable simply to contact an administrator to ask for help resolving your issue, we do not allow users to run multiple accounts in the normal course of events.
   
   Please PM me any difficulties you may have and we'll see what can be done to get you back using your proper account.

-NeoPa (Admin).

[ADezii]

NeoPa offers a viable, and unique, alternative in Post #4, and if you are interested in implementing it, I'll be happy to point you in the right direction. I would, however, need the SQL for the two Queries posted. My approach would be to directly modify the SQL Property of the two Queries based on the Value returned by the InputBox() Function, then execute them. The only Prompt would be that of InputBox() itself. With minimum Validation, this approach should work quite well.

[NeoPa]

I'm not sure it's a responsible approach simply to fiddle with existing QueryDefs ADezii, or were you referring to executing the resultant SQL strings?

If the former, then see my comment in post #4 about using Temp QueryDefs. This would be a safer approach as you cannot know what else these QueryDefs are used for and fiddling with their design for one job is unlikely to be a safe approach.

If, on the other hand, executing the SQL strings is intended, then you need to know that only action queries can be executed in that way.

[ADezii]

Depending on the specific circumstances, my original concept was to:

1. Capture the SQL from the Query
2. Modify the Query's SQL directly for specific Criteria
3. Open the Query
4. Restore Original SQL
   

   Dim strSQL As String
   Dim strSQL2 As String
    
   'Capture Original SQL
   strSQL = CurrentDb.QueryDefs("qryEmployees").SQL
    
   'New SQL Statement based on Criteria
   strSQL2 = "SELECT * FROM Employees WHERE [TitleOfCourtesy] = '" & "Mr." & "';"
    
   CurrentDb.QueryDefs("qryEmployees").SQL = strSQL2
    
   DoCmd.OpenQuery "qryEmployees", acViewNormal, acReadOnly
    
   'Restore Original SQL
   CurrentDb.QueryDefs("qryEmployees").SQL = strSQL

5. After careful consideration, I guess a Temporary QueryDef may be the way to go.

[NeoPa]

It's probably safer as :

1. The user may not have access rights to update the query.
2. Another user may attempt to use the query during the time it is in a modified state.

Multi-user can be a bitch huh :D

[ADezii]

Both points well stated and accepted. A Temporary QueryDef it is!

[Stevestan1]

How do you see the SQL code for queries you make in design view? I know you can go into VBA mode with the code button if you are looking at forms, but the same button is grayed out when you are looking at queries. Where would you put the inputbox commands in?

[ADezii]

First things first:

1. In the Database Window
2. Click the Queries Tab
3. Select a Query
4. Click Design
5. View ===> SQL

[NeoPa]

From Design View select View / SQL View.

The InputBox() call, if used, would be in the VBA only.

[ADezii]

Hello NeoPa, just a errant thought that came to me while I was getting a shower (scary thought, I know!). Temporary QueryDefs are a great little tool, but there are only two things that you can do with them, since they have no Visual Component:

1. Execute them if they are Action Queries (don't think that this is the case here)
2. For Non-Action Queries, assign their Output to a Recordset.

Assuming we are dealing with Non-Action Queries, this would leave us high and dry, since I'm assuming that the OP is looking for some sort of visual display.

[NeoPa]

Good point ADezii. That takes me back (I dealt with all this a long while back now).

I have my own Temp QueryDef objects that I manage and delete when done with. They're actually named so not properly Temporary, but I just use them that way. I was referring to these and forgetting that wouldn't make much sense.

I'd be happy to post my routine for creating them, but it does have a few dependent procedures so probably wouldn't run for you. It might give you a clue as to the logic though if you feel that would help.

If you're really interested I'll look at paring it down to work in a more portable environment, but I'm not keen to unless there's real interest.

Fundamentally, for a situation such as this, it copies the QueryDef that is required and modifies the SQL. This new object is deleted when it's finished with.

I believe this sort of thing can more easily be accomplished in private FE databases (Those where each user has their own local copy).

[ADezii]

I'm actually now leaning toward creating an Actual Query, dynamically assigning the SQL for it, Open it for visual display so the User can actually see the results, then at some point Delete the Object. This should eliminate the potential Multi-User problem as well as access rights. What is your slant on this approach?

[NeoPa]

That's pretty well exactly what I've done in my databases ADezii (See post #14). If you're interested in seeing any of my code just let me know.

[ADezii]

@Stevstan - I'm getting ready to go on Vacation, but before I did, there is something that I wanted to do in an attempt to gain some closure as far as this Thread goes. I created a Demo Database, based on the Orders Table of the Sample Northwind Database, that illustrates virtually all the points discussed in this Thread, plus a few others. Please keep in mind NeoPa's major contribution to this Thread also. The code will demonstrate the following:

1. Use of the InputBox$() Function to retrieve what will be a Parameter (DATE) to be used in Multiple Queries.
2. Validation on that Value obtained from InputBox$ to make sure that a Value was entered, the Value is an actual Date, and the Value falls within a specified Range.
3. Dynamic generation of SQL Statements using that Value retrieved from InputBox$.
4. Creation of 2 Queries using that Value in an opposing manner, namely: >= and <.
5. Opening both Queries and Tiling then Horizontally.
6. How to DELETE these Temporary Queries at some appropriate point.
7. The basic Code Segment follows:
   

   Dim strDate As String
   Dim qdf As DAO.QueryDef
   Dim qdf_1 As DAO.QueryDef
   Dim qdf_2 As DAO.QueryDef
   Dim strSQL_1 As String
   Dim strSQL_2 As String
    
   strDate = InputBox$("Enter a Valid Date in the Format mm/dd/yyyy:", "Date Entry")
    
   'Let's perform some Validations on the Return Value of InputBox$()
   If strDate = "" Then    'No Value entered, then OK or User clicked on Cancel
     Exit Sub
   ElseIf Not IsDate(strDate) Then     'Must be a Valid Date, not some erroneous Value
     MsgBox "[" & strDate & "] is not a Valid Date" & vbCrLf & vbCrLf & _
            "You must enter a Valid Date in the Format mm/dd/yyyy", vbExclamation, "Invalid Date"
               Exit Sub
   ElseIf CDate(strDate) < #8/4/1994# Or CDate(strDate) > #6/5/1996# Then    '>=8/4/1994 And <= 6/5/1996
     MsgBox "The Date entered must be between 8/4/1994 and 6/5/1996", vbExclamation, "Invalid Date"
       Exit Sub
   End If
    
   'If you get here, the User entered a Valid Date within the Range 8/4/1994 to 6/5/1996
   strSQL_1 = "SELECT * FROM Orders WHERE [OrderDate] >= #" & CDate(strDate) & "#;"
   strSQL_2 = "SELECT * FROM Orders WHERE [OrderDate] < #" & CDate(strDate) & "#;"
    
   With CurrentDb
     Set qdf_1 = .CreateQueryDef("qryOrders_1", strSQL_1)
     Set qdf_2 = .CreateQueryDef("qryOrders_2", strSQL_2)
   End With
    
   DoCmd.Minimize      'Minimize Main Form
    
   'Open both Queries
   DoCmd.OpenQuery "qryOrders_1", acViewNormal, acReadOnly
   DoCmd.OpenQuery "qryOrders_2", acViewNormal, acReadOnly
    
   'Tile all Windows Horizontally
   DoCmd.RunCommand acCmdTileHorizontally

8. At some point, you would want to DELETE these Temporary Queries:
   

   On Error Resume Next
   'You should DELETE the Temporarily created Queries at some point,
   'that can only be determined by you
   CurrentDb.QueryDefs.Delete "qryOrders_1"
   CurrentDb.QueryDefs.Delete "qryOrders_2"

9. I've posted the code for anyone who does not wish to Download the Attachment, but I would highly advise you (Stevstan) to download the Attachment in order to get a clear picture of exactly what is going on.

[Stevestan1]

Thanks for this code Adezil. I tried incorporating it into my queries, but can't get it to run smoothly. Here is my VBA code for the two queries that ask for the user input before I added any of the code you provided.

SELECT DISTINCT rpt_vwCaseData.CaseID, rpt_vwCaseData.Date_ST, rpt_vwCaseData.TeamID_PrimaryAgent_ST, rpt_vwCaseData.UserID_CreatedBy_ST, rpt_vwCaseData.CaseTypeName, rpt_vwCaseData.sIssueName, rpt_vwCaseData.DateClosed, rpt_vwCaseData.sCustID INTO IRSJake
FROM rpt_vwCaseData
WHERE (((rpt_vwCaseData.Date_ST) Between [Queries]![< QS_AllcasesIRS_STEP1>]![enter first day of  reporting month] And [enter first day of following month]) AND ((rpt_vwCaseData.TeamID_PrimaryAgent_ST)=40));

SELECT DISTINCT rpt_vwCaseData.CaseID, rpt_vwCaseData.Date_ST, rpt_vwCaseData.TeamID_PrimaryAgent_ST INTO AllCasesTeam40
FROM rpt_vwCaseData
WHERE (((rpt_vwCaseData.Date_ST) Between (Now()-395) And [enter first day of  reporting month]) AND ((rpt_vwCaseData.TeamID_PrimaryAgent_ST)=40));

Any suggestions on how to integrate the code you provided into these?

[ADezii]

At this point, I feel as though I would ned to see the Database. Any hance of uploading it, or a subset of it, pointing the specifics along the way?