Find not on correct record

I have code in my form that is to find a record and go to it. It finds the
record fine but when I make a change, the record changes to the one just
before the one I searched on and makes the change to that record. I can't
see to figure out why

Here's the code:
Private Sub cboQuickFind_AfterUpdate()

Dim MsgNotFound As String
Dim rs As Object
Set rs = Me.Recordset.Clone
On Error GoTo HandleErr

Repaint
Me!txtStatus = Null

If ((Me.StoreNum Me![cboQuickFind] Or Me.NewStoreNum Me!cboQuickFind)
And Me![cboQuickFind] "") Or IsNull(Me.StoreKey) Then
rs.FindFirst "[StoreNum] = '" & Me![cboQuickFind] & "'"

If rs.NoMatch Then
rs.FindFirst "[NewStoreNum] = '" & Me![cboQuickFind] & "'"
If rs.NoMatch Then
Me!txtStatus = "Store Number " + Me![cboQuickFind] + " not
found"
Me![cboQuickFind] = ""

Else
Me.Bookmark = rs.Bookmark
Me![cboQuickFind] = ""
Debug.Print "Quickfind Store "; Me.StoreNum
Check.SetFocus
End If
Else

Me.Bookmark = rs.Bookmark
Me![cboQuickFind] = ""
Debug.Print "Quickfind Store "; Me.StoreNum
Check.SetFocus
End If
Else
Me![cboQuickFind] = ""
GoTo ExitHere
End If

ExitHe
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_EditStoreInformation.cmdFindStore_Click"
End Select
Resume ExitHere
Resume
End Sub

Thanks!!
Willow

Several things that could be contributing to this.

- I've always found RecordsetClone more reliable and useful.

- Declaring a specific type of Recordset (rather than just Object) helps
with debugging.

- Explicitly saving the record before attempting to remove avoids strange
errors. (The Repaint doesn't save and is not needed.)

- The inital value of cboQuickFind is probably Null, but you are testing for
a zero-length string (ZLS), and assigning a ZLS. The construct below handles
the nulls.

Try this kind of thing:

Dim rs As DAO.Recordset
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
If (Me.StoreNum = Me.cboQuickFind) Or (Me.NewStoreNum = Me.cboQuickFind) Or
IsNull(Me.cboQuickFind) Then
'do nothing
Else
strWhere = "([StoreNum] = """ & Me.cboQuickFind & """) OR ([NewStoreNum]
= """ & Me.cboQuickFind & """)"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "not found"
Else
Me.Bookmark = rs.Bookmark
'Do your edit in here.
End if
End If
Me.cboQuickFind = Null

Note: if StoreNum is a Number field (not a Text field), you need to drop the
extra quotes:
strWhere = "([StoreNum] = " & Nz(Me.cboQuickFind, 0) & _
") OR ([NewStoreNum] = " & Nz(Me.cboQuickFind,0) & ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Willow" wrote in message
...
I have code in my form that is to find a record and go to it. It finds the
record fine but when I make a change, the record changes to the one just
before the one I searched on and makes the change to that record. I can't
see to figure out why

Here's the code:
Private Sub cboQuickFind_AfterUpdate()

Dim MsgNotFound As String
Dim rs As Object
Set rs = Me.Recordset.Clone
On Error GoTo HandleErr

Repaint
Me!txtStatus = Null

If ((Me.StoreNum Me![cboQuickFind] Or Me.NewStoreNum
Me!cboQuickFind)
And Me![cboQuickFind] "") Or IsNull(Me.StoreKey) Then
rs.FindFirst "[StoreNum] = '" & Me![cboQuickFind] & "'"

If rs.NoMatch Then
rs.FindFirst "[NewStoreNum] = '" & Me![cboQuickFind] & "'"
If rs.NoMatch Then
Me!txtStatus = "Store Number " + Me![cboQuickFind] + " not
found"
Me![cboQuickFind] = ""

Else
Me.Bookmark = rs.Bookmark
Me![cboQuickFind] = ""
Debug.Print "Quickfind Store "; Me.StoreNum
Check.SetFocus
End If
Else

Me.Bookmark = rs.Bookmark
Me![cboQuickFind] = ""
Debug.Print "Quickfind Store "; Me.StoreNum
Check.SetFocus
End If
Else
Me![cboQuickFind] = ""
GoTo ExitHere
End If

ExitHe
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_EditStoreInformation.cmdFindStore_Click"
End Select
Resume ExitHere
Resume
End Sub

Thanks!!
Willow

Thank you, this solved the problem!!

"Allen Browne" wrote:

Several things that could be contributing to this.

- I've always found RecordsetClone more reliable and useful.

- Declaring a specific type of Recordset (rather than just Object) helps
with debugging.

- Explicitly saving the record before attempting to remove avoids strange
errors. (The Repaint doesn't save and is not needed.)

- The inital value of cboQuickFind is probably Null, but you are testing for
a zero-length string (ZLS), and assigning a ZLS. The construct below handles
the nulls.

Try this kind of thing:

Dim rs As DAO.Recordset
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False
Set rs = Me.RecordsetClone
If (Me.StoreNum = Me.cboQuickFind) Or (Me.NewStoreNum = Me.cboQuickFind) Or
IsNull(Me.cboQuickFind) Then
'do nothing
Else
strWhere = "([StoreNum] = """ & Me.cboQuickFind & """) OR ([NewStoreNum]
= """ & Me.cboQuickFind & """)"
rs.FindFirst strWhere
If rs.NoMatch Then
MsgBox "not found"
Else
Me.Bookmark = rs.Bookmark
'Do your edit in here.
End if
End If
Me.cboQuickFind = Null

Note: if StoreNum is a Number field (not a Text field), you need to drop the
extra quotes:
strWhere = "([StoreNum] = " & Nz(Me.cboQuickFind, 0) & _
") OR ([NewStoreNum] = " & Nz(Me.cboQuickFind,0) & ")"

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Willow" wrote in message
...
I have code in my form that is to find a record and go to it. It finds the
record fine but when I make a change, the record changes to the one just
before the one I searched on and makes the change to that record. I can't
see to figure out why

Here's the code:
Private Sub cboQuickFind_AfterUpdate()

Dim MsgNotFound As String
Dim rs As Object
Set rs = Me.Recordset.Clone
On Error GoTo HandleErr

Repaint
Me!txtStatus = Null

If ((Me.StoreNum Me![cboQuickFind] Or Me.NewStoreNum
Me!cboQuickFind)
And Me![cboQuickFind] "") Or IsNull(Me.StoreKey) Then
rs.FindFirst "[StoreNum] = '" & Me![cboQuickFind] & "'"

If rs.NoMatch Then
rs.FindFirst "[NewStoreNum] = '" & Me![cboQuickFind] & "'"
If rs.NoMatch Then
Me!txtStatus = "Store Number " + Me![cboQuickFind] + " not
found"
Me![cboQuickFind] = ""

Else
Me.Bookmark = rs.Bookmark
Me![cboQuickFind] = ""
Debug.Print "Quickfind Store "; Me.StoreNum
Check.SetFocus
End If
Else

Me.Bookmark = rs.Bookmark
Me![cboQuickFind] = ""
Debug.Print "Quickfind Store "; Me.StoreNum
Check.SetFocus
End If
Else
Me![cboQuickFind] = ""
GoTo ExitHere
End If

ExitHe
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, vbCritical, _
"Error in Form_EditStoreInformation.cmdFindStore_Click"
End Select
Resume ExitHere
Resume
End Sub

Thanks!!
Willow