FindFirst fails when setting record from List Box

[ScottK1396]

TWIMC

I write you as an intermediate part-time Access developer. So, “hit the books” can be a valid answer. However, I believe a couple extra applied brain cells can help me with something I’m overlooking.

I have an Access form that helps process a work list among the different offices in my company. The form has a list box that is used to display a record with code behind OnClick.
I adopted code from [http: // allenbrowne.com / ser-03.html] to use on a list box, and it has worked in the past. The list box displays a subset of the table, filtered on status and location (no joins). The search is keyed on the autonumber ID field the control is bound to.
However, it will error out (“Not Found: Filtered?” as seen in the code) when I select a record of a certain type of upgrade (field value), which is displayed in the list but is not the bound field.
The data is on a SharePoint, and has a couple of nested-recordset fields (they aren’t involved in the list box, but are displayed on the form)

The record exists, obviously; I see it in the table, and it would not be in the list otherwise. What could it breaking on? Should I be using the same method for a list box as I would for a combo?

More info
lstComputers – list box
RowSource = SELECT ID, User_DispName, Comp_Name, Upgrade_Target FROM Processing_
WHERE (Site=[cboSite] AND Status = "Interview") ORDER BY User_DispName;
BoundColumn = 1 {AutoNumber ID field}
RowSourceType = Combo/Query
ColumnCount= 4
ColumnWidths = 0";1";1";0.75"

Code (from http: // allenbrowne.com / ser-03.html )
Private Sub lstComputers_Click()

Dim rs As DAO.Recordset

If Not IsNull(Me.lstComputers) Then
'Save before move.
If Me.Dirty Then
Me.Dirty = False
End If
'Search in the clone set.
Set rs = Me.RecordsetClone
rs.FindFirst "[ID] = " & Me.lstComputers
If rs.NoMatch Then
MsgBox "Not found: filtered?"
Else
'Display the found record in the form.
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If

End Sub

 

[pbaldy]

Is the listbox set to multiselect?

 

[ScottK1396]

Is the listbox set to multiselect?

MultiSelect is set to None

 

[pbaldy]

Dang, my guess was wrong.

The code looks okay offhand. Do you know how to set a breakpoint so you can follow the code as it runs, and check the value coming from the listbox?

http://www.baldyweb.com/Debugging.htm

 

[ScottK1396]

I debugged by setting a text box to lstComputers.Value. It immediately populates with the correct ID in the target list. That happens before any logic in the subroutine. I places a few breaks, and what I found is that Access thinks rs.NoMatch is false. Of course it would. This despite a valid [ID] with a corresponding record.

Does [ID] need to be consecutive for the search to work? If so, then random clicking would trip the error, and not just one value in a field.

One note, the form does include joined fields from tables in the same SP site, however, nothing is keyed to the field (Upgrade_Target ) where the problem values reside. Simply put, this field dictates to the tech whether a user gets new equipment("Refresh"), or an in-place Win7 upgrade("UDI"). It's the latter value in a record that, when clicked, delivers the message.

 

[pbaldy]

I don't believe the recordset has to be sorted for that code to work (I've never bothered). Can you post the db here, or a representative sample?

 

[missinglinq]

...Access thinks rs.NoMatch is false. Of course it would. This despite a valid [ID] with a corresponding record.

If a match does exist then NoMatch is False!

But Access has to think that NoMatch is True, or the "Not found: filtered?" messagebox would not appear. Assuming that the ID is, indeed an AutoNumber Field (I say this because this type of code frequently falls over because of a difference between the syntax used and the correct syntax called for by the Field's Datatype) my guess would be that the Listbox Control has become Corrupted. Controls can, and do, get Corrupted, and when one starts exhibiting illogical behavior Corruption is always a good bet. You could simply delete the current Listbox and recreate it.

Linq ;0)>

 

[ScottK1396]

That would be odd behaviour, indeed; it happens on two forms I built (granted, I duplicated one (schedule) to the other (Build).

As for the NoMatch, I misspoke; it's true, of course. Now to figure out why it turns true.

Does a FindFirst fail if a record is found, but a field from a joined table record is not found? I have a couple of fields from detail tables linked in.

 

[ScottK1396]

I have figured out that the not found issue extends to joined fields from other tables. The JOIN is sound, and some times it finds the record. I'm wondering if it's just a response time (it's not taking any longer to throw the error).

I rewrote the form to DLOOKUP the related information, and set the data source to a single table. It was mostly read-only, and if the users need to write a field I can allow for that using a CurrentDb.OpenRecordset("table").AddNew. The new form now works like a charm.

Thanks to all that tried to wrap their head around the problem.