Update All Records in Subform

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

re: Update All Records in Subform

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" <alwazup@hotmail.com> wrote in message
news:bc146017.0408191522.31fa24d1@posting.google.c om...[color=blue]
> 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[/color]

re: Update All Records in Subform

alwazup@hotmail.com (John) wrote in message news:<bc146017.0408191522.31fa24d1@posting.google. com>...[color=blue]
> 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[/color]

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.

re: Update All Records in Subform

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" <alwazup@hotmail.com> wrote in message
news:bc146017.0408191522.31fa24d1@posting.google.c om...[color=blue]
> 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[/color]

re: Update All Records in Subform

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.

re: Update All Records in Subform

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" <alwazup@hotmail.com> wrote in message
news:bc146017.0408201417.53ffc2e7@posting.google.c om...[color=blue]
> 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.[/color]

re: Update All Records in Subform

I can't do that though. There will be multiple PO's for each job number.

re: Update All Records in Subform

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" <alwazup@hotmail.com> wrote in message
news:bc146017.0408211509.79a3a079@posting.google.c om...[color=blue]
> I can't do that though. There will be multiple PO's for each job number.[/color]

re: Update All Records in Subform

You can put an Index on the JOB field that allows duplicates.

"John" <alwazup@hotmail.com> wrote in message
news:bc146017.0408211509.79a3a079@posting.google.c om...[color=blue]
> I can't do that though. There will be multiple PO's for each job number.[/color]