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

 

[PieterLinden via AccessMonster.com]

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.
[quoted text clipped - 48 lines]

to create all the records at once.

Here's the basic idea. Say you have two tables, "Person" and "Committee",
and you want to create a dataset of all possible combinations of the two.
Say Person has 3 records and Committee has 2. Your goal is to create 3 x 2
records. The way to do it is like this...

SELECT Person.PersonID, Committee.CommitteeID
FROM Person, Committee;

note the intentionally missing join operator (INNER JOIN).

You can filter the sets pretty easily...

SELECT Person.PersonID, Committee.CommitteeID
FROM Person, Committee
WHERE Person.PersonID IN (1,2)
AND Committee.CommitteeName LIKE 'A*';

so you'd get all people whose ID is either 1 or 2 and all committees with
names beginning with A.

Once you have that result, you need to use the find unmatched query to
eliminate all the records from that set that appear in the destination table.

SELECT Person.PersonID, Committee.CommitteeID
FROM qryPersonComm AS pc LEFT JOIN ExistingTable AS et ON pc.PersonID=et.
PersonID AND pc.CommitteeID = et.CommitteeID
WHERE et.PersonID IS NULL

This should return all the results that are in one result set
(PersonCommittee) but not in the other. Then turn that into an append query,
and you'll only add the records that don't already exist in the destination
table.

You shouldn't have to loop at all. SQL is a set based language, so use sets
instead. If you describe the sets correctly and join them, you won't need
any looping or code at all. There's no reason you can't do this with plain
SQL. If you think about it, what are "subform records"? They're all the
records in tableB with a related record in Table A. (which you can identify
with an inner join or an EXISTS clause). That's what the LinkMaster (parent
table's unique key) and LinkChild (child table's foreign key) properties of a
subform do - they tell the database engine how the records are related, so it
shows only the related ones.

Hope this makes sense.

 

[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!