By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Populating one field in a table with one to many relation

P: n/a
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

Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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 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


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

Nov 13 '05 #2

P: n/a
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 strMyUpdateQuery

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.