FindFirst strange behavior

[ChrisK]

Hi all,

I've been working with the following code in Access 2000:

Private Sub lstQuoteHistory_DblClick(Cancel As Integer)
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "QuoteID=" & Me.lstQuoteHistory
If rst.NoMatch Then MsgBox "!"
Me.Bookmark = rst.Bookmark
'Show controls on the form etc
End Sub

With most records, this code works but on some of the records in the table,
the record is not found and the rst.NoMatch condition is met (this is
obviously just for debugging). QuoteID is a Long Integer and
lstQuoteHistory is bound to this integer value

If I search on the table for the QuoteID values, I can find them with no
problems...

Any ideas?

CK

 

[Allen Browne]

Ideas to consider:

Is the listbox unbound? If so, set its Format property to General Number, so
Access treats the value like a number.

If the list box is Null, the FindFirst string will be mal-formed.

Presumably you have an "Else" before the line that sets the bookmark.

The attempt to set the bookmark will fail if the form is dirty and cannot be
saved.

Any chance the form is filtered, so the desired record is not present?

What is QuoteID? Table field? Text box? Both?

If the form is based on a query, are there 2 QuoteID fields (one from the
primary table, and the foreign key from the related table)?

If it is the same table where this fails, it could be a corrupted index. Try
the 8 steps for the 2nd symptom in this article:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

 

[ChrisK]

Allen,

Thanks for the prompt response. Nice to see a fellow Aussie in here as well
;-)

Is the listbox unbound? If so, set its Format property to General Number, so
Access treats the value like a number.

This list box is not bound to a Control Source. It's just a list of all the
sales quotes in the database matching the search parameters on the form (eg
who did the quote, what type of quote it is)

The Row Source is set to Table/Query, the Row Source is set in code and so
this property is blank in the Property sheet, two columns with no heads and
the widths are set to 0" and 1" to hide the QuoteID field and display the
meaningful information.

I selected the List box and looked amongst all the properties but could not
see how to set the Format of a list box to a General Number. How is this
done?

If the list box is Null, the FindFirst string will be mal-formed.

When set a break point and check the value of the list box, it's returning
the correct number.

Presumably you have an "Else" before the line that sets the bookmark.

As I said, this is just debug code and is just on my PC for the moment while
I try and work out why this is failing

The attempt to set the bookmark will fail if the form is dirty and cannot be
saved.

I'm not making any changes to the data and so the form's not dirty

Any chance the form is filtered, so the desired record is not present?

No, the record is definitely there...

What is QuoteID? Table field? Text box? Both?

QuoteID is the Primary Key in tblQuote

If the form is based on a query, are there 2 QuoteID fields (one from the
primary table, and the foreign key from the related table)?

The form is based on a query but there's only one QuoteID

If it is the same table where this fails, it could be a corrupted index. Try
the 8 steps for the 2nd symptom in this article:
Recovering from Corruption
at:
http://members.iinet.net.au/~allenbrowne/ser-47.html

Thanks for this pointer. This is a replicated database and while it may be
a corrupted Index, recreating the table is a step I'd rather not take just
yet...

CK

 

[Allen Browne]

Chris, my apology. You are correct: unlike a combo, a list box does not have
a Format property.

A compact/repair might fix a corrupted index.

Anything else that is consistent about this?
Is there a particular value that you can search for and get always get the
error?
Is it only if the code is run shortly after opening the form, but the
problem does not occur if you move to the last record?
Anything else?

 

[ChrisK]

Allen,

A bit more digging and I found it.. It wasn't a corrupt index - the form
and the listbox are based on two separate queries and the form query had an
incorrect join that returned less records. Doh!

Thanks for your help!

CK