help with loop end of file

[zionsaal]

I have a form attached to a query that runs a sql on each record

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"
How can I do that?
thanks

 

[pietlinden]

I have a form attached to a query that runs a sql on each record

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"
How can I do that?
thanks

Back up a few steps. What is this code intended to do? If you're
trying to update a set of records that has children, use a join in the
where clause of your update statement. Only use a recordset to update
records as a LAST resort. It's the slowest, least efficient way to do
it. Sometimes it is necessary, but I'm not convinced this is one of
them.

 

[Guest]

That is not the problem. The problem is this line:
DoCmd.GoToRecord
You have not specified a record to go to.
There are other issues with the code as well. First, what is the i doing?
It is not
referenced anywhere that has any effect on anything.

You should not have to check for RecorsetClone.EOF, that is what the Do Loop
is for.

Don't know what this is doing:
DoCmd.RunSQL ("insert into 123 select masterid from pt where
tikets =
'" & Me.tikets & "'")

It should not need a loop. It looks like it should update everything that
matches Me.tikets. However, since you are not advancing the recordset, it
will sit and spin forevever.

Also, you are using the GoTo statment - just a thought on that:
!!!! YOU NEVER, EVER USE THE GOTO STATMENT !!!!!

And last, note how I indented the code. I had to to be able to read it.

Don't really mean to beat you up, just trying to help.
If you will be kind enough to describe what you are doing, I think I can
help you clean this up.

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

 

[zionsaal]

klaatu

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

 

[zionsaal]

klaatu

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

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"

 

[George Nicholson]

the "Me.RecordsetClone.EOF" did not work
......

then the first loop is to navigate to the next record

where are you navigating anything?? Looks like you are in an endless loop.
As written, rst.EOF will never be true because you never move off the first
record.


Dim rst as Dao.Recordset

Set rst = Me.RecordsetClone

DoCmd.SetWarnings False
Do Until rst.EOF
.....other code
rst.MoveNext '*This is a crucial piece missing from your code
Loop
DoCmd.SetWarnings True

'The following test is unnecessary: EOF has to True or we wouldn't be here.
False will never execute, so no reason for test.
'If Me.RecordsetClone.EOF Then

HTH,

 

[Guest]

Note some changes. I use the Execute method because it is faster and you
don't need to set warnings on and off.
I use a long type for counting rather than an integer. Also faster.

Notice the indentation and structure of the code. Much easier to read.

Private Sub Command2_Click()
Dim rst As Recordset
Dim lngTikets As Long

Set rst = Me.RecordsetClone
With rst
If .Recordcount = 0 Then
MgBox "No Records to Process"
Exit Sub
End If
.MoveLast
.MoveFirst
Do While Not .EOF
Currentdb.Execute ("insert into 123 select masterid from pt
where tikets =
'" & Me.tikets & "'")
lngTikets = lngTikets + 1
If lngTikets > Me.tikets Then
Exit Do
End If
.MoveNext
Loop
End With
Set rst = Nothing
End Sub

Hope this works okay for you.

 

[John W. Vinson]

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

I'd suggest a different approach altogether.

Create a table named NUM with one integer field N, with values from 0 through
some large number (10000, 65535 even).

Create a Query by adding [pt] and [Num] to the query grid, with NO join line.

Put a criterion on N of

< [pt].[tickets]

Select MasterID and N as fields in the query.

This query will give you [tickets] copies of each record in [pt]. You can use
the query for an export, as the recordsource for a report, as the rowsource
for a combo, etc. - there's probably NO good reason to create a new table at
all, much less store 20 redundant records in it.

John W. Vinson [MVP]

 

[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

I'd suggest a different approach altogether.

Create a table named NUM with one integer field N, with values from 0 through
some large number (10000, 65535 even).

Create a Query by adding [pt] and [Num] to the query grid, with NO join line.

Put a criterion on N of

< [pt].[tickets]

Select MasterID and N as fields in the query.

This query will give you [tickets] copies of each record in [pt]. You can use
the query for an export, as the recordsource for a report, as the rowsource
for a combo, etc. - there's probably NO good reason to create a new table at
all, much less store 20 redundant records in it.

John W. Vinson [MVP]

thanks John W. Vinson
it works

and thanks all

George Nicholson
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"

 

[George Nicholson]

On one hand you could pretty much ignore my posts. I misread the nested
loops and in doing so I completely discounted the possibility that
DoCmd.GoToRecord was actually doing anything because of 1) where it was
placed and 2) its lack of arguments (I generally avoid relying on default
arguments. No guarantees that they will be the defaults in the next version
and I find code easier to read/maintain when its specific.).

George Nicholson
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"

Well, your code never tests to see whether you are in the last record. There
really is no such test. I think you misunderstand what EOF is...

If you want to use the status of EOF for your loop, you can't use
GoToRecord. Here's why: EOF will *never* be true while you are on a valid
record and GoToRecord won't allow you to move anywhere except valid records.
You discovered that when you issued GoToRecord while on the last record:
"Can't go there" error.

For EOF to be True, you have to move *past* the last record. That's what EOF
is: a "beyond last record" marker, not a "last record" marker.

Do Until Me.RecordsetClone.EOF
'..........lotsa code...........
If Me.RecordsetClone.EOF Then
' This condition will never happen. If EOF were true, the loop
wouldn't have been entered
' and you haven't done anythingduring this iteration to change the
EOF status
GoTo finish
Else
' If we are on the last record, this will try to execute but will
raise a "can't go to specified record" error
DoCmd.GoToRecord
End If
Loop

Using MoveNext while on the last record would *not* raise the same error. It
would actually move you past the last record, thereby allowing EOF to become
True. This allows your loop to end as you expect. This is how EOF tests are
meant to be used.

GoToRecord and MoveNext might appear to do the same thing, but they are in
different object models and have different behaviour. EOF and MoveNext work
together (and were designed to). EOF and GoToControl do not.

HTH,

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

I'd suggest a different approach altogether.

Create a table named NUM with one integer field N, with values from 0
through
some large number (10000, 65535 even).

Create a Query by adding [pt] and [Num] to the query grid, with NO join
line.

Put a criterion on N of

< [pt].[tickets]

Select MasterID and N as fields in the query.

This query will give you [tickets] copies of each record in [pt]. You can
use
the query for an export, as the recordsource for a report, as the
rowsource
for a combo, etc. - there's probably NO good reason to create a new table
at
all, much less store 20 redundant records in it.

John W. Vinson [MVP]

thanks John W. Vinson
it works

and thanks all

George Nicholson
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"

 

[zionsaal]

On one hand you could pretty much ignore my posts. I misread the nested
loops and in doing so I completely discounted the possibility that
DoCmd.GoToRecord was actually doing anything because of 1) where it was
placed and 2) its lack of arguments (I generally avoid relying on default
arguments. No guarantees that they will be the defaults in the next version
and I find code easier to read/maintain when its specific.).

George Nicholson
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"

Well, your code never tests to see whether you are in the last record. There
really is no such test. I think you misunderstand what EOF is...

If you want to use the status of EOF for your loop, you can't use
GoToRecord. Here's why: EOF will *never* be true while you are on a valid
record and GoToRecord won't allow you to move anywhere except valid records.
You discovered that when you issued GoToRecord while on the last record:
"Can't go there" error.

For EOF to be True, you have to move *past* the last record. That's what EOF
is: a "beyond last record" marker, not a "last record" marker.

Do Until Me.RecordsetClone.EOF
'..........lotsa code...........
If Me.RecordsetClone.EOF Then
' This condition will never happen. If EOF were true, the loop
wouldn't have been entered
' and you haven't done anythingduring this iteration to change the
EOF status
GoTo finish
Else
' If we are on the last record, this will try to execute but will
raise a "can't go to specified record" error
DoCmd.GoToRecord
End If
Loop

Using MoveNext while on the last record would *not* raise the same error. It
would actually move you past the last record, thereby allowing EOF to become
True. This allows your loop to end as you expect. This is how EOF tests are
meant to be used.

GoToRecord and MoveNext might appear to do the same thing, but they are in
different object models and have different behaviour. EOF and MoveNext work
together (and were designed to). EOF and GoToControl do not.

HTH,

On Tue, 07 Aug 2007 09:50:19 -0700, (e-mail address removed) wrote:
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
I'd suggest a different approach altogether.
Create a table named NUM with one integer field N, with values from 0
through
some large number (10000, 65535 even).
Create a Query by adding [pt] and [Num] to the query grid, with NO join
line.
Put a criterion on N of
< [pt].[tickets]
Select MasterID and N as fields in the query.
This query will give you [tickets] copies of each record in [pt]. You can
use
the query for an export, as the recordsource for a report, as the
rowsource
for a combo, etc. - there's probably NO good reason to create a new table
at
all, much less store 20 redundant records in it.
John W. Vinson [MVP]

thanks John W. Vinson
it works

and thanks all

George Nicholson
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"- Hide quoted text -

- Show quoted text -

so you mean that I should use MoveNext instant of Go To Record?

 

[zionsaal]

On one hand you could pretty much ignore my posts. I misread the nested
loops and in doing so I completely discounted the possibility that
DoCmd.GoToRecord was actually doing anything because of 1) where it was
placed and 2) its lack of arguments (I generally avoid relying on default
arguments. No guarantees that they will be the defaults in the next version
and I find code easier to read/maintain when its specific.).

Well, your code never tests to see whether you are in the last record. There
really is no such test. I think you misunderstand what EOF is...

If you want to use the status of EOF for your loop, you can't use
GoToRecord. Here's why: EOF will *never* be true while you are on a valid
record and GoToRecord won't allow you to move anywhere except valid records.
You discovered that when you issued GoToRecord while on the last record:
"Can't go there" error.

For EOF to be True, you have to move *past* the last record. That's what EOF
is: a "beyond last record" marker, not a "last record" marker.

Do Until Me.RecordsetClone.EOF
'..........lotsa code...........
If Me.RecordsetClone.EOF Then
' This condition will never happen. If EOF were true, the loop
wouldn't have been entered
' and you haven't done anythingduring this iteration to change the
EOF status
GoTo finish
Else
' If we are on the last record, this will try to execute but will
raise a "can't go to specified record" error
DoCmd.GoToRecord
End If
Loop

Using MoveNext while on the last record would *not* raise the same error. It
would actually move you past the last record, thereby allowing EOF to become
True. This allows your loop to end as you expect. This is how EOF tests are
meant to be used.

GoToRecord and MoveNext might appear to do the same thing, but they are in
different object models and have different behaviour. EOF and MoveNext work
together (and were designed to). EOF and GoToControl do not.

HTH,

news:[email protected]...

On Aug 7, 3:35 pm, John W. Vinson <jvinson@STOP_SPAM.WysardOfInfo.com>
wrote:
On Tue, 07 Aug 2007 09:50:19 -0700, (e-mail address removed) wrote:
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
I'd suggest a different approach altogether.
Create a table named NUM with one integer field N, with values from 0
through
some large number (10000, 65535 even).
Create a Query by adding [pt] and [Num] to the query grid, with NO join
line.
Put a criterion on N of
< [pt].[tickets]
Select MasterID and N as fields in the query.
This query will give you [tickets] copies of each record in [pt]. You can
use
the query for an export, as the recordsource for a report, as the
rowsource
for a combo, etc. - there's probably NO good reason to create a new table
at
all, much less store 20 redundant records in it.
John W. Vinson [MVP]
thanks John W. Vinson
it works
and thanks all
George Nicholson
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"- Hide quoted text -

- Show quoted text -

so you mean that I should use MoveNext instant of Go To Record?- Hide quoted text -

- Show quoted text -

this code works very good
but the query from john is the best

Private Sub Command2_Click()
Dim i As Integer
DoCmd.SetWarnings False
Do
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.tikets = "73" Then ' 73 is the last record
GoTo finish
Else
DoCmd.GoToRecord
End If
Loop
finish:
MsgBox "finish"
DoCmd.SetWarnings True
End Sub

It works fine
I'm using access 2003