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?