We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

combobox check for duplicates before "insert into" statement

Pioneermfg
Pioneermfg asked
on
Medium Priority
355 Views
Last Modified: 2006-11-18
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
Comment
Watch Question

Commented:
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
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Author

Commented:
what is the "dlookup"? Is this a function I have to create?

Commented:
Pioneermfg,

it's an standard function

(in access 2000)

Cesc

Author

Commented:
Ok, how do I use it in VB? In case you haven't guessed, I am not a good programmer.
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
I'm going home. I'll not be able to answer anything until monday

Author

Commented:
Thank you csk 73 and mathwespatrick.  Csk 73 gave me the part I needed to make this happen.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.