Pioneermfg
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
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
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
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
ASKER
what is the "dlookup"? Is this a function I have to create?
Pioneermfg,
it's an standard function
(in access 2000)
Cesc
it's an standard function
(in access 2000)
Cesc
ASKER
Ok, how do I use it in VB? In case you haven't guessed, I am not a good programmer.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I'm going home. I'll not be able to answer anything until monday
ASKER
Thank you csk 73 and mathwespatrick. Csk 73 gave me the part I needed to make this happen.
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
CN.Execute ssql
end if
Hope this helps!
Cesc