HELP: Add new record instead of Updating only

[mjassalina]

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

Thank you for the help

Private Sub cmdSave_Click()

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

 

[mjassalina]

Sub List_box_Data()
 
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Support1")

sh.Cells.ClearContents

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

Dim qry As String, i As Integer
Dim n As Long
 
If Me.ComboBox1.Value = "ALL" Then
    qry = "SELECT * FROM TBL_Customer"
ElseIf Me.ComboBox1.Value = "Return Pending" Then
    qry = "SELECT * FROM TBL_Customer WHERE Return_Date IS NULL"
Else
    qry = "SELECT * FROM TBL_Customer WHERE " & Me.ComboBox1.Value & " LIKE '" & Me.TextBox1.Value & "'"
End If

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

rst.Open qry, cnn, adOpenKeyset, adLockOptimistic

sh.Range("A2").CopyFromRecordset rst
 
For i = 1 To rst.Fields.Count
    sh.Cells(1, i).Value = rst.Fields(i - 1).Name
Next i
    
rst.Close
cnn.Close
 
    
'===========================================================
    'sh.Range("A:A").NumberFormat = "0"
    'sh.Range("F:G").NumberFormat = "D-MMM-YY"
    'sh.Range("H:I").NumberFormat = "0.0"
    'sh.Range("J:J").NumberFormat = "D-MMM-YY HH:MM AM/PM"
 
'*****************************************************
With Me.lstDatabase
    .ColumnCount = 27
    .ColumnHeads = True
    .ColumnWidths = "50,120,80,140,140,80,80,80,80,90,120,80,140,140,80,80,80,80,90,120,80,140,140,80,80,80,80"


n = sh.Range("A" & Application.Rows.Count).End(xlUp).Row

If n > 1 Then
 .RowSource = "Support1!A2:AA" & n
Else
 .RowSource = "Support1!A2:AA2"
End If
 
End With

If (n - 1) < 2 Then
    Me.lbl_record_count.Caption = (n - 1) & " Record"
ElseIf (n - 1) > 1 Then
    Me.lbl_record_count.Caption = (n - 1) & " Records"
End If
 

'****************************************************

 
End Sub