Solved

using table in access for prompts  - Access 2010

Posted on 2014-09-21
2
222 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

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.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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, when working with VBA, learn some techniques for writing readable and easily maintained code.

740 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