Solved

Access 2003 form, automatically input query parameter from list

Posted on 2016-11-10
5
52 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 36

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 36

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views 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 Access…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

830 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