Probs with a Listbox - please help!!!!

[Lee]

Hi everyone,
I really hope you can help as I'm tearing (what's left of
my) hair out!!!
A form has a listbox (called lstCentres) which gets its
data from a separate (query) source to the main form -
it's not bound to the forms data.
The list contains lots of records so I want to be able to
allow the user to 'jump' to a specific record rather than
scroll endlessly....simple eh?
I just can't get this to work though and I don't know why.
Here's the code:

Dim rs As DAO.Recordset
Dim varCentre As Variant

varCentre = InputBox(blah blah...)

Set rs = Me.RecordsetClone
rs.FindFirst "lstCentres=" & varCentre
If rs.NoMatch Then
msgbox(blah blah)
Else
Me.Bookmark = rs.Bookmark
End If

The bound column of the listbox is 'CentreNo' which is a
five-digit LongInteger and is the number that will be put
in the input box.
When I try this out I'm told 'lstCentres' isn't
recognised/valid.
Any ideas would be really appreciated.
Please spell it out to me as I've lost the plot!
Many thanks,

Lee

 

[Albert D. Kallal]

You should try using the combo box wizard to do this, as it will write all
the code you need.

However, lets take a look at what you got:

rs.FindFirst "lstCentres=" & varCentre

I would write the above as:

rs.FindFirst "lstCentres = " & varCentre

Note how I spaced things out a bit. Further, do you really have a field in
the table called lstCentres ? Remember, the FindFirst is a sql "where"
clause without the word "where". That expression that you feed to findFirst
is based on the FIELDS IN THE TABLE. There is NO relation to the text boxes
on your form. Those text boxes on your form can have any name, and they
certainly don't have to be the same as the field names in the table.
However, when you use FindFirst, YOU MUST USE valid field names FROM THE
TABLE, and not the form.

So, if in fact, you really do have a field in your table called lstCentres,
then the above should work.

If lstCentres is a text field, and NOT a number (long id), the you must
surround the condition with quotes just like in sql.

So, if a text field, then we must still use valid sql here, and you will
have to use:

rs.FindFirst "lstCentres = '" & varCentre & "'"

 

[Lee]

Thanks for the reply Albert and for the explanations. As
you suggest, I've confused a control with the data behind
the control!!! I'll go back and have another bash at
this.

Best Regards,

Lee