Link to home
Start Free TrialLog in
Avatar of Pioneermfg
PioneermfgFlag for United States of America

asked on

combobox check for duplicates before "insert into" statement

I have an application with a combobox.  The box is populated on form load.  the users have the ability to "add" items to the combobox if the item isn't already on the list.  The backend is an MS Access DB and for this table the "duplicates allowed" is set to no.  I need a way to check for duplicates before the "insert into" statement runs.

Private Sub cboPt1_LostFocus()
Dim rsc As adodb.Recordset
Dim NewItem As String
Dim ssql As String

NewItem = "ZZ"
 
   ssql = "insert into itemlist"
   ssql = ssql & " (itemnmbr, itemdesc, uscatvls_2)" & " "
   ssql = ssql & " values('" & cboPt1.Text & "', '" & cboPt1.Text & "', '" & NewItem & "')"
   'Debug.Print ssql
   CN.Execute ssql
   
End Sub
Avatar of csk_73
csk_73
Flag of Spain image

Hi Pioneermfg,

I would do this:
   ssql = "insert into itemlist"
   ssql = ssql & " (itemnmbr, itemdesc, uscatvls_2)" & " "
   ssql = ssql & " values('" & cboPt1.Text & "', '" & cboPt1.Text & "', '" & NewItem & "')"
   'Debug.Print ssql
if isnull(dlookup("uscatvls_2","itemlist","itemnmbr='" & cboPt1.Text & "' AND itemdesc='" & dboPt1.Text & "' AND uscatvls_2='" & NewItem & "'")) then
   CN.Execute ssql
end if

Hope this helps!

Cesc
Avatar of Patrick Matthews
Hi Pioneermfg,

Try using a SQL statement like:

SELECT Count(*) FROM YourTable WHERE SomeField = possible_new_item_here

If that comes back as 0, it's not there; if 1, there is already an entry for it.

Regards,

Patrick
Avatar of Pioneermfg

ASKER

what is the "dlookup"? Is this a function I have to create?
Pioneermfg,

it's an standard function

(in access 2000)

Cesc
Ok, how do I use it in VB? In case you haven't guessed, I am not a good programmer.
ASKER CERTIFIED SOLUTION
Avatar of csk_73
csk_73
Flag of Spain image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I'm going home. I'll not be able to answer anything until monday

Thank you csk 73 and mathwespatrick.  Csk 73 gave me the part I needed to make this happen.