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

Update All Records in Subform

P: n/a
I have created a purchase order database that has a PO form with PO
Details in the subform. I need to assign each PO a job number and the
PO details subform also has a job number field (for easy of tracking
elsewhere). I have a problem where if I change the job number on the
main area of the form I need it to update ALL of the line items in the
sub form. I can get it to update the line item that currently has
focus but not the others.

Thank you for any help that you can offer.

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


P: n/a
I'd suggest that you create a relationship between the JobNumber field in
your PO table and the JobNumber field in your Details table. Enforce
relational integrity with cascade updates. Now whenever you change the
JobNumber in a record in your PO table, all related records in your Details
table will change automagically.

HTH
- Turtle

"John" <al*****@hotmail.com> wrote in message
news:bc**************************@posting.google.c om...
I have created a purchase order database that has a PO form with PO
Details in the subform. I need to assign each PO a job number and the
PO details subform also has a job number field (for easy of tracking
elsewhere). I have a problem where if I change the job number on the
main area of the form I need it to update ALL of the line items in the
sub form. I can get it to update the line item that currently has
focus but not the others.

Thank you for any help that you can offer.

ALWAZUP

Nov 13 '05 #2

P: n/a
al*****@hotmail.com (John) wrote in message news:<bc**************************@posting.google. com>...
I have created a purchase order database that has a PO form with PO
Details in the subform. I need to assign each PO a job number and the
PO details subform also has a job number field (for easy of tracking
elsewhere). I have a problem where if I change the job number on the
main area of the form I need it to update ALL of the line items in the
sub form. I can get it to update the line item that currently has
focus but not the others.

Thank you for any help that you can offer.

ALWAZUP


Easiest way is to create a relationship betweeen the PO table and the
PO Details table and then just make sure the Cascading Updates
checkbox is checked.
Nov 13 '05 #3

P: n/a
Why don't you remove the job number field from the detail table, then use a
query to get the job number from the PO table?

"John" <al*****@hotmail.com> wrote in message
news:bc**************************@posting.google.c om...
I have created a purchase order database that has a PO form with PO
Details in the subform. I need to assign each PO a job number and the
PO details subform also has a job number field (for easy of tracking
elsewhere). I have a problem where if I change the job number on the
main area of the form I need it to update ALL of the line items in the
sub form. I can get it to update the line item that currently has
focus but not the others.

Thank you for any help that you can offer.

ALWAZUP

Nov 13 '05 #4

P: n/a
I have tried to setup a relationship but it gives me an error of no
unique index found. These two forms are already linked by the PO
Number. Thanks for all of the responses so far.
Nov 13 '05 #5

P: n/a
If you have a unique job number for each PO number, you should be able to
create a unique index on the JobNumber field in the PO table.
"John" <al*****@hotmail.com> wrote in message
news:bc**************************@posting.google.c om...
I have tried to setup a relationship but it gives me an error of no
unique index found. These two forms are already linked by the PO
Number. Thanks for all of the responses so far.

Nov 13 '05 #6

P: n/a
I can't do that though. There will be multiple PO's for each job number.
Nov 13 '05 #7

P: n/a
Guess you've run into the main reason we normalize data in Access.
The job number in your detail record is redundant, and keeping it in sync
with the parent record will always be a hassle.

As far as tracking this elsewhere, just use a query which pulls the
appropriate job number from the PO table when you need it. One approach is
to write a query which returns all of your detail information, plus the job
number (and anything else you frequently need from the parent record) and
use this anywhere you'd otherwise use your Detail table.

HTH
- Turtle

"John" <al*****@hotmail.com> wrote in message
news:bc**************************@posting.google.c om...
I can't do that though. There will be multiple PO's for each job number.

Nov 13 '05 #8

P: n/a
You can put an Index on the JOB field that allows duplicates.

"John" <al*****@hotmail.com> wrote in message
news:bc**************************@posting.google.c om...
I can't do that though. There will be multiple PO's for each job number.

Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.