R
Rocky
I am currently using Access 2002 to control the availability of stock
at any given point in time in the future. (stock is used for a job,
then returned, so that it can be used again.)
I run a Macro that looks at the value of a specific field
("stockItem"), then opens a number of queries based on the "stockItem"
value and date parameters and then appends the maximum used for the
"StockItem" for the date period specified.
I have written the code below to run through all records, fetch the
"StockItem" for record1 and run the macro code, 2 and run the macro
code, 3 and run the macro code ...,etc. .
The problem is that "StockItem" needs to be copied to a form which the
queries use to base the query on. I don't know how to do this. I am
not sure if I should rather use a form as a clone.
CODE:
Private Sub Command104_Click()
On Error GoTo Stock_Totals_Err
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("PartSize")
rs.MoveFirst
Do Until rs.EOF
'<I now run the macro code>
DoCmd.SetWarnings False
DoCmd.OpenForm "Stock Total", acNormal, "", "", , acHidden
Forms!Today!Total = Forms![Stock Total]!StockTotal
DoCmd.Close acForm, "Stock Total"
DoCmd.OpenQuery "QT1", acViewNormal, acEdit
DoCmd.Close acQuery, "QT1"
DoCmd.OpenQuery "QT2", acViewNormal, acEdit
DoCmd.Close acQuery, "QT2"
DoCmd.OpenForm "Max Used", acNormal, "", "", , acHidden
Forms!Today!MaxUsed = Forms![Max Used]!MinOfTotal
DoCmd.Close acForm, "Max Used"
rs.MoveNext
Loop
rs.Close
Stock_Totals_Exit:
Exit Sub
Stock_Totals_Err:
MsgBox Error$
Resume Stock_Totals_Exit
at any given point in time in the future. (stock is used for a job,
then returned, so that it can be used again.)
I run a Macro that looks at the value of a specific field
("stockItem"), then opens a number of queries based on the "stockItem"
value and date parameters and then appends the maximum used for the
"StockItem" for the date period specified.
I have written the code below to run through all records, fetch the
"StockItem" for record1 and run the macro code, 2 and run the macro
code, 3 and run the macro code ...,etc. .
The problem is that "StockItem" needs to be copied to a form which the
queries use to base the query on. I don't know how to do this. I am
not sure if I should rather use a form as a clone.
CODE:
Private Sub Command104_Click()
On Error GoTo Stock_Totals_Err
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("PartSize")
rs.MoveFirst
Do Until rs.EOF
'<I now run the macro code>
DoCmd.SetWarnings False
DoCmd.OpenForm "Stock Total", acNormal, "", "", , acHidden
Forms!Today!Total = Forms![Stock Total]!StockTotal
DoCmd.Close acForm, "Stock Total"
DoCmd.OpenQuery "QT1", acViewNormal, acEdit
DoCmd.Close acQuery, "QT1"
DoCmd.OpenQuery "QT2", acViewNormal, acEdit
DoCmd.Close acQuery, "QT2"
DoCmd.OpenForm "Max Used", acNormal, "", "", , acHidden
Forms!Today!MaxUsed = Forms![Max Used]!MinOfTotal
DoCmd.Close acForm, "Max Used"
rs.MoveNext
Loop
rs.Close
Stock_Totals_Exit:
Exit Sub
Stock_Totals_Err:
MsgBox Error$
Resume Stock_Totals_Exit