Add new record instead of Updating only

[mjassalina]

Hi.. Good evening,

Here I am again,

Private Sub cmdSave_Click()
''''''''Add Validation here '''''''''''''

If Me.cmbShift.Value = "" Then
MsgBox "Please enter the Shift Encountered", vbCritical
Exit Sub
End If

If Me.txtItem.Value = "" Then
MsgBox "Please enter the Item Number", vbCritical
Exit Sub
End If

If Me.txtLot.Value = "" Then
MsgBox "Please enter the Lot", vbCritical
Exit Sub
End If

If Me.txtPN.Value = "" Then
MsgBox "Please enter the Product Number", vbCritical
Exit Sub
End If

If IsDate(Me.txtDate.Value) = False Then
MsgBox "Please enter the correct date", vbCritical
Exit Sub
End If



'''''''''''''''''''''''''''''''''''''''''

Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim qry As String

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\Database.accdb"

If Me.txtRowNumber.Value <> "" Then
qry = "SELECT * FROM TBL_Customer WHERE ID = " & Me.txtRowNumber.Value
Else
qry = "SELECT * FROM TBL_Customer Where ID = 0"
End If

rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

If rst.RecordCount = 0 Then
rst.AddNew

End If

rst.Fields("Sen").Value = Me.cmbShift.Value
rst.Fields("Date").Value = VBA.CDate(Me.txtDate.Value)
rst.Fields("Lot").Value = Me.txtLot.Value
rst.Fields("Product").Value = Me.txtPN.Value
rst.Fields("Item_No").Value = Me.txtItem.Value
rst.Fields("Serial_No").Value = Me.txtSerial.Value
rst.Fields("Line_No").Value = Me.cmbLine.Value
rst.Fields("Shift").Value = Me.cmbShift1.Value
rst.Fields("Defect").Value = Me.cmbDefect.Value
rst.Fields("Details_of_Defect").Value = Me.txtDet.Value
rst.Fields("Connector_Name").Value = Me.txtCon.Value
rst.Fields("Quantity").Value = Me.txtQty.Value
rst.Fields("Process").Value = Me.txtProcess.Value
rst.Fields("Detection_of_Defect").Value = Me.cmbDetection.Value
rst.Fields("Responsible_Person").Value = Me.cmbResPer.Value
rst.Fields("Responsible_Leader").Value = Me.cmbResLead.Value
rst.Fields("Repair_Personnel").Value = Me.cmbRepair.Value
rst.Fields("Removed_Details").Value = Me.txtRemoved.Value
rst.Fields("Repair_and_Install_Details").Value = Me.txtIns.Value
rst.Fields("Standard").Value = Me.txtStd.Value
rst.Fields("Confirmed_by").Value = Me.txtConf.Value
rst.Fields("Category").Value = Me.cmbCat.Value
rst.Fields("Remarks").Value = Me.txtRemark.Value
rst.Fields("Encoder").Value = Me.txtuser.Value
rst.Fields("Time Encoded").Value = VBA.Now

rst.Update

Me.txtRowNumber.Value = ""
Me.cmbShift.Value = ""
Me.txtDate.Value = ""
Me.txtLot.Value = ""
Me.txtPN.Value = ""
Me.txtItem.Value = ""
Me.txtSerial.Value = ""
Me.cmbLine.Value = ""
Me.cmbShift1.Value = ""
Me.cmbDefect.Value = ""
Me.txtDet.Value = ""
Me.txtCon.Value = ""
Me.txtQty.Value = ""
Me.txtProcess.Value = ""
Me.cmbDetection.Value = ""
Me.cmbResPer.Value = ""
Me.cmbResLead.Value = ""
Me.cmbRepair.Value = ""
Me.txtRemoved.Value = ""
Me.txtIns.Value = ""
Me.txtStd.Value = ""
Me.txtConf.Value = ""
Me.cmbCat.Value = ""
Me.txtRemark.Value = ""


MsgBox "Updated Successfully", vbInformation

Call Me.List_box_Data
End Sub



I encountered add new instead of update when i edit data entry from Listbox.
EDIT: I am using Excel User form VBA with MS Access as Database


Thank you for the help

 

[Andrzejek]

Have you considered building your Insert / Update statements on-the-fly and execute it?
This way (IMHO) you have full control of what’s going on.

qry = "SELECT * FROM TBL_Customer WHERE ID = " & Val(Me.txtRowNumber.Value)

rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

If rst.RecordCount = 0 Then
    qry = "Insert Into TBL_Customer ...”
Else
    qry = "Update TBL_Customer Set ... Where ID = ” & Val(Me.txtRowNumber.Value)
End If

Cnn.Execute qry

---- Andy

There is a great need for a sarcasm font.

 

[Andrzejek]

...and, instead of listing 24 controls in your code, if you want to set the [tt]Value = ""[/tt] to all TextBoxes and ComboBoxes on your Form, you can just do:

Dim c As Control

For Each c In Me.Controls
    If (TypeOf c Is MSForms.TextBox) Or _
        (TypeOf c Is MSForms.ComboBox) Then
        c.Value = ""
    End If
Next c

---- Andy

There is a great need for a sarcasm font.

 

[mjassalina]

Thanks for the reply, fortunately, I found out the culprit.

My double click code in listbox does not match with my textbox/ combobox.