object variable or with block variable not set

[ronvhill]

I am encountering an error on the following code. Basically, I have
two queries which I am using to enter new records into two other
tables, one a master and the other a child.

Function Macro3()
On Error GoTo Macro3_Err

Dim dbsCreditCard As Database
Dim rst999 As Recordset
Dim rst999Detail As Recordset
Dim rstAutoPayTo999 As Recordset
Dim rstAutoPayTo999 Detail As Recordset

Set dbsCreditCard = CurrentDb()
Set rst999 = dbsCreditCard.O penRecordset("9 99", dbOpenDynaset)
Set rst999Detail = dbsCreditCard.O penRecordset("9 99Detail",
dbOpenDynaset)
Set rstAutoPayTo999 = dbsCreditCard.O penRecordset("A utoPay To
999", dbOpenDynaset)
Set rstAutoPay999De tail = dbsCreditCard.O penRecordset("A utoPay
To 999Detail", dbOpenDynaset)

With rstAutoPayTo999
Do Until .EOF
rst999.AddNew
rst999!Date = rstAutoPayTo999 !ReportDate
rst999!userid = "autopay"
rst999.Update

With rstAutoPayTo999 Detail
Do Until .EOF '********** ERRORS HERE ***********
rst999Detail.Ad dNew
rst999Detail!Pr o = rstAutoPayTo999 Detail!Pro
rst999Detail!Am ount = rstAutoPayTo999 Detail!Amount
'rst999detail!9 99Pro = rst999!999pro
'rst999detail!9 99ProT = rst999!999pro
rst999Detail.Up date
.MoveNext
Loop
End With

.MoveNext
Loop
End With

Macro3_Exit:
Exit Function

Macro3_Err:
MsgBox Error$
Resume Macro3_Exit

End Function
I am guessing it is a "Set" issue of some sort, but cannot seem to
figure it out. Any help would be great!

Thanks

Ron

[David W. Fenton]

ro******@alltel .net wrote in
news:11******** *************@f 14g2000cwb.goog legroups.com:

I am encountering an error on the following code. Basically, I
have two queries which I am using to enter new records into two
other tables, one a master and the other a child.
First off, it looks to me like you could be doing the insert with a
SQL statement, instead of needing to open a recordset to do it.

Secondly, I

Function Macro3()
On Error GoTo Macro3_Err

Dim dbsCreditCard As Database
Dim rst999 As Recordset
Dim rst999Detail As Recordset
Dim rstAutoPayTo999 As Recordset
Dim rstAutoPayTo999 Detail As Recordset

Set dbsCreditCard = CurrentDb()
Set rst999 = dbsCreditCard.O penRecordset("9 99",
dbOpenDynaset) Set rst999Detail =
dbsCreditCard.O penRecordset("9 99Detail",
dbOpenDynaset)
Set rstAutoPayTo999 = dbsCreditCard.O penRecordset("A utoPay
To
999", dbOpenDynaset)
Set rstAutoPay999De tail =
dbsCreditCard.O penRecordset("A utoPay
To 999Detail", dbOpenDynaset)

With rstAutoPayTo999
Seems to me that right here you need these lines:
If .RecordCount <> 0 Then
.MoveFirst

And then the rest of your code should be inside that If/Then/Else
structure, with an End If at the end.

Two things: if there are no records, you don't want to do anything.

And you can't assume that the current record will be the first
record of your recordset.

Also, I don't think it's a very good idea to nest WITH blocks. Your
outer WITH block is really only used once (for the .EOF). In fact,
that may be the reason you're having the problem. How is the code
supposed to know for certain which parent With it belongs with?
Do Until .EOF
rst999.AddNew
rst999!Date = rstAutoPayTo999 !ReportDate
rst999!userid = "autopay"
rst999.Update

With rstAutoPayTo999 Detail
Do Until .EOF '********** ERRORS HERE ***********
rst999Detail.Ad dNew
rst999Detail!Pr o = rstAutoPayTo999 Detail!Pro
rst999Detail!Am ount = rstAutoPayTo999 Detail!Amount
'rst999detail!9 99Pro = rst999!999pro
'rst999detail!9 99ProT = rst999!999pro
rst999Detail.Up date
.MoveNext
Loop
End With

.MoveNext
Loop
End With

Macro3_Exit:
You should clean up all your variables here, with a codeblock like
this for each of your recordset variables:

If Not (rst999 Is Nothing) Then
rst999.Close
Set rst999 = Nothing
End If

And last of all:

Set dbsCreditCard = Nothing

(there's no need to close dbsCreditCard because you can't, as it's
pointing to the current database).
Exit Function

Macro3_Err:
MsgBox Error$
Resume Macro3_Exit

End Function

I don't understand what you're doing here.

It looks like:

For each record in [AutoPay To 999] you are adding a record to
[999].

As well, for each record in [AutoPay To 999] you are adding a record
for each of the records in [AutoPay To 999Detail], using values
drawn from [999Detail].

But what is not at all clear is what the links are between the data
in the source table and in the tables you're adding the data to. I
would expect to see some ID number that joins the detail records to
their parent, but I seen nothing in your code.

So, I can't help much, because I don't really understand what you're
trying to accomplish.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc