If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Find not on correct record
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 |
#3
|
|||
|
|||
Find not on correct record
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 |
Thread Tools | |
Display Modes | |
|
|