Populating one field in a table with one to many relation

[hasanainf]

Hi,

I have a main table named tblCrPurchase2 with following fields
CrPurchase2ID - key field
InvoiceDate
InvoiceNumber
PF1

I have a child table called tblCrPurchase2D etail (this is the many side
of the table) with following fields
CrPurchase2Deta ilID - Key field
CrPurchase2ID - for linking with tblCrPurchase2 table
ProdDesc
PurPr2
PurQty2
PF2

Figure 1
Data for tblCrPurchase2 is:
CrPurchase2ID InvoiceDate InvoiceNumber PF1
1 7/1/2005 1245 0.10
2 7/5/2005 32154 0.25

Figure 2
Data for tblCrPurchase2D etail is
CrPurchase2ID CrPurchase2Deta il ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0
1 2 prod2 5.35 0
1 3 prod3 3.65 0
2 4 prod1 1.35 0
2 5 prod2 5.00 0
2 6 prod3 3.65 0

I want to update the first three records with PF2 = 0.10 and the last
three records with PF2 = 0.25 so end result would look like this
Figure 3
CrPurchase2ID CrPurchase2Deta il ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0.10
1 2 prod2 5.35 0.10
1 3 prod3 3.65 0.10
2 4 prod1 1.35 0.25
2 5 prod2 5.00 0.25
2 6 prod3 3.65 0.25

By using a command button I put the following code on click event

Private Sub cmdMakechanges_ Click()
Dim db As Database
Dim rs As Recordset
Dim FirstPF As Double

Set db = CurrentDb

Set rs = db.OpenRecordse t("SELECT * FROM tblCrPurchase2 INNER JOIN
tblCrPurchase2D etail ON tblCrPurchase2. CrPurchase2ID =
tblCrPurchase2D etail.CrPurchas e2ID", dbOpenDynaset)

FirstPF = rs!PF1

rs.MoveFirst
Do While Not rs.EOF
rs.Edit
rs!PF2 = FirstPF
rs.Update
rs.MoveNext
Loop

rs.Close

End Sub
Figure 4
After running this code my query get populated as follows
CrPurchase2ID CrPurchase2Deta il ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0.10
1 2 prod2 5.35 0.10
1 3 prod3 3.65 0.10
2 4 prod1 1.35 0.10
2 5 prod2 5.00 0.10
2 6 prod3 3.65 0.10

What I really want done is if I am at nth record of main table than the
code should pull up the data from many side of the table that relates
with one side of the table and populate PF2 with whatever is in PF1.
For example if I am at record 2 of main table with PF1 = 0.25 than it
should populate PF2 = 0.25 in many side of the table in only three
records. I.e record 4,5, and 6. because record 4,5, and 6 in many side
of table is related to record 2 on one side of table as the table shows

Figure 5
CrPurchase2ID CrPurchase2Deta il ProdDesc PurPr2 PF2
2 4 prod1 1.35 0.25
2 5 prod2 5.00 0.25
2 6 prod3 3.65 0.25
I hope I have made myself clear.

How do I code to get the results as shown?

Thanking you all in advance

[Bas Cost Budde]

1) do you need to save a value that you can derive from existing data?
2) can you use an update query instead of code? Most of the times this
is more reliable and executes (way) faster

ha*******@yahoo .com wrote:

I want to update the first three records with PF2 = 0.10 and the last
three records with PF2 = 0.25 so end result would look like this
Figure 3
CrPurchase2ID CrPurchase2Deta il ProdDesc PurPr2 PF2
1 1 Prod1 1.56 0.10
1 2 prod2 5.35 0.10
1 3 prod3 3.65 0.10
2 4 prod1 1.35 0.25
2 5 prod2 5.00 0.25
2 6 prod3 3.65 0.25

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html

[BillCo]

Do you really need to do it in code? You could just run an update
query:

UPDATE tblCrPurchase2
SET ProdDesc = 0.10
WHERE CrPurchase2ID = 1

.... actually, you could dynamically create the query in code and
execute it there if you need to - probably easier (and faster running)
than messing with recordsets.

docmd.runsql strMyUpdateQuer y