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

mjassalina · Dec 2, 2020

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

Can anyone help me? Thank you.

Here's my code.

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 · Dec 2, 2020

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

mjassalina · Dec 2, 2020

Hi, Post was edited. Sorry,

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

combo · Dec 2, 2020

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 · Dec 2, 2020

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.

Code:

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 · Dec 2, 2020

For Main Code.

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 · Dec 2, 2020

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 · Dec 3, 2020

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

mjassalina · Dec 3, 2020

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

mjassalina · Dec 3, 2020

Can you look at my file? Thank you.

strongm · Dec 3, 2020

Can't look at it without the password

strongm · Dec 3, 2020

Actually, don't worry, hacked in.

mjassalina · Dec 3, 2020

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

strongm · Dec 3, 2020

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

Can't see anything obvious currently ...

mjassalina · Dec 3, 2020

I understand, you hacked in my vba passowrd

.

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

strongm · Dec 3, 2020

>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 · Dec 3, 2020

>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 · Dec 3, 2020

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 · Dec 3, 2020

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 · Dec 3, 2020

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

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

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

Programmer

MIS

Programmer

Technical User

Programmer

Programmer

Technical User

Programmer

Programmer

Programmer

MIS

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor