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
|
|||
|
|||
2003 Glitch -- Search by last name not working
I have a database that searches 3 ways: by caseid, by last six of a vin and
by last name. After an automatic update the search by last name doesn't work. This is a split datebase and two other computers running Microsoft 2003 one is still being able to search by last name but the others cannot. Here is my code, this database has been running for over 5 years and I have never had this problem. I am wondering if it was the update or something else. We have repaired and compacted the database and that hasn't done anything either. PLEASE HELP! Code for the three unbound comboboxes: Private Sub cboFindByCaseID_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Str(Nz(Me![cboFindByCaseID], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Private Sub cboFindByDebtor_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Me![cboFindByDebtor].Value & "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark 'cboFindByDebtor.Text = Me![DbLastName] End Sub Private Sub cboFindByVin_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "right([VIN],6) = '" & Me![cboFindByVin] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub |
#2
|
|||
|
|||
2003 Glitch -- Search by last name not working
Suggestions:
1. After a FindFirst, you must check NoMatch. The outcome of testing EOF is undefined. 2. Make sure cboFindByCaseID is unbound, and its Bound Column is the same data type and value as the CaseID field in your table. 3. Explicitly save the record before attempting to move. 4. To help debugging, use a string for the criteria (so you can verify it is what you expect), and pop up a MsgBox if there is no match. 5. Narrow down the kind of recordset object, so you know exactly what's happening. Perhaps something like this: Private Sub cboFindByCaseID_AfterUpdate() ' Find the record that matches the control. Dim rs As DAO.Recordset Dim strWhere As String If Not IsNull(Me.cboFindByCaseID) Then 'Save any edits first. If Me.Dirty Then Me.Dirty = False strWhere = "[CaseID] = " & Me.cboFindByCaseID 'Debug.Print strWhere Set rs = Me.Recordset.Clone rs.FindFirst strWhere If rs.NoMatch Then MsgBox "Not found. Filtered?" Else Me.Bookmark = rs.Bookmark End If End If Set rs = Nothing End Sub -- 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. "maura" wrote in message ... I have a database that searches 3 ways: by caseid, by last six of a vin and by last name. After an automatic update the search by last name doesn't work. This is a split datebase and two other computers running Microsoft 2003 one is still being able to search by last name but the others cannot. Here is my code, this database has been running for over 5 years and I have never had this problem. I am wondering if it was the update or something else. We have repaired and compacted the database and that hasn't done anything either. PLEASE HELP! Code for the three unbound comboboxes: Private Sub cboFindByCaseID_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Str(Nz(Me![cboFindByCaseID], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Private Sub cboFindByDebtor_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Me![cboFindByDebtor].Value & "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark 'cboFindByDebtor.Text = Me![DbLastName] End Sub Private Sub cboFindByVin_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "right([VIN],6) = '" & Me![cboFindByVin] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub |
#3
|
|||
|
|||
2003 Glitch -- Search by last name not working
Allen, my code has been working for years. I don't know what happened other
than we did an update on the system and now it won't work. Your code isn't working either (I tried it on a backup)... With my code, I do get an error that says a name isn't on the list, and the user has to choose from the list. And still my biggest confusion is why now after years of it working why doesn't it show anything in that field? Thanks, maura "Allen Browne" wrote: Suggestions: 1. After a FindFirst, you must check NoMatch. The outcome of testing EOF is undefined. 2. Make sure cboFindByCaseID is unbound, and its Bound Column is the same data type and value as the CaseID field in your table. 3. Explicitly save the record before attempting to move. 4. To help debugging, use a string for the criteria (so you can verify it is what you expect), and pop up a MsgBox if there is no match. 5. Narrow down the kind of recordset object, so you know exactly what's happening. Perhaps something like this: Private Sub cboFindByCaseID_AfterUpdate() ' Find the record that matches the control. Dim rs As DAO.Recordset Dim strWhere As String If Not IsNull(Me.cboFindByCaseID) Then 'Save any edits first. If Me.Dirty Then Me.Dirty = False strWhere = "[CaseID] = " & Me.cboFindByCaseID 'Debug.Print strWhere Set rs = Me.Recordset.Clone rs.FindFirst strWhere If rs.NoMatch Then MsgBox "Not found. Filtered?" Else Me.Bookmark = rs.Bookmark End If End If Set rs = Nothing End Sub -- 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. "maura" wrote in message ... I have a database that searches 3 ways: by caseid, by last six of a vin and by last name. After an automatic update the search by last name doesn't work. This is a split datebase and two other computers running Microsoft 2003 one is still being able to search by last name but the others cannot. Here is my code, this database has been running for over 5 years and I have never had this problem. I am wondering if it was the update or something else. We have repaired and compacted the database and that hasn't done anything either. PLEASE HELP! Code for the three unbound comboboxes: Private Sub cboFindByCaseID_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Str(Nz(Me![cboFindByCaseID], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Private Sub cboFindByDebtor_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Me![cboFindByDebtor].Value & "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark 'cboFindByDebtor.Text = Me![DbLastName] End Sub Private Sub cboFindByVin_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "right([VIN],6) = '" & Me![cboFindByVin] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub |
#4
|
|||
|
|||
2003 Glitch -- Search by last name not working
Okay, to debug it, add this line to the top of the procedu
Debug.Print "cboFindByCaseID_AfterUpdate run at " & Now() to verify the code is running. After running it, open the Immediate Window (Ctrl+G) to see if it did run. If not, perhaps the AfterUpdate property of the combo is not set to [Event Procedure], or perhaps security/permission issues are preventing the code from executing. When you know it's running, remove the single quote from: 'Debug.Print strWhere When it runs, copy the expression into the WHERE clause of a query, and check that the query runs. Next, add the line: Stop just above the line: If rs.NoMatch Then When it runs and stops here, pause the mouse over NoMatch to see its conclusion. If there is no match, check that the record is actually in the form's data (not filtered, or DataEntry, or ...) Then press F8 to single-step through the code and see what's going on. (The serious flaw in the code was checking EOF instead of NoMatch.) -- 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. "maura" wrote in message ... Allen, my code has been working for years. I don't know what happened other than we did an update on the system and now it won't work. Your code isn't working either (I tried it on a backup)... With my code, I do get an error that says a name isn't on the list, and the user has to choose from the list. And still my biggest confusion is why now after years of it working why doesn't it show anything in that field? Thanks, maura "Allen Browne" wrote: Suggestions: 1. After a FindFirst, you must check NoMatch. The outcome of testing EOF is undefined. 2. Make sure cboFindByCaseID is unbound, and its Bound Column is the same data type and value as the CaseID field in your table. 3. Explicitly save the record before attempting to move. 4. To help debugging, use a string for the criteria (so you can verify it is what you expect), and pop up a MsgBox if there is no match. 5. Narrow down the kind of recordset object, so you know exactly what's happening. Perhaps something like this: Private Sub cboFindByCaseID_AfterUpdate() ' Find the record that matches the control. Dim rs As DAO.Recordset Dim strWhere As String If Not IsNull(Me.cboFindByCaseID) Then 'Save any edits first. If Me.Dirty Then Me.Dirty = False strWhere = "[CaseID] = " & Me.cboFindByCaseID 'Debug.Print strWhere Set rs = Me.Recordset.Clone rs.FindFirst strWhere If rs.NoMatch Then MsgBox "Not found. Filtered?" Else Me.Bookmark = rs.Bookmark End If End If Set rs = Nothing End Sub "maura" wrote in message ... I have a database that searches 3 ways: by caseid, by last six of a vin and by last name. After an automatic update the search by last name doesn't work. This is a split datebase and two other computers running Microsoft 2003 one is still being able to search by last name but the others cannot. Here is my code, this database has been running for over 5 years and I have never had this problem. I am wondering if it was the update or something else. We have repaired and compacted the database and that hasn't done anything either. PLEASE HELP! Code for the three unbound comboboxes: Private Sub cboFindByCaseID_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Str(Nz(Me![cboFindByCaseID], 0)) If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub Private Sub cboFindByDebtor_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "[CaseID] = " & Me![cboFindByDebtor].Value & "" If Not rs.EOF Then Me.Bookmark = rs.Bookmark 'cboFindByDebtor.Text = Me![DbLastName] End Sub Private Sub cboFindByVin_AfterUpdate() ' Find the record that matches the control. Dim rs As Object Set rs = Me.Recordset.Clone rs.FindFirst "right([VIN],6) = '" & Me![cboFindByVin] & "'" If Not rs.EOF Then Me.Bookmark = rs.Bookmark End Sub |
Thread Tools | |
Display Modes | |
|
|