424 Error - Input box to Report

[TitaniaTiO2]

I am working with some code that Steve Sanford previously helped me with. I
have modified it to try and add extra fun things.

The premise is that I have a form with a combo box and a list box. The user
selects an employee name from the combo box (cmbEmployeeName) and then
multiple documents from the list box (lstboxSOPSToChooseFrom). Upon clicking
the close button, I want to generate a report. One (1) report for each
document that was highlighted in the list box. For each report, I want to
enter a unique history number on a field on the report. I am trying to do
this via an input box. The input box value should feed to a text box on the
form (txtHistory)

The input box appears, allows me to enter a number but when I click ok, I
get the error message:

424
Object Required

Here is my code...

Private Sub cmdClose_Click()

On Error GoTo ErrorHandler

Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String
Dim i As Integer
Dim WasAdded As Boolean
Dim stDocName As String
Dim History As Integer

Set ctl = Forms!frmAssignSopsToEmployee.lstboxSOPSToChooseFrom

WasAdded = False
' open a recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblTraining")

'loop thru the items in the list box
For i = 0 To ctl.ListCount - 1
If ctl.Selected(i) Then
rs.AddNew
rs!PersonID = Me.cmbEmployeeName
rs!DocumentNumber = ctl.Column(0, i)
rs.Update

History = InputBox("Enter History Number for Document: " &
ctl.Column(0, i), " ")
txtHistory.Value = History
stDocName = "Training Record Signature Sheet"
DoCmd.OpenReport stDocName, acNormal
ctl.Selected(i) = False 'clears the selection
WasAdded = True
History = 0
End If

Next i

If WasAdded Then

Exit_cmdClose_Click:
Exit Sub

Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click
End If

Exit_Here:
On Error Resume Next
rs.Close
Set rs = Nothing
Exit Sub

ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Here

End Sub


THANK YOU!

Titania

 

[strive4peace]

Hi Titania,

this is probably happening because a control you are referencing for a
value is not filled out ...

put one more line in your error handler...

'~~~~~~~~~~~~~~~~~~~~
ErrorHandler:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_Here
RESUME
'~~~~~~~~~~~~~~~~~~~~~

if you get an error, press CTRL-BREAK when the message box pops up,
Enter to dismiss the dialog box

this takes you into the code

right-click on the *Resume* statement
from the shortcut menu, choose --> Set Next Statement

then press F8 to resume with the statement that caused the problem --
you can fix it!
pressing F8 executes one statement at a time

press F5 to continue execution automatically

~~~
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Resume goes back to
the offending line. When code Stops, press F8 to execute one statement
at a time.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*