• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

Access 2003 form, automatically input query parameter from list

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
QMBB
Asked:
QMBB
1 Solution
 
bfuchsCommented:
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
 
nathanielIT ConsultantCommented:
what are you trying to accomplish? can you give more info regarding your requirement?
0
 
PatHartmanCommented:
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
 
QMBBAuthor Commented:
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
 
PatHartmanCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now