Force closed or Crashed when Clicking Update/Save Button Userform Excel VBA 1

[mjassalina]

Hi, I encountered force closed when saving. Error was not displayed,

Can anyone help me? Thank you.


Here's my code.

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.txtId.Value <> "" Then
        qry = "SELECT * FROM TBL_Customer WHERE ID = " & Me.txtId.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.txtId.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 = ""
    
    Call Main
    MsgBox "Updated Successfully", vbInformation
    Call Me.List_box_Data
    
End Sub

 

[strongm]

You probably need to state what your question/problem is ...

 

[mjassalina]

Hi, Post was edited. Sorry,

When I clicked the save button, excel was forced to close without any errors.

 

[combo]

After processing recordset and controls there are two calls to other procedures and MsgBox in the middle. If you can't see "Updated Successfully" message, post the contents of "Main" procedure, otherwise "List_box_Data" in the userform's module.

combo

 

[mjassalina]

I can't see "Update Successful", Excel was forced closed automatically.

EDIT: Even though I removed "Call Main" and "Call List box Data", Excel was forced closed automatically.

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
 

With Me.lstDatabase
    .ColumnCount = 27
    .ColumnHeads = True
    .ColumnWidths = "30,30,70,50,70,50,55,70,30,60,100,80,30,55,55,100,100,100,100,150,500,70,70,70,70,100,100"


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

End Sub

 

[mjassalina]

For Main Code.

' PROGRESS BAR CODES
Sub Main()
Dim i, tot As Integer
tot = 5000
For i = 1 To tot
If i Mod 5 = 0 Then
ProgressBar i / tot
End If
Next i
lblDone.Width = 0
lblPct.Visible = False
End Sub

 

[combo]

You can try to add a breakpoint in line [tt]If Me.txtId.Value <> "" Then[/tt] run the form (or earlier code if there are project level variables). When you click the cmdSave button, the code should stop in this line, now execute it line by line. Observe which line causes problem.
You can also set the error trapping level (VBE options, 'general' tab, error trapping frame) to 'break on all errors'.
When excel closes, check in task manager if its process remains in the memory.

combo

 

[DjangMan]

You also have no error trapping in your code. You should add that.

 

[mjassalina]

hI combo, I already done using breakpoint method. Unfortunately, Random line, excel was crashed then re-opened as Autosave.

 

[mjassalina]

Can you look at my file? Thank you.

 

[strongm]

Can't look at it without the password

 

[strongm]

Actually, don't worry, hacked in.

 

[mjassalina]

I'm Sorry, stongm.. here's the new file without vba password.
just by-pass the login form.

 

[strongm]

No, not your login form - the VBA password. But, as I say, I've hacked past that.

Can't see anything obvious currently ...

 

[mjassalina]

I understand, you hacked in my vba passowrd .

What do you mean? Is saved button working to you?

 

[strongm]

>Is saved button working to you?

Yes. Or more accurately, yes if certain fields are completed in the details frame. Several of them unfortunately cause type mismatch errors if left empty

 

[mjassalina]

>Or more accurately, yes if certain fields are completed in the details frame.
>Several of them unfortunately cause type mismatch errors if left empty - What do you think is the culprit?

 

[strongm]

I meant that yes, it works for me if all the fields are complete.

However, if you leave them uncompleted some of the fields cause type mismatches.

I am not going to go through and check/verify each and every one of them for you, though, I am afraid ...

 

[mjassalina]

Thank you for your reply..I checked also what you said, And yes, you're right, if incomplete will result to excel crash...

But there is certain field that can be blank during encoding. hmmmmmmmmmmm...... Anyways, Thank you.

 

[mjassalina]

Hi all, When I try to 2 different PC... Excel was not crashed. hmmm. something fishy.