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
LVL 3
PioneermfgAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

csk_73Commented:
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
0
Patrick MatthewsCommented:
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
0
PioneermfgAuthor Commented:
what is the "dlookup"? Is this a function I have to create?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

csk_73Commented:
Pioneermfg,

it's an standard function

(in access 2000)

Cesc
0
PioneermfgAuthor Commented:
Ok, how do I use it in VB? In case you haven't guessed, I am not a good programmer.
0
csk_73Commented:
Pioneermfg,

sorry, it'll only work in access
then create a recordset with this query

dim rs as recordset


    ssql2="Select Count(*) as total from itemlist where itemnmbr='" & cboPt1.Text & "' AND itemdesc='" & dboPt1.Text & "' AND uscatvls_2='" & NewItem & "'"
    set rs=cn.execute ssql2
    if rs!total=0 then
        cn.execute ssql
    endif
    rs.close

Cesc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
csk_73Commented:
I'm going home. I'll not be able to answer anything until monday

0
PioneermfgAuthor Commented:
Thank you csk 73 and mathwespatrick.  Csk 73 gave me the part I needed to make this happen.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.