Need For/Next Statment Assistance Please

  • Thread starter Thread starter mzaiderman
  • Start date Start date
M

mzaiderman

Trying to automate some code for excel and need to break every 1200
lines to END SUB and Create a new SUB. I can do this with an insert
statement, my issue is trying to cut every 1200 records for each user.

Each user will have about 3000 lines of code, and needs to be split
into 1200 for each. I have an ID field on an autonumber that
identifies the field. Have about 55 users, so about 140,000 records

user code ID
userA text 1
userA text 2

What I have so far is...

Dim Emp As variant, ID as variant
Emp = Me.Employee#
ID = Me.ID

For Emp = 1 to 50
For ID = 1 to 1200
docmd.runsql("insert into tbl_test employeename,id,code")
gotorecord, acnext
next ID
docmd.runsql("insert into tbl_test employeename,id,'END SUB'")
Next Emp

Doesn't seem to be working, can anyone please lend a hand?
Thank you!
 
Loop Through Recordset
---

you need to loop through your recordset -- here is some code you can modify:

'~~~~~~~
Sub LoopThroughRecordset()

'Crystal
'strive4peace

'NEED reference to
'Microsoft DAO Library

'dimension variables

Dim r As DAO.Recordset

Dim strSQL as string


'Set up error handler
On Error GoTo Err_proc

'define the recordset to open
strSQL = "SELECT ..."

'comment or take this line out after procedure is tested
debug.print strSQL

'open the Recordset
Set r = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

'move to the first record
r.MoveFirst

'loop through the Recordset until the end
Do While Not r.EOF

'do something

r.MoveNext
Loop

Exit_proc:
on error resume next

'close the recordset
r.close

'release object variables
Set r = Nothing


exit sub

Err_proc:
msgbox err.description _
,,"ERROR " & err.number _
& " LoopThroughRecordset"

'press F8 to step through code and see where problem is
'comment next line after routine is debugged
stop : resume
goto Exit_proc

End Sub

'~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Back
Top