Solved

Access 2003 form, automatically input query parameter from list

Posted on 2016-11-10
5
43 Views
Last Modified: 2016-11-11
I have a form which, when opened, asks for a parameter and then runs to completion, at which point Access closes. I need it to be able to read the parameter (an integer) from a table, and then close after it runs (like it does now), and then re-open and run again using the next parameter in the list (table).
0
Comment
Question by:QMBB
5 Comments
 
LVL 3

Expert Comment

by:bfuchs
ID: 41883129
you cant run code after access is fully closed, however you can minimize the form or the entire app and have a minimized form with a timer look for additional tasks to perform..
0
 
LVL 6

Expert Comment

by:nathaniel
ID: 41883365
what are you trying to accomplish? can you give more info regarding your requirement?
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
ID: 41883979
The form would not open and close.  The form would run a procedure that read each record of the controlling table and for each record, execute whatever procedure you currently are running.  The code loop could post the result to the existing form field so the existing procedure would not need to change.  You would just change how it runs.  At it's simplest, the code to run the loop would look like this:
Private Sub cmdRun_Click()
Dim db as DAO.Database
Dim qd as DAO.Querydef
Dim rs as DAO.Recordset

    Set db = CurrentDB()
    Set qd = db.QueryDefs!myquery
    Set rs = qd.OpenRecordset

    Do until rs.EOF
        Me.txtformfield = rs!parameter
        ''' do something
        rs.MoveNext
    Loop

    rs.Close
End Sub

Open in new window

0
 

Author Comment

by:QMBB
ID: 41884160
PatHartman,

That looks interesting, but I don't see how the form will get the next parameter for txtformfield. Are you saying it will automatically go down the records in the table? That's what i need. Once it's done with first record, go to second record, and so on.
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 41884217
That is what the code does.  It opens a recordset that presumably contains the list of values and as it reads each row, places the value in the form field and then executes whatever the current procedure is.  rs.MoveNext is what moves the record pointer though the recordset.  The check for EOF (End of File) ends the loop when you attempt to read past the last record.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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…

932 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

15 Experts available now in Live!

Get 1:1 Help Now