Bookmarking problem in Access 2002

[matteo.trombetti]

Following a problem that I strive to solve without success for another
project (Access 2002 crashes at that point every single time), I
developed a small form to track down where the problem was.

This is a very simple form, with a RecordSource set to a query that
looks up a table primary key, let's say [myID]. The query results in a
single column with value 1, 10, 12, 13 (4 records).

I have a TextBox myField bound to this field [myID] and another TextBox
myInput, which is the input from the user. The user should input a
number and the form should jump to the record with that [myID]. The
problem I have found is that whenever the input [myID] does not exist
or is the same as the current record, the form will step forward 1
record, no matter what I put in the code! Why is this happening and
what should I do to force the form not to move forward?

This is the code for the AfterUpdate procedure in myInput TextBox:

Private Sub myInput_AfterUpdate()
With Me.RecordsetClone
'comment 1
.FindFirst "[myID]=" & Me!myInput
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End Sub

I tried a lot of different things, from using
Dim value as Long
value = Me!myInput
and then setting .FindFirst "[myID]=" & value

I added the line
.Bookmark = Me.Bookmark
where I put 'comment 1'

I tried to convert myInput to a string. I tried to save the original
bookmark and reapply it when .NoMatch. It works just until the End Sub
statement. But I can't see it working.

Any suggestion will be highly appreciated (and I'm looking forward to
posting the main problem...)

Thanks,
Matteo

 

[Allen Browne]

Chances are there is some other code that is advancing the record.

Try explicitly saving the record before moving. That sidesteps several
strange problems, probably because it flushes the pending events.

So:
Private Sub myInput_AfterUpdate()
If Me.Dirty Then Me.Dirty = False
With Me.RecordsetClone ...

Regarding the crash, we probably should wait for you to post details, but
please indicate if the form has a subform. #2 under this link details with
that issue:
http://allenbrowne.com/ser-48.html#usability

 

[matteo.trombetti]

Thank you for your answer. I found out that it was not the code, but
the CycleRecords property set to AllRecords that was advancing it.
Setting it to CurrentRecord works just fine.
With regard to the other problem, I did post the main problem, and I
tried to disable Name AutoCorrect and to remove the linked subform as
well but it is still crashing

Thanks again,
Matteo