Need to Loop through Subform records...

[ThriftyFinanceGirl]

I need to check each record in a subform and create records if none exist.
Below is my code with comments where I know I need to create the Loop but I'm
not really sure how. I just haven't ever had to create records en masse' so
I've just never learned how! Guess it's time!
=====================================
'Now check each record to see if the LocationObjects exist
MsgBox "Checking for LocationObjects"
Application.Echo False
Me.qryWorkWithTax_subform.Form!TaxPercentage.SetFocus

If DCount("Taxable", "qryCheckLocObj") < 1 Then
'there are no LocationObjects for this tax
'create them
MsgBox "there are no LocationObjects for Tax " &
Me.qryWorkWithTax_subform.Form!TaxID
DoCmd.OpenQuery "qryAddNewLocationObjects"

'check the next record

Else
'there are already LocationObjects for this tax Move Next and
'check the next record

End If
'Application.Echo True

 

[Jeff Boyce]

Are you trying to add (empty) placeholder records in a "child" table? If
so, why?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ThriftyFinanceGirl]

No, I have already taken the user through a process to create a group of
taxtype records (ex. State Tax, California, 10 locations), now I need to use
the NEW taxes and create LocationObjects for each new tax in the subform.
The code I have is checking the FIRST record in the subform to see if it is a
new tax or not (ie. if it has no LocationObjects it is new) and, if it is
new, create the LocationObjects. Then move to the next record in the subform
and repeat the procedure.

 

[Piet Linden]

No, I have already taken the user through a process to create a group of
taxtype records (ex. State Tax, California, 10 locations), now I need to use
the NEW taxes and create LocationObjects for each new tax in the subform. 
The code I have is checking the FIRST record in the subform to see if it is a
new tax or not (ie. if it has no LocationObjects it is new) and, if it is
new, create the LocationObjects.  Then move to the next record in the subform
and repeat the procedure.

I would grab the Foreign Key value from the form, and then create a
deliberate (but filtered) Cartesian Product and then convert it to an
append query,. Then you can use

CurrentDb.Execute "myCPAppendQuery", dbFailOnError

to create all the records at once.

 

[ThriftyFinanceGirl]

I think I understand your concept but still don't know how to do it. I need
to create related records for all the records on the form that don't already
have the related records in place. So it will be a group of records. --
With your concept of using the append qry(which I already have in place) that
is fine, but I need to know how to continue to move through the subform,
checking each record.

 

[ThriftyFinanceGirl]

Well I've gotten this far, but no luck in 'grabbing' the Tax ID number as I
move through the recordset. Can someone please help me modify this code?

------------------------------------------------------------------
Dim rst As Recordset
Dim intTaxID As Integer

Set rst = Forms!frmworkwithtax.RecordsetClone

Do While Not rst.EOF ' Iterate through each element.
intTaxID = rst.FindNext
If DCount("Taxable", "qryCheckLocObj") < 1 Then
'there are no LocationObjects for this tax
'create them
MsgBox "there are no LocationObjects for Tax " &
Forms!frmworkwithtax.TaxID
DoCmd.OpenQuery "qryAddNewLocationObjects"
'Exit For ' Exit loop.
'check the next record
rst.MoveNext
Else
'there are LocationObjects for this tax
'check the next record
rst.MoveNext
End If
Loop
rst.Close
-----------------------------------------------------------------

 

[Jonathan]

Hi ThriftyFinanceGirl,

'simply create the recordset as you have done
Set rst = Forms!frmworkwithtax.RecordsetClone
'loop all subform records
do until rst.eof

rst.movenext
loop

I cannot see a reason for the rst.findnext line.

'grab the tax id using field in recordset.
intTaxID=rst!TaxID

I am glad that in NZ we have a simple tax system. It winds me up whenever I
go shopping in USA... I can't understand why prices are not displayed tax
inclusive.

Luck
Jonathan

 

[ThriftyFinanceGirl]

Ha! Wouldn't that be nice! Believe me the tax stuff winds me up too! It is
often a nightmare and different for each state, county and city! Thanks for
the tips, I think I've got what I need now!