Solved

using table in access for prompts  - Access 2010

Posted on 2014-09-21
2
228 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 48

Accepted Solution

by:
Dale Fye 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

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…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

636 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