Add multiple records to a subform/table based on user input

[puppygirl3939]

I have been working on this for two days!!! Based on what the user selects on a combo box on the main form I want the system to add the matching tasks to a subform/table. This is the code that I am using and I am getting the following error: Run time error 3704 Operation is not allowed when the object is closed
Any ideas?

Example

If the user selects "Advertising" from the the job type field automatically add 5 new records to the tbl_jobtask table which are Creative, Creative/Design Revisions, Copy Changes, Proofs, Production and refresh the form.

'1st Recordset "Look for the proper Task"
Dim rstask As New ADODB.Recordset
Dim Sql1 As String

Sql1 = "Select ([TaskID]) from tbl_list_job_spec
Where [JobTypeID] = " & Me!JobTypeID
rstask.Open Sql1, CurrentProject.Connection
Do While Not rstask.EOF

'2nd Recordset "Check to see if something already exist"
Dim CurConn1 As ADODB.Connection
Dim rstcheck As New ADODB.Recordset
Dim tblCheck As New ADODB.Recordset
Set CurConn1 = CurrentProject.Connection
Set tblCheck.ActiveConnection = CurConn1
Dim Sql2 As String
Dim CheckCount As Integer

tblCheck.Source = "tbl_jobtask"

Sql2 = "SELECT Count([JobTypeID]) As CheckCount FROM tbl_jobtask where [TicketID] = " & Me!TicketID & " And [JobTypeID] = " & Me!JobTypeID
rstcheck.Open Sql2, CurrentProject.Connection
If rstcheck("CheckCount") >= 1 Then

'3rd Recordset "Adds the regions"

Dim CurConn2 As ADODB.Connection
Dim rstAdd As New ADODB.Recordset
Dim tblAdd As New ADODB.Recordset

Set CurConn2 = CurrentProject.Connection
Set tblAdd.ActiveConnection = CurConn2

tblAdd.Source = "tbl_jobtask"

tblAdd.CursorType = adOpenKeyset
tblAdd.LockType = adLockOptimistic
tblAdd.Source = "tbl_jobtask"
tblAdd.Open Options:=adCmdTable

With tblAdd
.AddNew
.Fields("TicketID") = Me.TicketID
.Fields("JobTypeID") = Me.JobTypeID
.Fields("TaskID") = rstask("TaskID")
.Update
End With
Set tblAdd = Nothing
rstcheck.Close
Set rstcheck = Nothing

tblAdd.MoveNext
End If
Loop

 

[PHV]

Two notes:
1) Try to better indent your code
2) rstcheck should be closed after the End If

One question:
Which line is highlighted when in debug mode ?

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

 

[puppygirl3939]

The line that is hightlighted is "tblAdd.MoveNext" I moved the End If after the End With and I still get the same error message.

 

[PHV]

End With
Set tblAdd = Nothing
End If
rstcheck.Close
Set rstcheck = Nothing
rstask.MoveNext
Loop

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