[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Access 2003 form, automatically input query parameter from list

Posted on 2016-11-10
5
Medium Priority
?
71 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 41

Accepted Solution

by:
PatHartman earned 2000 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 41

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Hi, this video explains a free download that you can incorporate into your Access databases, or use stand-alone for contact management. Contacts -- Names, Addresses, Phone Numbers, eMail Addresses, Websites, Lists, Projects, Notes, Attachments…

612 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