How do I use an INSERT sql statement to enter text/data into the same row and column of my access table using VBA

Question

Hello stackoverflow community,

UPDATED/EDITED:

I am using an SQL INSERT statement that fires in the click event of 5 combo boxes. I pick a part (automotive) from each combo box and then INSERT statement below (same statement for each combo box) enters that part into the PartName column of the tblParts and also adds a respective location number: 1, 2, 3, 4 and 5 in the PartPlacement column. The code below works just fine for each combo box:

    Dim strSQL As String
strSQL = "INSERT INTO tblParts ([CID], [PartName], [PartPlacement]) VALUES (" & _
        Chr(34) & Forms!frmDataEntry!CID & Chr(34) & ", " & _
         Chr(34) & Me.PartsCombo.Value & Chr(34) & ", " & _
         Chr(34) & 1 & Chr(34) & ")"
        
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

The CID is my FK. CID = CustomerID

However, if I need to update combo box 1 after I incorrectly entered a part, then that is where the fuzz happens (see red text). I don't think that binding a single combo box would work (I absolutely could be wrong). I am not quite sure how I would make a DELETE or UPDATE query work with my INSERT statement.

Table image with proper data and error data (in red)

Many thanks!

Answer 1

I can think of two approaches given your set up. Let's call the first approach the Access approach. Set frmDataEntry's datasource to tblParts and set the detail section's format to continuous records. Then the user can scroll to and edit any mistakes using the default highlighted tools like the record selectors:

For the second approach just add a control(s) like the option group in the top right of the example form and wrap your vba in if statements based on the selected value of the control.

Private Sub cmbPlacement1_AfterUpdate()
 Dim strSQL As String
 If Me.OptionGroup = 2 Then 'update
 'note  the necessary ' ' around string variables
strSQL = "UPDATE tblParts SET PartPlacement = 1, PartName = '" & Me.cmbPlacement1 & "' WHERE CID = " & Me.cmbCID
 
 ElseIf Me.OptionGroup = 1 Then 'insert
 strSQL = "INSERT INTO tblParts ([CID], [PartName], [PartPlacement]) VALUES (" & _
        Chr(34) & cmbCID.Value & Chr(34) & ", " & _
         Chr(34) & Me.cmbPlacement1.Value & Chr(34) & ", " & _
         Chr(34) & 1 & Chr(34) & ")"
ElseIf Me.OptionGroup = 3 Then 'delete 'not aesthetically pleasing as we are selecting a part then ignoring it
   strSQL = "DELETE FROM tblParts WHERE CID = " & Me.cmbCID & " AND PartPlacement = 1"
End If
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Me.Requery 'show changes in the details section
End Sub