help with stop loop eof

[zionsaal]

I have a table "pt" with 2 fields 1 named "MasterID" and 1 named
"tickets"
each MasterID hes a number of tickets to print so I want to append to
a new table each masterid so many times the value of the ticket field
is
example if the masterid "15251" hes the ticket value 20 I want to
append to the new table the value "15251" 20 times
so I created a query "select tickets from pt group by tickets"
and the form runs the loop on each query record so many times the
value of me.tikets
then the first loop is to navigate to the next record
the docmd.gotorecord is default the next record if no one is
described


all works fine only I can't stop the loop when it reaches the end of
the query
thanks
here is the code:

Private Sub Command2_Click()
Dim i As Integer
i = 1
DoCmd.SetWarnings False
Do Until Me.RecordsetClone.EOF
i = 1
Do Until i > Me.tikets
DoCmd.RunSQL ("insert into 123 select masterid from pt where tikets =
'" & Me.tikets & "'")
i = i + 1
Loop
If Me.RecordsetClone.EOF Then
GoTo finish
Else
DoCmd.GoToRecord
End If
Loop
finish:
DoCmd.SetWarnings True
End Sub

the "Me.RecordsetClone.EOF" did not work
I got the massage "you can't go to specific record"

 

[mray29]

For starters, the GoToREcord command requires some parameters:
DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7. I don't believe you can
use GoToRecord by itself, because it doesn't know which record you want it to
go to.

Also, I don't think you need the code "If Me.RecordsetClone.EOF" because
that is handled by the Do Until Me.RecordsetClone.EOF statement at the top.
When EOF is reached, the code will exit the loop after the second Loop
statement. At that point you need to decide which record you want to go to
and use the parameters for GoToRecord accordingly.

 

[George Nicholson]

Answered in modulesdaovba i believe:

Do Until Me.RecordsetClone.EOF ..........
Loop

for the above to work, you need a Me.RecordsetClone.MoveNext (or similar)
within the loop. Otherwise you will never leave the first record and never
get to EOF.

If Me.RecordsetClone.EOF Then ...........
End If

The above test has no purpose. EOF will always be True at that point in
code. False statements will never execute. No need for a If..End If. This
should be reduced to just the statements you want executed when true:

DoCmd.SetWarnings True

HTH,

 

[zionsaal]

Answered in modulesdaovba i believe:




for the above to work, you need a Me.RecordsetClone.MoveNext (or similar)
within the loop. Otherwise you will never leave the first record and never
get to EOF.




The above test has no purpose. EOF will always be True at that point in
code. False statements will never execute. No need for a If..End If. This
should be reduced to just the statements you want executed when true:

DoCmd.SetWarnings True

HTH,









- Show quoted text -

in other words I'm looking a way to tell access:
"If you are in the last record then stop the loop and GoTo finish"
I didn't understand why I do not need to use the:
"if you are in the last record don't go to the next record because a
err will occur"

 

[pietlinden]

I agree with John (one of them...).

Don't use code at all. It's not necessary. (never mind slow).

If you use an query with a non-equijoin, then you can generate a
resultset of records. Check the answer under his post...

SELECT pt.MasterID, pt.Tickets, Counters.Counter
FROM Counters INNER JOIN pt ON Counters.Counter <= pt.Tickets;


Counters.Counter contains a series of values. (1 to Max(pt.Tickets))

Much easier than the code I wrote to figure this out.

DO NOT DO THIS....
Public Sub CreateRecords()
Dim rsRead As DAO.Recordset
Dim rsWrite As DAO.Recordset
Dim intCounter As Integer 'for the looping

Set rsRead = DBEngine(0)(0).OpenRecordset("pt", dbOpenTable,
dbForwardOnly, dbReadOnly)
Set rsWrite = DBEngine(0)(0).OpenRecordset("FinalTable",
dbOpenTable, dbAppendOnly)

Do Until rsRead.EOF

For intCounter = 1 To rsRead.Fields("Tickets")
rsWrite.AddNew
rsWrite.Fields("MasterID") = rsRead.Fields("MasterID")
rsWrite.Update
Next intCounter

rsRead.MoveNext

Loop

rsRead.Close
Set rsRead = Nothing
rsWrite.Close
Set rsWrite = Nothing
End Sub

 

[zionsaal]

I agree with John (one of them...).

Don't use code at all. It's not necessary. (never mind slow).

If you use an query with a non-equijoin, then you can generate a
resultset of records. Check the answer under his post...

SELECT pt.MasterID, pt.Tickets, Counters.Counter
FROM Counters INNER JOIN pt ON Counters.Counter <= pt.Tickets;

Counters.Counter contains a series of values. (1 to Max(pt.Tickets))

Much easier than the code I wrote to figure this out.

DO NOT DO THIS....
Public Sub CreateRecords()
Dim rsRead As DAO.Recordset
Dim rsWrite As DAO.Recordset
Dim intCounter As Integer 'for the looping

Set rsRead = DBEngine(0)(0).OpenRecordset("pt", dbOpenTable,
dbForwardOnly, dbReadOnly)
Set rsWrite = DBEngine(0)(0).OpenRecordset("FinalTable",
dbOpenTable, dbAppendOnly)

Do Until rsRead.EOF

For intCounter = 1 To rsRead.Fields("Tickets")
rsWrite.AddNew
rsWrite.Fields("MasterID") = rsRead.Fields("MasterID")
rsWrite.Update
Next intCounter

rsRead.MoveNext

Loop

rsRead.Close
Set rsRead = Nothing
rsWrite.Close
Set rsWrite = Nothing
End Sub

thanks (e-mail address removed)

both works fine!!

 

[George Nicholson]

As stated elsewhere:
AFAIK, there is no pure "am i on the last record?" test in existance.

Testing EOF tells you whether you have moved *past* the last record, not
whether you are on it.
If you are testing for EOF you *have* to go past the last record in order
for EOF to become True.