• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 38
  • Last Modified:

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

0
skull52
Asked:
skull52
2 Solutions
 
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
 
skull52Author 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 FyeCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
skull52Author 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
 
skull52Author 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 FyeCommented:
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
 
skull52Author 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
 
skull52Author 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
 
skull52Author 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
 
skull52Author 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
 
skull52Author Commented:
Yes, thanks that worked.
0
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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