Solved

using table in access for prompts  - Access 2010

Posted on 2014-09-21
2
213 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
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now