Access 2007 - Find Record Occasionally Returns Incorrect Rec

[PaulW]

I have the following code in the After Update event of a combo box:

Code: Select all

Private Sub Combo69_AfterUpdate()
Dim strwhere As String
Dim qualrec As Variant
Dim frmcomvolrepresent As String
On Error GoTo err1

QualRecExists = "f"
Text64.Visible = True
   Me.Requery
   Me.Repaint
DoCmd.GoToControl "Text64"

DoCmd.FindRecord CLng(Combo69.Column(4)), acEntire, , acSearchAll, True, acCurrent

[badge Number] = Combo69.Column(4)
'Text64 = Combo69.Column(3)
Text67 = Combo69.Column(1)
Text66 = Combo69.Column(2)

frm256ErrorMsg = ""
frm256ErrorMsg = ""
strwhere = "[tbl330badgenbr] = " & Combo69.Column(4)
qualrec = DLookup("[tbl330badgeNbr]", "ShopQualification", strwhere)
If IsNull(qualrec) Or IsEmpty(qualrec) Then
' create a ShopQualification record for this badge nbr
   Dim dbsHappyFactory As Database
   Dim rstShopQualification As Recordset
   Set dbsHappyFactory = CurrentDb()
   Set rstShopQualification = dbsHappyFactory.OpenRecordset("ShopQualification", dbOpenDynaset)

   With rstShopQualification
       .AddNew
       ![tbl330BadgeNbr] = Combo69.Column(4)
       .Update
       .Bookmark = .LastModified
   End With
   Me.Requery
   Me.Repaint
End If

If Check86 = True Then
   If IsNull(frmcomvolrepresent) Or IsEmpty(frmcomvolrepresent) Then
      Check72 = True
   Else
      Check87 = True
   End If
   Check86 = False
End If
err1:
Debug.Print Err.Number; Err.Description


End Sub

For the most part, maybe 98% of the time, the correct record is found and displayed on the Form. Every once in a while, an incorrect record is returned. It appears to be completely random and intermittent. In fact, if I close the mdb file and reopen it, the record that was just incorrectly returned now returns properly.

Details:
Text64 is a bound field pointing to an autonumber field in the People Table.

Combo69.column (4) is the id number of the record in the People Table.

Any thoughts? The randomness is puzzling.

TIA.

[HansV]

Without seeing the database it's impossible to know what causes the problem. I'd try another way of finding the record; the following is derived from the code generated by the combo box wizard.

Replace the lines

Code: Select all

DoCmd.GoToControl "Text64"

DoCmd.FindRecord CLng(Combo69.Column(4)), acEntire, , acSearchAll, True, acCurrent

with

Code: Select all

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[FieldName] = " & Str(Nz(Me!Combo69.Column(4), 0))
If Not rs.EOF Then
  Me.Bookmark = rs.Bookmark
Else
  MsgBox "Record not found!", vbExclamation
  ' optional: get out
  Exit Sub
End If

Substitute the name of the autonumber field for FieldName.

[PaulW]

Hi Hans,

Thank you for your timely and complete answer. It pointed out a lack of understanding on my part of how Access works. I did not realize that the record source for the combo box had to be the same as the record record source for the form. It makes perfect sense once I think about it, but I had not considered the implications earlier.

Again thanks.