Nested Do Until Loop

[Jeff G]

Hello all-

I have set up a function listed below. What I'm trying to do is:
1. Select the expenseGLVoucherNumber in the tblExpenseIntegrationGLAccount
table.
2. Assign the expenseGLDistributionSequenctNumber via VBA.

Example:

Voucher expenseGLDistribtuionSequence
expenseCreditAmount expenseDebitAmount
300000001 16384
50 0
300000001
0 25
300000001
0 25

Here's the code:

Function UpdateDistributionSequenceNumber()

Dim RecordCount As Integer
Dim RecordCount2 As Integer
Dim db As Database
Dim rec As DAO.Recordset
Dim rec2 As DAO.Recordset

Set db = CurrentDb
Set rec = db.OpenRecordset("Select expenseGLVoucherNumber from
tblExpenseIntegrationGLAccount Where expenseGLDistributionSequence is Null")

rec.MoveFirst
RecordCount = 0
Do Until rec.EOF
Set rec2 = db.OpenRecordset("SELECT * from
tblExpenseIntegrationGLAccount WHERE expenseGLVoucher=" & rec)

rec2.MoveFirst
RecordCount2 = 0
Do Until rec2.EOF
rec2.Edit
rec2!expenseGLDistributionSequence =
DMax("expenseGLDistributionSequence", "tbltblExpenseIntegrationGLAccount") +
16384
rec2.Update
RecordCount2 = RecordCount2 + 1
rec2.MoveNext
Loop
rec.MoveNext
Loop

End Function

When I run the code, I receive an error:

Run-time error '2766': The object doesn't cont the Automation object '|.'

I'm pretty sure that the error is somewhere in the nested loop.

Any help would be much appreciated.

Thanks in advance.

Jeff

 

[Jeff Boyce]

Have you tried setting a breakpoint at the top of your code and using that
to step through, line-by-line, to discover exactly where it's failing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Dale Fye]

My guess is that it is in this line:

DMax("expenseGLDistributionSequence", "tbltblExpenseIntegrationGLAccount") +
16384

I seriously doubt that you have a table named:

"tbltblExpenseIntegrationGLAccount"

This looks like it has too many "tbl". It should probably be:

"tblExpenseIntegrationGLAccount"

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.