puppygirl3939
Technical User
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
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