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

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
*




TitaniaTiO2 wrote:
> 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