Updating table with a form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have a table that is set up to have repeating values in a field. For
instance,

BOL# Order # Appt Date Appt Time
12345 144
12345 562
12345 777
99999 145
87547 555

Appt Date and Appt Time are blank.

One BOL# could have multiple Order #'s and appear on multiple rows.

In a form, I want to be able to update the appt date and appt time (in the
table) for one BOL#, without having to scroll through and update all three
records (which are associated with each order #). Is this even possible to
update multiple records based off of one repeating value?

Thanks for any help!
Chuck
 
Chuck said:
Hi,
I have a table that is set up to have repeating values in a field.
For instance,

BOL# Order # Appt Date Appt Time
12345 144
12345 562
12345 777
99999 145
87547 555

Appt Date and Appt Time are blank.

One BOL# could have multiple Order #'s and appear on multiple rows.

In a form, I want to be able to update the appt date and appt time
(in the table) for one BOL#, without having to scroll through and
update all three records (which are associated with each order #).
Is this even possible to update multiple records based off of one
repeating value?

Thanks for any help!
Chuck
Click to expand...

You should have more tables. One with BOL# and Order# (entered once) and another
with the other fields (one to many relationship).

Then you enter the stuff on the one side once.
 
On Fri, 17 Mar 2006 14:31:16 -0800, Chuck Neal <Chuck
Hi,
I have a table that is set up to have repeating values in a field. For
instance,

BOL# Order # Appt Date Appt Time
12345 144
12345 562
12345 777
99999 145
87547 555

Appt Date and Appt Time are blank.

One BOL# could have multiple Order #'s and appear on multiple rows.

In a form, I want to be able to update the appt date and appt time (in the
table) for one BOL#, without having to scroll through and update all three
records (which are associated with each order #). Is this even possible to
update multiple records based off of one repeating value?
Click to expand...

If each BOL# corresponds to multiple Order#, and the Appt Date and
Time pertain to the BOL# and not to an Order, then I would really
suggest that you normalize your data into two tables: a table of BOL
related one to many to a table of Orders. You could use a Form based
on the BOL table (with the date fields) and a Subform based on Orders.

A couple of other suggestions: it's best not to use special characters
such as blanks or (especially) # in fieldnames; and Access date/time
values comfortably store both date and time in the same field, and
date calculations are much easier if you use a single appointment
date/time field rather than two separate fields which must be combined
for sorting or searching.

If for some reason you're stuck with the current non-normal design,
you can run an Update query using the BOL# as a criterion to update
all rows with that BOL#.

John W. Vinson[MVP]
 
John,
Thanks for the feedback. Given the nature of this database, I think your
last suggestion is going to be the way to go. Unfortunately, I cannot split
the table. I'll try to use the query you suggested. Thanks again!
 
John,
Thanks for the feedback. Given the nature of this database, I think your
last suggestion is going to be the way to go. Unfortunately, I cannot split
the table. I'll try to use the query you suggested. Thanks again!
Click to expand...

Given that you could create a Query which looks, works, feels and acts
exactly like the non-normalized table (and even give it the same name
so that it would work in all existing forms and queries), I'm not sure
why you insist that you must stick with an incorrect design... but
that's your choice!

John W. Vinson[MVP]
 
John,
Since this was posted, I've gone to an Access class. I understand what you
were saying now and my database works perfect with the new design. Sorry for
the confusion...I was completely Access stupid before, but I'm getting better.
Chuck
 
Back
Top