Solved

using table in access for prompts  - Access 2010

Posted on 2014-09-21
2
219 Views
Last Modified: 2014-09-23
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
Comment
Question by:morinia
2 Comments
 
LVL 16

Assisted Solution

by:Sheils
Sheils earned 100 total points
ID: 40335502
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
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 400 total points
ID: 40335513
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question