×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread707-1807386

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

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

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

Can anyone help me? Thank you.


Here's my code.



CODE --> VBA

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

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

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

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

(OP)
Hi, Post was edited. Sorry,

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

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

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

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

(OP)
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 --> VBA

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

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

(OP)
For Main Code.

CODE --> VBA

' 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

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

You can try to add a breakpoint in line If Me.txtId.Value <> "" Then 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

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

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

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

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

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

Can't look at it without the password

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

Actually, don't worry, hacked in.

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

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

Can't see anything obvious currently ...

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

(OP)
I understand, you hacked in my vba passowrd :D.

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

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

>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

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

(OP)
>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?

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

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 ...

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

(OP)
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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close