Object required - Resume Exit_cmdSubmit_Click 2

[comnetlimited]

Hi folks,
(Win2000 sp4, Access 2000, MADO 2.7 Library)

Tables: tblCustomers, tableDebtorLedger.
Form: frmNewTransaction

I got a dialog box "Object Required" everytime I click Submit button. I did a control break and it stops at line Resume Exit_cmdSubmit_Click (Refer to code below).

Option Compare Database
Private Sub cmdExit_Click()
DoCmd.Close
End Sub

Private Sub cmdReset_Click()
On Error GoTo Err_cmdReset_Click

cboCustomerId = ""
lblCustomerName = ""
txtLoanAmount = ""
cboCustomerName.SetFocus

Exit_cmdReset_Click:
Exit Sub

Err_cmdReset_Click:
MsgBox Err.Description
Resume Exit_cmdReset_Click

End Sub

Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click

Dim rstNewLoan As ADODB.Recordset

Set rstNewLoan = New ADODB.Recordset

rstOrder.Open "tblDebtorLedger", CurrentProject.Connection, adOpenStatic, adLockOptimistic
If rstNewLoan.Supports(adAddNew) Then

With rstNewLoan
.AddNew
.Fields("CustomerId") = cboCustomerId
.Fields("CustomerName") = "Testing"
.Fields("LoanAmount") = txtLoanAmount
.Update
cmdReset_Click
End With
End If

rstNewLoan.Close
Set rstNewLoan = Nothing

Exit_cmdSubmit_Click:
Exit Sub

Err_cmdSubmit_Click:
MsgBox Err.Description
Resume Exit_cmdSubmit_Click
End Sub


Am I missing some object or something in the Tools-reference?

Please help.

 

[PHV]

rstOrder.Open
Where is the rstOrder object defined/instantiated ?
You don't use Option Explicit instruction ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RickSpr]

The error dialog box is being displayed by the MsgBox function preceding the Resume statement. Ctrl-Break when the message box is being displayed simply halts execution at the next statement. The error message is not about the Resume statement, though: It's about an error that is being trapped and sending control to the MsgBox function (that is, to the Err_cmdSubmit_Click label).

To find out which statement the error is actually occurring on, you should go to the VBA Editor, choose Tools|Options, select the General tab, and select the Break On All Errors option. Then return to the Access window and re-test. Because you've set Break On All Errors, a break will happen on the actual statement, rather than transferring to the error handler.

I can shortcut you though. The statement that starts "rstOrder.Open" is obviously treating rstOpen as an ADODB Recordset object, but the variable isn't declared. That's causing your error.

If you put an Option Explicit statement at the top of the module, you'll get an easier to understand compile error rather than the error you're getting. If you set the VBA Editor option "Require Variable Declaration", every module you create from then on will include an Option Explicit statement. Most of us set that option permanently to avoid confusing problems like this.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[comnetlimited]

Thank you so much both of yout RickSpr and PHV for your help.
Much appreciated.