ro******@alltel.net wrote in
news:11*********************@f14g2000cwb.googlegro ups.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 rstAutoPayTo999Detail As Recordset
Set dbsCreditCard = CurrentDb()
Set rst999 = dbsCreditCard.OpenRecordset("999",
dbOpenDynaset) Set rst999Detail =
dbsCreditCard.OpenRecordset("999Detail",
dbOpenDynaset)
Set rstAutoPayTo999 = dbsCreditCard.OpenRecordset("AutoPay
To
999", dbOpenDynaset)
Set rstAutoPay999Detail =
dbsCreditCard.OpenRecordset("AutoPay
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 rstAutoPayTo999Detail
Do Until .EOF '********** ERRORS HERE ***********
rst999Detail.AddNew
rst999Detail!Pro = rstAutoPayTo999Detail!Pro
rst999Detail!Amount = rstAutoPayTo999Detail!Amount
'rst999detail!999Pro = rst999!999pro
'rst999detail!999ProT = rst999!999pro
rst999Detail.Update
.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 
