Solved

Access 2003 form, automatically input query parameter from list

Posted on 2016-11-10
5
48 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 4

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 35

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 35

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

773 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