2003 Glitch -- Search by last name not working

[maura]

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

 

[Allen Browne]

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]

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

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

Reply to group, rather than allenbrowne at mvps dot org.

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

 

[Allen Browne]

Okay, to debug it, add this line to the top of the procedure:
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

Reply to group, rather than allenbrowne at mvps dot org.

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

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

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