Looping through records

[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

 

[Stefan Hoffmann]

hi Rocky,

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.

Use a proxy function instead of a form reference in your query:

Private m_ProxyVar As Long

Public Sub SetProxyVar(AValue As Long)

m_ProxyVar = AValue

End Sub

Public Function GetProxyVar() As Long

GetProxyVar= m_ProxyVar

End Function


In your queries use: WHERE [Field] = GetProxyVar()

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

This works? For action queries use DoCmd.RunQuery:

SetProxyVar rs![StockItem]
DoCmd.RunQuery "QT1"


mfG
--> stefan <--