G
Guest
Thanks, Doug and Mike for your suggestions the other day, you did put me on
the right track. It's still not behaving, so I have a new question..... my
apologies for not being clear previously. I want to loop through a
recordset, copy one of the field values, then create a new record and paste
this value into one of the fields. I need the new record to have a different
key field value, so the SQL way to append it doesn't seem to me like it would
work. Here's my revised code:
Public Sub NewContractYear(strRecSource1 As String, strRecSource2 As String,
strCtl1 As String, strCtl2 As String, strCtl3 As String, strCtl4 As String)
On Error Resume Next
Dim db As DAO.Database
Dim rst1 As Variable
Dim rst2 As Variable
Dim strValue As String
Set db = DBEngine(0)(0)
Set rst1 = db.OpenRecordset(strRecSource1)
Set rst2 = db.OpenRecordset(strRecSource2)
If rst1.EOF And rst1.BOF = True Then
Exit Sub
End If
While Not rst1.EOF
strValue = rst1(strCtl1).Value
With rst2
.AddNew
rst2(strCtl2).Value = ContractNumber
rst2(strCtl3).Value = strCYCode
rst2(strCtl4).Value = strValue
Stepping through, moving my cursor over these lines just above (rst) gives
me the error message: Object Variable of With Block Variable not set. It
appears to me to be set. The string control names are recognized. Again,
it's probably obvious to you what's wrong. Here's the rest:
.Update
End With
Wend
rst1.MoveNext
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing
End Sub
Then called from a button click:
Call NewContractYear("Current Agency Contact",
"Crossreference:ContractAgencyContact", "AC_Code", "Contract_Number",
"CYCode", "AC_Code")
Thanks in advance for all your help. - Anne
the right track. It's still not behaving, so I have a new question..... my
apologies for not being clear previously. I want to loop through a
recordset, copy one of the field values, then create a new record and paste
this value into one of the fields. I need the new record to have a different
key field value, so the SQL way to append it doesn't seem to me like it would
work. Here's my revised code:
Public Sub NewContractYear(strRecSource1 As String, strRecSource2 As String,
strCtl1 As String, strCtl2 As String, strCtl3 As String, strCtl4 As String)
On Error Resume Next
Dim db As DAO.Database
Dim rst1 As Variable
Dim rst2 As Variable
Dim strValue As String
Set db = DBEngine(0)(0)
Set rst1 = db.OpenRecordset(strRecSource1)
Set rst2 = db.OpenRecordset(strRecSource2)
If rst1.EOF And rst1.BOF = True Then
Exit Sub
End If
While Not rst1.EOF
strValue = rst1(strCtl1).Value
With rst2
.AddNew
rst2(strCtl2).Value = ContractNumber
rst2(strCtl3).Value = strCYCode
rst2(strCtl4).Value = strValue
Stepping through, moving my cursor over these lines just above (rst) gives
me the error message: Object Variable of With Block Variable not set. It
appears to me to be set. The string control names are recognized. Again,
it's probably obvious to you what's wrong. Here's the rest:
.Update
End With
Wend
rst1.MoveNext
Set rst1 = Nothing
Set rst2 = Nothing
Set db = Nothing
End Sub
Then called from a button click:
Call NewContractYear("Current Agency Contact",
"Crossreference:ContractAgencyContact", "AC_Code", "Contract_Number",
"CYCode", "AC_Code")
Thanks in advance for all your help. - Anne