While/Wend statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following code - written in a Form - and activated by double clicking a
control-button, is used to move quickly through the records in the underlying
query; which in a simple explanation, contains names e.g. Joe, Fred , Sid
etc. Each name belongs to one of three groups (group-name = "Set") i.e.
Set1,2 & 3 the purpose of having this code, is to quickly get to the
beginning of Set 2 without wading through all of Set 1

In all the Access versions prior to Office 2007 this code has worked. Now
it seems unable to recognize that the records it is passing through have
changed and careers blithely on, until an end-of- records fault is flagged up.

Anyone any ideas on whats wrong, or a workaround?

ColinG


If IsNull([Set]) = False Then
Chg& = [Set]
While [Set] = Chg&
DoCmd.GoToRecord , , acNext
Debug.Print [Set]
Wend
Forms![QueryEdit PLAY LIST]!No_within_set.SetFocus
End If
 
Hi Colin

I can't answer your question specifically. I can only assume that some sort
of "optimisation" is going on such that the DoCmd.GoToRecord is not fully
refreshing the controls until the code has finished running.

Here are two things you could try:

1. Insert a DoEvents after the DoCmd.GoToRecord

2. Replace the DoCmd.GoToRecord with:
Me.Recordset.MoveNext

Another way to achieve what you want is to search the form's Recordset for
the next record that does not match and jump directly to it:

With Me.RecordsetClone
.FindNext "[Set]<>" & Nz(Me![Set], 0)
If .NoMatch Then
MsgBox "some message"
Else
Me.Bookmark = .Bookmark
End If
End With
 
Graham Mandeno said:
Hi Colin

I can't answer your question specifically. I can only assume that some sort
of "optimisation" is going on such that the DoCmd.GoToRecord is not fully
refreshing the controls until the code has finished running.

Here are two things you could try:

1. Insert a DoEvents after the DoCmd.GoToRecord

2. Replace the DoCmd.GoToRecord with:
Me.Recordset.MoveNext

Another way to achieve what you want is to search the form's Recordset for
the next record that does not match and jump directly to it:

With Me.RecordsetClone
.FindNext "[Set]<>" & Nz(Me![Set], 0)
If .NoMatch Then
MsgBox "some message"
Else
Me.Bookmark = .Bookmark
End If
End With

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

ColinG said:
The following code - written in a Form - and activated by double clicking
a
control-button, is used to move quickly through the records in the
underlying
query; which in a simple explanation, contains names e.g. Joe, Fred , Sid
etc. Each name belongs to one of three groups (group-name = "Set") i.e.
Set1,2 & 3 the purpose of having this code, is to quickly get to the
beginning of Set 2 without wading through all of Set 1

In all the Access versions prior to Office 2007 this code has worked. Now
it seems unable to recognize that the records it is passing through have
changed and careers blithely on, until an end-of- records fault is flagged
up.

Anyone any ideas on whats wrong, or a workaround?

ColinG


If IsNull([Set]) = False Then
Chg& = [Set]
While [Set] = Chg&
DoCmd.GoToRecord , , acNext
Debug.Print [Set]
Wend
Forms![QueryEdit PLAY LIST]!No_within_set.SetFocus
End If
Click to expand...

Hi Graham
Click to expand...
thanks for your answer, I will try your suggestions and report the success -
or lack of it - to you.
Obliged Colin
 
Hi (again) Graham
Whilst I AM grateful for the effort you spent trying to resolve this issue,
I'm afraid I have to report that all the methods you detailed were
unsuccessful. That said, I must thank you for pointing out these additional
ways to skin this particular cat... I am sure they WILL come in useful in my
other work.
The "With Me.RecordsetClone" method worked once and then mysteriously
decided to act on the 'Else' part of your code, which produces the message
"some code".
As I initially indicated, it is strange - and not a little frustrating - to
have programs that have run for years, suddenly exhibit problems after buying
the new upgrades, which whilst (presumably) containing many new tricks,
appear to have forgotten some of their basic operations

:
 
Hi Colin

I'm sorry - I missed out one important line from the code I gave you.

Before executing the .FindNext you must synchronise the RecordsetClone with
the current record on the form:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindNext "[Set]<>" & Nz(Me![Set], 0)
.... and then the rest as I gave you
 
Hi Graham
That did it!

THANK YOU!

At the risk of being a pain, there is a reverse procedure in operation which
needs the same treatment; I have amended your code to read:
"FindPrevious "[Set]<>" & Nz(Me![Set], 0)"

this works, but needs refinement as it produces this result:

Name No Set
Jim 109 2
Fred 110 2 (location found by Graham's code)
Sid 1 3
Joe 2 3
Bill 3 3
etc. etc

My need is to have the location-found (when going backwards), be the first
number in the previous set ie 1

Is there some additional code (working along the lines of your original
success) which can, having found the last [Set], then search the [No] field
for the number 1
PS
It may be helpful for me to confirm that these records we are searching ARE
sorted by: [Set] and then by [No]
Regards Colin
 
Hi Colin

If the first record in each set can be guaranteed to have [No]=1 then you
can modify it to find the previous record with a different set no *and*
[No]=1.
Also, add a .MoveFirst to the If .NoMatch conditional in case you are
already in the first set:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindPrevious "[No]=1 and [Set]<>" & Nz(Me![Set], 0)
If .NoMatch Then
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With


If you can't guarantee you have a [No]=1 then you must move back *two* sets,
then forward one record. This should do it:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindPrevious "[Set]<>" & Nz(![Set], 0)
If .NoMatch Then
.MoveFirst
Else
.FindPrevious "[Set]<>" & Nz(![Set], 0)
If .NoMatch Then
.MoveFirst
Else
.MoveNext
End If
End If
Me.Bookmark = .Bookmark
End With

Note here that I am using the set number in the current record of the
RecordsetClone (![Set]) not the current record on the form (Me![Set]).

[Once again, untested, so I hope I haven't omitted anything important this
time :-)]
 
Hi Graham
Thats me sorted!
I am most grateful for your help and prompt replies.
Many thanks indeed Colin
 
Graham - Hi.
I noticed your assistance to Coling and wondered if you could help me with
an issue to do with 'recordsetclone'.
I have been using a database I compiled in MS Access 2000. I have now
started to use Access 2007 but when I try to use the database (even if I save
it as 2007 format) I get the follwing message and the 'buttons' don't work.
"The Object doesn't contain the Automation object 'RecordSetClone'".
Help - if possible.
NoelB

Graham Mandeno said:
Hi Colin

If the first record in each set can be guaranteed to have [No]=1 then you
can modify it to find the previous record with a different set no *and*
[No]=1.
Also, add a .MoveFirst to the If .NoMatch conditional in case you are
already in the first set:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindPrevious "[No]=1 and [Set]<>" & Nz(Me![Set], 0)
If .NoMatch Then
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With


If you can't guarantee you have a [No]=1 then you must move back *two* sets,
then forward one record. This should do it:

With Me.RecordsetClone
.Bookmark = Me.Bookmark
.FindPrevious "[Set]<>" & Nz(![Set], 0)
If .NoMatch Then
.MoveFirst
Else
.FindPrevious "[Set]<>" & Nz(![Set], 0)
If .NoMatch Then
.MoveFirst
Else
.MoveNext
End If
End If
Me.Bookmark = .Bookmark
End With

Note here that I am using the set number in the current record of the
RecordsetClone (![Set]) not the current record on the form (Me![Set]).

[Once again, untested, so I hope I haven't omitted anything important this
time :-)]
--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

ColinG said:
Hi Graham
That did it!

THANK YOU!

At the risk of being a pain, there is a reverse procedure in operation
which
needs the same treatment; I have amended your code to read:
"FindPrevious "[Set]<>" & Nz(Me![Set], 0)"

this works, but needs refinement as it produces this result:

Name No Set
Jim 109 2
Fred 110 2 (location found by Graham's code)
Sid 1 3
Joe 2 3
Bill 3 3
etc. etc

My need is to have the location-found (when going backwards), be the first
number in the previous set ie 1

Is there some additional code (working along the lines of your original
success) which can, having found the last [Set], then search the [No]
field
for the number 1
PS
It may be helpful for me to confirm that these records we are searching
ARE
sorted by: [Set] and then by [No]
Regards Colin
Click to expand...
Click to expand...
 
Back
Top