Insert only if not already in table

I have the 2 insert expressions the first one enters the values into the item_cat_subcat table which can have only 1 entry for the same Item Number. I would assume that i would use a WHERE NOT EXISTS in the first expression but not sure how to do that in this string.

  strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "','" & Me.cboAddSub & "');"
  CurrentDb.Execute strSQL, dbFailOnError

Open in new window

skull52IT director Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

PatHartmanCommented:
The better solution is to add a unique index on the ItemNumber column.  That way the database engine will prevent the duplicate from being added.
0
skull52IT director Author Commented:
I do have that but it won't save to the second table that will allow duplicate values because that  unique index won't allow it to save.Because the form has multiple unbound controls i need to do it this way, so how do i exclude it from entering the ITEMNMBR,CAT_ID,SUBCAT_ID,SubCatMod values if they are already in the item_cat_subcat table

Private Sub btnSave_Click()
bSaveClicked = True
Dim strSQL As String

On Error GoTo Error_Procedure

bSaveClicked = True

If Len(Me.cboSubcat & "") = 0 Then
  MsgBox "You must Select a Subcategory", vbOKOnly + vbCritical
  Me.cboSubcat.SetFocus
Else
  strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "','" & Me.cboAddSub & "');"
  CurrentDb.Execute strSQL, dbFailOnError

  strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & Me.cboAttribvl & "','" & Me.cboAddAttrib & "','" & Me.cboAddAttribvl & "');"
  CurrentDb.Execute strSQL, dbFailOnError

  'DoCmd.GoToRecord , "", acNext

  Me.cboSubcat = Null
  Me.cboAttrb = Null
  Me.cboAttribvl = Null
Me.cboAddSub = Null
Me.cboAddAttrib = Null
Me.cboAddAttribvl = Null
End If

Exit_Procedure:
  
  Exit Sub

Error_Procedure:
   MsgBox Err.Number & " - " & Err.Description
   Resume Exit_Procedure

End Sub

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
I would probably just insert a test to see whether a record with the matching value already exists, before even attempting the Append query.  This would give you the ability to inform your user that the value cannot be added because it already exists.

Dale
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

skull52IT director Author Commented:
I don't care about informing the user, If the record is already in the table I don't want it to attempt to insert it again.
0
skull52IT director Author Commented:
The way the form is designed it inserts Item number, Category, and Subcategory into item_cat_subcat that can only have one record so defined by a unique index.  Item Number, Attribute, and Attribute values are inserted into item_attrib_attribvl which can have duplicate item numbers with with multiple Attributes and Attribute values. All i want to do is prevent moe that one record being entered into the item_cat_subcat  table.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Then I would replace lines 13/14 in the above code with something like the following.  I'm still not sure this has the correct columns in the criteria argument of the DCount() function

   if DCount("*", "item_cat_subcat", "(ItemNmbr = " & me.txtItem & ") AND (Cat_ID = " & me.txtCatID & ")") = 0 Then
       strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "','" & Me.cboAddSub & "');"
       CurrentDb.Execute strSQL, dbFailOnError
   end if

Open in new window

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
skull52IT director Author Commented:
Ok I got the following error "2471 - the expression you entered as a query parameter produced this error:" and displayed the item number value
0
skull52IT director Author Commented:
All i need to validate is if the ITEMNMBR that is in txtItem on the form is not in the item_cat_subcat, so i shortened the code to  
 If DCount("ITEMNMBR", "item_cat_subcat", "(ITEMNMBR = " & Me.txtItem & ")") = 0 Then

Open in new window


but I still get the error
0
skull52IT director Author Commented:
I got it it it was missing a single quote on Me.txtItem

 If DCount("ITEMNMBR", "item_cat_subcat", "(ITEMNMBR = '" & Me.txtItem & "')") = 0 Then
0
skull52IT director Author Commented:
I now have another issue if the cboAttrb and cboAttribvl are blank it throws a 3464 - Data type mismatch, i think it is becaust the control values are blank or null How would I encapsulate the controls in the code below to use the Nz function

strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & Me.cboAttribvl & "','" & Me.cboAddAttrib & "','" & Me.cboAddAttribvl & "');"
  CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
John TsioumprisSoftware & Systems EngineerCommented:
You mean this ?
strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & nz(Me.cboAttrb,0) & "','" & nz(Me.cboAttribvl,0) & "','" & nz(Me.cboAddAttrib,0) & "','" & nz(Me.cboAddAttribvl,0) & "');"

Open in new window

0
skull52IT director Author Commented:
Yes, thanks that worked.
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
Microsoft Access

From novice to tech pro — start learning today.