ListBox processing

[Sid]

I'm populating a listBox with a query. When adding new
items to the list (from ComboBox), what's the best way to
check that the item to be added isn't already in the
list? (I've tried sequencing through using the ListIndex
as well as a FindFirst against the recordSet of the list
control -- having problems with both approaches.)

Sid Baucom

 

[Guest]

Your description is a little vague, but assuming that
your combo box (henceforth named "combobox") updates a
table that you query to populate the listbox. Lets call
the table "listboxtable", then use the following:


Set db = CurrentDb

sqlString = "SELECT * FROM listboxtable WHERE
listboxvalue = """ & Me.combobox.value & """

Set rs = db.OpenRecordset(sqlString)

If Not rs.EOF And Not rs.BOF Then
MsgBox "You cannot add this value because it is a
duplicate. ", vbOKOnly, "Duplicate Value"
Me.combobox.SetFocus
Exit Sub
End If

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


Hope that helps.

 

[Guest]

Sounds great -- thanks for the help. The reason the
question came to mind is that I'll likely have relatively
few items (typically 1-10) in the listbox, which has
already been retrieved for display -- while there will be
thousands of records in "listboxtable". Just not sure at
what point performance would be impacted -- which led me
to trying to sequence through the records of the listbox.

Thanks again for the solution and your time.

Sid Baucom.

 

[Guest]

Can also query the listbox using the following:

Set rs = Me.listbox.Recordset

This will get the recordset of the listbox that you've
already filtered. Then you can compare that to the
combobox via the code I already noted. Guess I should
have thought of that one while I was responding...