recordset FindFirst not giving proper results...

[InLaNoche]

I have 2 listboxes on a form. the first is populated by a query, and lists all the box numbers for a specific office. The second listbox is populated by referencing the current switch and finding all the boxnumbers that are assigned to that switch.

What I am trying to do is be able to remove the box number from the switch by double clicking on the entry in the second listbox. This way you will know what box you are removing from the switch. The problem is that the first listbox is the actual data, so I would need to look up what was selected in the 2nd listbox in the first (boxnumber). So here is the code:


Private Sub PortList_DblClick(Cancel As Integer)
'using double click to find this record in the BoxNumber listbox
Dim aStr As String
Dim isFound As Integer

'search critera is based on what is selected
aStr = Me.PortList.Value
isFound = -1
Debug.Print aStr

Dim rs As Recordset

Me.BoxNumberList.Requery
Set rs = Me.BoxNumberList.Recordset

rs.MoveFirst


rs.FindFirst "[BoxNumber] LIKE '" & aStr & "'"
If rs.NoMatch Then
MsgBox "ERROR!!!"
Else
MsgBox "looking for " & aStr
MsgBox "found " & rs!BoxNumber & " at " & rs.AbsolutePosition
isFound = rs.AbsolutePosition
Me.BoxNumberList.Selected(isFound) = True
MsgBox Me.BoxNumberList.ListIndex & " Selected"
End If

End Sub

funny thing is that aStr does not match rs!BoxNumber. I though that rs.FindFirst would sit on the found record... Does anyone know why this is not working?

For example, if I click on the first item in the 2nd list box, the msgbox outputs are :
looking for 415-PD001
found 415-PD001 at 522
521 Selected

But when I double click on the 2nd entry I get:
looking for 415-PD002
found 415-PD001 at 522
521 Selected

most often it will find 415-PD001, regardless of what the search is for (aStr). And to make things worse, 415-PD001 is not the 522nd record, it's record 511.

[jonh]

can you upload an example db?

[HiTechCoach]

Check out:
Access 2007 move items between listboxes, filter listbox and clear listbox Items (http://www.msofficegurus.com/post/Access-2007-move-items-between-listboxes-filter-listbox-and-clear-listbox-Items.aspx)

[InLaNoche]

Sorry, should have stated that I am using access 2013 (o365). At this point I do not have trouble seeing the data in the tables. The first table lists all boxnumbers for the selected office. The second table shows a query that references the boxnumber list to find matching switchIP data, and then orders them by port number:

SELECT dbo_BoxNoQuery.SwitchPort, dbo_BoxNoQuery.BoxNumber, dbo_BoxNoQuery.SwitchIP
FROM dbo_BoxNoQuery
WHERE (((dbo_BoxNoQuery.SwitchIP) Like [Forms]![dbo_SwitchList]![SwitchIPCombo]));

The data shows fine, and when I double click on the PortList listbox, it knows the search criteria (as the first msgbox outputs), it just finds the wrong record... My hope is that on a double click, I can remove the SwitchIP and Port info from that record in the boxnumber query, thus removing it from its association.

John, you have been helping me with the other post on the design for this interaction, so maybe this can drift away for now, as a change in the design means that this form needs to be rebuild anyway.

This is more of an "IT guy" needing to know why in this specific case, the findfirst fails. I have used it many times in other forms I have with this database...

[HiTechCoach]

Private Sub PortList_DblClick(Cancel As Integer)
'using double click to find this record in the BoxNumber listbox
Dim aStr As String
Dim isFound As Integer

'search critera is based on what is selected
aStr = Me.PortList.Value
isFound = -1
Debug.Print aStr

Dim rs As Recordset

Me.BoxNumberList.Requery
Set rs = Me.BoxNumberList.Recordset

rs.MoveFirst


rs.FindFirst "[BoxNumber] LIKE '" & aStr & "'"
If rs.NoMatch Then
MsgBox "ERROR!!!"
Else
MsgBox "looking for " & aStr
MsgBox "found " & rs!BoxNumber & " at " & rs.AbsolutePosition
isFound = rs.AbsolutePosition
Me.BoxNumberList.Selected(isFound) = True
MsgBox Me.BoxNumberList.ListIndex & " Selected"
End If

End Sub

funny thing is that aStr does not match rs!BoxNumber. I though that rs.FindFirst would sit on the found record... Does anyone know why this is not working?

For example, if I click on the first item in the 2nd list box, the msgbox outputs are :
looking for 415-PD001
found 415-PD001 at 522
521 Selected

But when I double click on the 2nd entry I get:
looking for 415-PD002
found 415-PD001 at 522
521 Selected

most often it will find 415-PD001, regardless of what the search is for (aStr). And to make things worse, 415-PD001 is not the 522nd record, it's record 511.

The find first is working correctly. It is your code that is not handling the difference between list boxes and recordsets .

List boxes are start with 0 while recordsets start with 1.

Try changing:


isFound = rs.AbsolutePosition

to


isFound = rs.AbsolutePosition -1

TIP: If you have Access 20123 the almost every example from Access 2000 and newer can be used. A lot of examples were done in the Access 200o format and never upgraded. There is usually no reason to upgrade since they work with all the newer versions. This way one example can be used by everyone. I create all my example in the oldest format possible just for this reason. Some of the best code example that as still often referenced used today are Access 97 code! And they still work with the latest version.

[InLaNoche]

@HiTechCoach,

I'm sure I am doing something wrong, but I am not sure what. I though about that (-1) as well, but it does not explain the output info I posted. It is actually looking for something different than what aStr is. When I clicked on 415-PD002, the msgbox stated that it instead found 415-PD001 (see the red bold text near the bottom of my post).

Again, I have this kind of search done in several other forms, using pretty much the same format... This is the only instance that the look up is not working. Also, on the difference between the indexes, this is kind of a null point when as I stated earlier:
most often it will find 415-PD001, regardless of what the search is for (aStr). And to make things worse, 415-PD001 is not the 522nd record, it's record 511.

[EDIT] : Going to redefine the 2 boxes. I left out the autonumber, and maybe I should add them back as search for that instead, and have that column hidden.... Could this be the route of the problem?

[HiTechCoach]

The query not including the primary key (usually an auto number) can definitely be an issue. The primary key is the unique value that that can properly identify the correct record.