Test for EOF from a macro?

[rich575]

In Access 2002, I'm trying to use the RunMacro action within a macro. It is
going to the first record, performing some actions. Then, going to the Next
record and performing some actions. It should repeat (Repeat Expression)
until the table is at the End of File. Is there a way to enter the EOF
command into the Repeat Expression field, or is there another way?

Thanks,
Richard

 

[Ken Snell [MVP]]

How are you looping through a table via a macro? Tell us more details about
your macro's actions, etc.

 

[rich575]

This may be a convoluted way of doing it, but this is what I'm trying to do:

Background:
We're getting two CSV files from a shopping cart, order.csv and
orderitem.csv, which are imported into an Access db and processed.

Order.csv contains the orderID, payer info, number of items and total of
order. Orderitem.csv contains the orderID associated with that item, price
and item info. There may be one or more items associated with one orderID,
however there is no individual item# provided by the cart.

Goal:
-- To assign an item # to each item so that the orderID and the item#
together will create a unique ID and have meaning to a person looking at it.

Basic Logic:
Set ALL TempCartOrderID to 1 (initialize, Update Query for all records in
the table)
Set ALL TempItemNumber to 1 (initialize, Update Query for all records in
the table)
Loop – DO UNTIL EOF.
If TempCartOrderID <> CartOrderID,
Set TempItemNumber to 1 (update query)
CreateID# (update query)???
Set ALL TempCartOrderIDS to this CartOrderID (in this record)(Update
Query)
Go to Next Record

If TempCartOrderID = CartOrderID (item #2 or greater on a multiple
item order)
TempItemNumber = TempItemNumber + 1
CreateID# (update query)???
Set TempCartOrderID to CartOrderID
(Logic for resetting the TempItemNumber back to one or leaving it
incremented if there is another duplicate CartOrderID)??
Go to Next Record

I'm not real clear on parts of it. The way I'd set up the macro would be
similar to Article 90814:

Macro Name Action
-------------------------
Do_Loop1 RunMacro
Loop1 MsgBox

Do_Loop1 Actions
--------------------------------------
RunMacro
Macro Name: Do_Loops.Loop1
Repeat Expression: <your_condition>

Loop1 Actions
-------------------
MsgBox Message: ="Loop"


Not quite sure how to set up the Repeat Expression to go to the EOF, or if
there is a better, simpler way to do the whole thing. Any help you can give
would be appreciated. Thanks, Ken.
Richard

 

[Ken Snell [MVP]]

An ACCESS macro is not how I would do this. ACCESS macros cannot open
recordsets, which is what you will want to do here. (Mind you, given some
time, I probably could figure out a convoluted way to do what you seek via a
macro, but it's so much easier to do with VBA code.)

Are you familiar with VBA programming / code?

--

Ken Snell
<MS ACCESS MVP>

 

[rich575]

I did a little Visual Basic 6.0 development about 5 years ago. If you can
refresh my memory of how to create/close a recordset and manipulate it. Or,
I can do some research...

Thanks,
Richard

 

[Ken Snell [MVP]]

It's not complicated to do what you seek. The following is a code example
based on what you posted:
------

Dim dbs As DAO.Database
Dim lngNextNum As Long, lngOrderID As Long
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OrderID, OrderItemID FROM tblOrderItems "
strSQL = strSQL & "ORDER BY OrderID;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
lngOrderID = rst.Fields("OrderID").Value
lngNextNum = 0
Do While rst.EOF = False
If lngOrderID <> rst.Fields("OrderID").Value Then
lngNextNum = 0
lngOrderID = rst.Fields("OrderID").Value
End If
lngNextNum = lngNextNum + 1
rst.Edit
rst.Fields("OrderItemID").Value = lngNextNum
rst.Update
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing


--

Ken Snell
<MS ACCESS MVP>

 

[rich575]

That looks great, Ken. I'll try it out. Thank you so much for your help.
Richard

 

[rich575]

Ken,

I starting to test this now, and am having trouble creating the code to make
it run.

Steps Taken:
1. Created a module to hold the code.
2. Created a function called SetItemCodes()
(More on this later)
3.Created a macro with a RunCode action in it, and put SetItemCodes() in the
Function Name argument (used build expression to find it).
4. Put your code in SetItemCodes().
5. Ran the macro, got an "Compile error: User-defined type not defined."
The code "dbs As DAO.Database" was highlighted.

Questions:
A. Can you run the code from a function, or do you need run it from a sub?
If from a sub, what is example function code to get the sub to run?
B. What caused the compile error, and what's the fix?

Code:
Function SetItemCodes()
Dim dbs As DAO.Database
Dim lngNextNum As Long, lngOrderID As Long
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OrderID, ItemID FROM ORDERITEM"
strSQL = strSQL & "ORDER BY OrderID;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
lngOrderID = rst.Fields("OrderID").Value
lngNextNum = 0
Do While rst.EOF = False
If lngOrderID <> rst.Fields("OrderID").Value Then
lngNextNum = 0
lngOrderID = rst.Fields("OrderID").Value
End If
lngNextNum = lngNextNum + 1
rst.Edit
rst.Fields("ItemID").Value = lngNextNum
rst.Update
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function

Thanks, Ken.

 

[Ken Snell \(MVP\)]

You're using ACCESS 2002, which does not have a reference set to DAO library
by default. You will need to set the reference to it manually.

Open the VBE and click Tools | References. In the list, you'll find Data
Access Objects library v3.x. Select it. Then the code should compile without
problem.

And the macro should then run without problem.

--

Ken Snell
<MS ACCESS MVP>

Ken,

I starting to test this now, and am having trouble creating the code to
make
it run.

Steps Taken:
1. Created a module to hold the code.
2. Created a function called SetItemCodes()
(More on this later)
3.Created a macro with a RunCode action in it, and put SetItemCodes() in
the
Function Name argument (used build expression to find it).
4. Put your code in SetItemCodes().
5. Ran the macro, got an "Compile error: User-defined type not defined."
The code "dbs As DAO.Database" was highlighted.

Questions:
A. Can you run the code from a function, or do you need run it from a sub?
If from a sub, what is example function code to get the sub to run?
B. What caused the compile error, and what's the fix?

Code:
Function SetItemCodes()
Dim dbs As DAO.Database
Dim lngNextNum As Long, lngOrderID As Long
Dim rst As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT OrderID, ItemID FROM ORDERITEM"
strSQL = strSQL & "ORDER BY OrderID;"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = False And rst.BOF = False Then
rst.MoveFirst
lngOrderID = rst.Fields("OrderID").Value
lngNextNum = 0
Do While rst.EOF = False
If lngOrderID <> rst.Fields("OrderID").Value Then
lngNextNum = 0
lngOrderID = rst.Fields("OrderID").Value
End If
lngNextNum = lngNextNum + 1
rst.Edit
rst.Fields("ItemID").Value = lngNextNum
rst.Update
rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Function

Thanks, Ken.

< snipped >