• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

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
0
Pioneermfg
Asked:
Pioneermfg
  • 4
  • 3
1 Solution
 
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now