Hi,
I have a main table named tblCrPurchase2 with following fields
CrPurchase2ID - key field
InvoiceDate
InvoiceNumber
PF1
I have a child table called tblCrPurchase2Detail (this is the many side
of the table) with following fields
CrPurchase2DetailID - 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 tblCrPurchase2Detail is
CrPurchase2ID CrPurchase2Detail 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 CrPurchase2Detail 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.OpenRecordset("SELECT * FROM tblCrPurchase2 INNER JOIN
tblCrPurchase2Detail ON tblCrPurchase2.CrPurchase2ID =
tblCrPurchase2Detail.CrPurchase2ID", 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 CrPurchase2Detail 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 CrPurchase2Detail 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