Need loop help, add records by field that match query records

[Todd H]

Thanks for all the help so far, this forum is great.

I have one other function that I need help with. I have a tblPayments
that stores the actual and projected costs by cost code and billing
period; the billing periods are pulled from a separate
tblBillingPeriod. I have a master/sub form based on tblBillingPeriod/
tblPayments that I need to create function that will add records to
tblPayments based on the codes that match a separate qryBudget.

I haven't done much with loops, but here are the fields we are dealing
with:

qryBudget.CostCode (text that I need to create a tblPayment record for
each record)

tblPayments
CostCode (text); need to match each record in qryBudget
BillingPeriodNo (number); need to match tblPayment.BillingPeriodNo
(master form)
ActualCost (currency); set to 0
ProjectedCost (currency); set to 0

tblBillingPeriod.BillingPeriodNo (this is is the field that I want all
the tblPayment.BillingPeriodNo to equal)

I want to assign this to a command button.

Any help is appreciated.

Thanks, Todd

 

[Todd H]

This is what I have come up with so far:

For Each CostCode In qryBudget
DoCmd.RunSQL "INSERT INTO tblPayments (CostCode,ActualCost,
ProjectedCost, BillingPeriodNo, Contract) VALUES ('" &
qryBudget.CostCode & "', 0, 0,Me.BillingPeriodNo,'N/A');"
Next CostCode


But this gives me a type mismatch and expected variable errors.

I would appreciate any help.

Thanks,

Todd

 

[Access Developer]

Better read the VBA Help on For Each... it deals with members of a
collection. A Query isn't a Collection.

If you want to do this with code, you'll need to open a Recordset on
qryBudget to get the Records, or use the Form's and Subform's Recordset or
RecordsetClone, and then process those Records from which you will use the
CostCode values

But, likely a better way is to use multiple tables/queries to create an
Append Query .

To what "thing" do the tblPayment Records apply? Is it the "contract" shown
in the SQL, for which you seem to have supplied a value of "N/A" -- an
unorthodox approach, at best, to identifying something.

 

[Todd H]

the tblPayment records relate to qryBudget by CostCode. The Contract
could be another way to relate the records, but for now the
qryBudget.CostCode is the basis of the new records; one
tblPayments.CostCode per each qryBudget.CostCode. Then I want the
BillingPeriodNo to be based off the current record in the form. I
want to set the values of the ActualCost, ProjectedCost and Contract
to 0,0,'N/A' respectively...

Is there a way to define the recordset based off the query in the
expression? This query needed is not part of the form.

I am not familar with append queries...but I am willing to try
anything.

Thanks for the help.