Preventing duplicate values in listbox.

[gnt]

I know this is not difficult, but I can't seem to find the code for it...

I have a textbox which is used to gather user input. A cmd button adds this value to the listbox (and the table/query on which it's based). No problems there - not too difficult. However, I want to display a msgbox if this value already exists in the list box.

If it matters, the listbox is filtered by a combobox.

Thanks!
-gnt

 

[hermanlaksko]

Looks like a group by on the query that you use for your combobox ;-)

 

[gnt]

Thanks, but I'm not sure I understand how that would help. The duplicate value would not show up in the listbox, true, but it is still added to the table and the user is not made aware that it already exists.

Is there a way to just prevent it from being added to the table altogether and to notify the user?

Thanks-

 

[RBE]

can you make the field on the table a primary key field? that would make it a required unique value and your database would take care of the message. Otherwise the code would have to change as items were added. RBE


Are you 100% sure of where you are going.

 

[CJTyo]

You could make it an "indexed, no duplicates" field instead of the primary key field.

C
*~*~*~*~*~*~*~*~*~*~*~*~*
Insanity is a matter of Perception.

 

[essnrv]

Try this.

Declare A Dynamic Array
Dim strvalues() As String 'Module level variable

'Resize the array in Form load event
Redim strValues(0)

In Command Click Event
dim blnExists as Boolean

intUbound=Ubound(strValues)

For i = 0 to intUbound
if txt1.text = strvalues(i) then
'Value already In the list
blnExists = True
Exit for
end if
Next i

If blnExits= True then
'Do whatever you want
else
Redim Preserve strValues(intUbound+1)
strvalues(intUbound+1)=txt1.text
end if


Hope this helps

 

[FIDIC]

gnt:
Try writing this code as datasource for your combobox:
Select field1, field2, ...,fieldn FROM sourcetable WHERE NOT IN(SELECT * from sourcetable ...WHERE ...)
I´ve used this trick with good results. As a last step you should write a comboboxname.requery. Let me know.
CFMM

 

[gnt]

Thanks for all the help. I don't think I was clear enough as to the structure of my tables/queries.

FIDIC, CJTyo and RBE: Those methods keep the value from being added, but do not notify the user that it hasn't been added. (RBE, I have the warnings off because of the append query, so the duplicate warning does not appear)

essnrv: I worked with your code for awhile but kept getting an error message on intUbound=Ubound(strValues)


At any rate, I micky-moused it and got it to work. Created a hidden combo box, set the rowsource to a Count SQL query. Then displayed a message box when itemdata(0) was greater than 0.

Thanks again for all your help!
-gnt