Access VBA DAO Find routine on Form Combobox

[Matt Slattery]

I have a standard "Find" routine triggered by the AfterUpdate event on an
Unbound Form Combobox.
Code as follows:

Sub cboGoto_AfterUpdate()
Dim rs as Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[JobNo] = " & Me.cboGoto
Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

This event has been working perfectly for several months and has suddenly
stopped working in a certain DB. The exact same code runs smoothly in other
DB's without a problem.

Notes:
The field in question is a numeric data type.
The event procedure does actually run, but it always goes to the first
record in the form, rather than to the record it should find.
The numbers in the combobox can always be found in the destination field.
All other routines in the DB run fine

The DB in question is a Front / Back end setup in Access 2000 File Format
All clients are running Access 2003 + WinXP Pro with latest updates / patches

Without solving the problem, I have:
Tried the "combobox wizard" to "Find a record on my form based on the value
I selected in my combobox".
Recreated the combobox and the event code from scratch.
Recompiled the DB.
Imported all objects into a new DB file.
Tried previous DAO Libraries + used both Early and Late binding versions of
the event procedure.

I am at wits end for what would seem to be such a simple problem.

I am VERY curious to see if anybody else has experienced similar problems or
if anybody has any ADODB code to goto a specific record on a form based on a
combobox selection.

My kindest thanks to anybody who can help shed light on this situation

Regards,
Matt Slattery

 

[Allen Browne]

Matt, this should work, and the steps you have taken make good sense.

Try the code from this link in the combo's AfterUpdate:
http://allenbrowne.com/ser-03.html
Improvements over the wizard-generated code include:
- Forcing the save the the existing record (avoid some bugs associated with
clearing hanging event queue before trying to move);
- Testing NoMatch, and indicating if something went wrong, instead of
assuming it worked.

Presumably you have also tried a repair (Tools | Databse Utilities |
Repair), and if so, that should have repaired any corrupt index.

Is there any chance that Access is finding the right record, but something
else is changing it afterwards? You could see that happening if you add a
breakpoint (F9) and then single-step through the code (F8).

You say the field is a Number type. Not Decimal type by any chance? JET
cannot handle that type correctly.

Presumably Name AutoCorrect is off? Otherwise Access is likely to get
confused and refer to the wrong things. More info:
http://allenbrowne.com/bug-03.html

You say you have applied all the patches. I think it's worth double-checking
that there is only one copy of msjet40.dll anywhere on path, and that it is
version 4.0.8xxx.0 (Properties of the file when you find it in My Computer.)

How many records are in the combo's RowSource when this error occurs?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

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

I have a standard "Find" routine triggered by the AfterUpdate event on an
Unbound Form Combobox.
Code as follows:

Sub cboGoto_AfterUpdate()
Dim rs as Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[JobNo] = " & Me.cboGoto
Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

This event has been working perfectly for several months and has suddenly
stopped working in a certain DB. The exact same code runs smoothly in
other
DB's without a problem.

Notes:
The field in question is a numeric data type.
The event procedure does actually run, but it always goes to the first
record in the form, rather than to the record it should find.
The numbers in the combobox can always be found in the destination field.
All other routines in the DB run fine

The DB in question is a Front / Back end setup in Access 2000 File Format
All clients are running Access 2003 + WinXP Pro with latest updates /
patches

Without solving the problem, I have:
Tried the "combobox wizard" to "Find a record on my form based on the
value
I selected in my combobox".
Recreated the combobox and the event code from scratch.
Recompiled the DB.
Imported all objects into a new DB file.
Tried previous DAO Libraries + used both Early and Late binding versions
of
the event procedure.

I am at wits end for what would seem to be such a simple problem.

I am VERY curious to see if anybody else has experienced similar problems
or
if anybody has any ADODB code to goto a specific record on a form based on
a
combobox selection.

My kindest thanks to anybody who can help shed light on this situation

Regards,
Matt Slattery