Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 235
  • Last Modified:

using table in access for prompts - Access 2010

Experts,

I am running a job in Microsoft Access that currently prompts for values.  
Ex,   start date
        end date
       perecentage

Is there a way to put these parms in a table so they don't have to be typed in each time.


In addition there are three queries that run that the parms must be typed in.  If a manual method is used, is there a way to run all three queries so the parms only have to be entered one time and not for each query.
0
morinia
Asked:
morinia
2 Solutions
 
SheilsCommented:
Yes for part 1; you can use a lookup table.

Yes for part 2: link your queries to a form and the parameters can be stored in the form.
0
 
Dale FyeCommented:
Can this be done: Absolutely.  

Easiest way to do it is to create a table that will hold those values, and a form to enter the values into.  Provide a way to open that form (maybe an Options button) or something like that.

Add a step to check whether the fields in that table are filled in prior to running the queries.  Generally, I don' simply run queries, they are mostly tied to forms or reports.  So if you are opening a report, you might want to use the Report_Open event to check and see if that form is loaded.  If not, load it and allow the user to set the values, then when the user clicks the close button on that form, you can simply hide it rather than closing it.  This will allow you to reference the controls on that form in the query that is the RecordSource of the Report.
Private Sub Report_OPen

    if currentproject.allforms("PopupFormName").isloaded = false then
        docmd.openform "PopupFormName", acNormal, , , ,acDialog 
    End If

End Sub

Open in new window

Use of the acDialog argument above means that the rest of the code associated with opening the form will not fire until the PopupForm is closed or hidden.  As I indicated above, don't actually close that form, set it's Visible property to False when you have validated the values with code similar to:
Private Sub cmd_Close_Click()

    if me.dirty then me.dirty = false  'writes the changes to the values to the table
    me.visible = false

End Sub

Open in new window

Then, in your query refer to those fields like:
SELECT * FROM yourTable
WHERE [StartDate] = Forms!PopupFormName.txt_StartDate
AND [EndDate] = Forms!PopupFormName.txt_EndDate
AND [Percentage] < Forms!PopupFormName.txt_Percentage

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now