Form entering data in wrong record

[Toffee]

I have a form that includes an unbound control used for
searching records. When the form loads, this control
receives the focus first. If I type in a search number
for a record before all the records are loaded, the form
will display the data from that record but any data I
enter will appear to not be accepted. The problem is that
it will actually be entered in the table under the
previous record in the form which causes huge data error
problems. I discovered this by looking at the form record
by the navigation buttons and realizing it was one number
off. I also found that if I index one record ahead using
the forward navigation button and then use the unbound
control to search for a record before all records are
loaded, it is off by two records.

If I wait until the number of records is displayed
(appears as "of xxxx" to the right of the navigation
buttons on the bottom of the form), this problem never
happens. If I enter a search number prior to that, it
always does. Is there a way I can prevent a user from
entering data into a control or from changing records
until all records are loaded for a form? By placing beeps
in the code, it appears as if the form and control events
are already completed and all items are loaded and then
you still have a couple seconds where this error can take
place prior to all records being loaded. I don't know if
this has to do with the recordset cloning or what. If so,
maybe there is a better option to prevent this problem.
The code for the unbound control is as follows...

Private Sub Combo_FindCustomer_AfterUpdate()
'Find the record that matches the customer number
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerNumber] = '" & Me!
[Combo_FindCustomer] & "'"
Me.Bookmark = rs.Bookmark
End Sub

 

[Dirk Goldgar]

I have a form that includes an unbound control used for
searching records. When the form loads, this control
receives the focus first. If I type in a search number
for a record before all the records are loaded, the form
will display the data from that record but any data I
enter will appear to not be accepted. The problem is that
it will actually be entered in the table under the
previous record in the form which causes huge data error
problems. I discovered this by looking at the form record
by the navigation buttons and realizing it was one number
off. I also found that if I index one record ahead using
the forward navigation button and then use the unbound
control to search for a record before all records are
loaded, it is off by two records.

If I wait until the number of records is displayed
(appears as "of xxxx" to the right of the navigation
buttons on the bottom of the form), this problem never
happens. If I enter a search number prior to that, it
always does. Is there a way I can prevent a user from
entering data into a control or from changing records
until all records are loaded for a form? By placing beeps
in the code, it appears as if the form and control events
are already completed and all items are loaded and then
you still have a couple seconds where this error can take
place prior to all records being loaded. I don't know if
this has to do with the recordset cloning or what. If so,
maybe there is a better option to prevent this problem.
The code for the unbound control is as follows...

Private Sub Combo_FindCustomer_AfterUpdate()
'Find the record that matches the customer number
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerNumber] = '" & Me!
[Combo_FindCustomer] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Hmm, this sounds similar to an old bug in Access 97 that was fixed years
ago, but your code looks like the wizard-generated code of a more recent
version of Access. What version and SP-level of Access are you using?
Is this in an .mdb file or and .adp? If the former, is the table local
or linked, and if linked, to what type of back-end database?

Here are a couple of things to try while we try to figure out the cause
of the problem. First, if this is not an .adp, try revising your code
as follows:

Private Sub Combo_FindCustomer_AfterUpdate()
'Find the record that matches the customer number
With Me.RecordsetClone
.FindFirst "[CustomerNumber] = '" & _
Me![Combo_FindCustomer] & "'"
Me.Bookmark = .Bookmark
End With
End Sub

Second, if that doesn't work, try adding this code in the form's Load
event:

Private Sub Form_Load()

With Me.Recordset
If .RecordCount <> 0 Then
.MoveLast
.MoveFirst
End If
End With

End Sub

Please let me know if either of these changes makes any difference to
the behavior.

 

[Toffee]

I am using Access 2000 (9.0.6926 SP-3). The file is
an .mdb file and it is a local table.

The first change did nothing to affect this problem.

The second one appears to have cured my ills. The search
combo box no longer gets the focus as soon as the form
loads but if I hit the tab key or do a mouse highlight and
enter a number and then hit the enter key before the total
number of records appears next to the navigation buttons,
it still goes to the correct numbered record. A test of
the data entry shows it works as well.

Thank you very much. Now I am curious as to what moving
to the last record and then back to the first record
during the form load is doing to correct this problem. Do
tell.

-----Original Message-----

I have a form that includes an unbound control used for
searching records. When the form loads, this control
receives the focus first. If I type in a search number
for a record before all the records are loaded, the form
will display the data from that record but any data I
enter will appear to not be accepted. The problem is that
it will actually be entered in the table under the
previous record in the form which causes huge data error
problems. I discovered this by looking at the form record
by the navigation buttons and realizing it was one number
off. I also found that if I index one record ahead using
the forward navigation button and then use the unbound
control to search for a record before all records are
loaded, it is off by two records.

If I wait until the number of records is displayed
(appears as "of xxxx" to the right of the navigation
buttons on the bottom of the form), this problem never
happens. If I enter a search number prior to that, it
always does. Is there a way I can prevent a user from
entering data into a control or from changing records
until all records are loaded for a form? By placing beeps
in the code, it appears as if the form and control events
are already completed and all items are loaded and then
you still have a couple seconds where this error can take
place prior to all records being loaded. I don't know if
this has to do with the recordset cloning or what. If so,
maybe there is a better option to prevent this problem.
The code for the unbound control is as follows...

Private Sub Combo_FindCustomer_AfterUpdate()
'Find the record that matches the customer number
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerNumber] = '" & Me!
[Combo_FindCustomer] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Hmm, this sounds similar to an old bug in Access 97 that was fixed years
ago, but your code looks like the wizard-generated code of a more recent
version of Access. What version and SP-level of Access are you using?
Is this in an .mdb file or and .adp? If the former, is the table local
or linked, and if linked, to what type of back-end database?

Here are a couple of things to try while we try to figure out the cause
of the problem. First, if this is not an .adp, try revising your code
as follows:

Private Sub Combo_FindCustomer_AfterUpdate()
'Find the record that matches the customer number
With Me.RecordsetClone
.FindFirst "[CustomerNumber] = '" & _
Me![Combo_FindCustomer] & "'"
Me.Bookmark = .Bookmark
End With
End Sub

Second, if that doesn't work, try adding this code in the form's Load
event:

Private Sub Form_Load()

With Me.Recordset
If .RecordCount <> 0 Then
.MoveLast
.MoveFirst
End If
End With

End Sub

Please let me know if either of these changes makes any difference to
the behavior.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.