Find record in a listbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Baby Face Lee - 17 Dec 2005 15:32 GMT

Hi guys,
Hope you can help me.
I have a listbox that lists two fields from within a table. The first field
(CentreNo) is the bound column.
I'd like the users to be able to 'jump' to a particular CentreNo by
inputting the number in a separate textbox. This is the code in the command
button that is supposed to 'jump' to the required record:
Private Sub cmdJump_Click()
'jump to a selected Centre No
If IsNull([txtJump]) Then
MsgBox "Please enter a Centre No in the box.", vbExclamation, "Oops!"
ElseIf tCount("[CentreNo]", "qry_CentreNoName", "CentreNo=" & txtJump) <
1 Then
MsgBox "Sorry, centre not found.", vbInformation, "Try again"
txtJump = ""
DoCmd.GoToControl "txtJump"
Exit Sub
ElseIf tCount("[CentreNo]", "qry_CentreNoName", "CentreNo=" & txtJump) >
0 Then
DoCmd.GoToControl "lstCentres"
DoCmd.FindRecord txtJump, acEntire, False, acSearchAll, True,
acCurrent, True
End If
End Sub

I can't see why this doesn't work. Can you help?

Thanks in advance,

Lee

Reply to this Message

Marshall Barton - 17 Dec 2005 16:56 GMT

>I have a listbox that lists two fields from within a table. The first field
>(CentreNo) is the bound column.
[quoted text clipped - 18 lines]
> End If
>End Sub

I can't see how your code relates to your question. Either
you are trying to do something other then select a row in
the list box, or you are way off track on how to do it.

Instead of a list box, you should use a combo box, which has
this capability built into its AutoExpand property.

If you must use a list box (single select) and if the value
you are searching for is its BoundColumn, then just set the
list box's Value to the value in the text box:

If IsNull(Me.textbox) Then
    MsgBox "Please enter . . .
Else
    Me.lstCentres = Me.textbox
    If Me.lstCentres.ListIndex = -1 Then
        MsgBox "Sorry, . . .
    End If
End If

If the search column is not the bound coulmn or the list box
is multi-select, then you need to loop through the rows
using the Column property.

Signature

Marsh
MVP [MS Access]

Reply to this Message