How to update all records in a subfrom

  • Thread starter Thread starter helmer horta via AccessMonster.com
  • Start date Start date
H

helmer horta via AccessMonster.com

Hi, this drive me crazy guys.

PLEASE HELP ME.

I have a Main Form (Using Table BOOKS "It show me the book info"), and in the
same form I have a Subform (Using the table TICKETS).

Wen I select the Book number automatically show me in the subform the Tickets
information (Number, price, etc). It work Perfect.

QUESTION: Now I need to change in the book the person name asigned for this
book, but as well I need to change the same person name to each ticket
automatically. (each book have 50 or 40 tickets)

I don't want to change the one by one, with the new information.

Is any way to do it using a code??????


Example:

(BOOK TABLE)
Book: 1 Name: Mr. Peter Address: 425 east 138 st

(TICKET TABLE)
Book: 1 Ticket: 0001 Name: Mr. Peter
Book: 2 Ticket: 0002 Name: Mr. Peter
Book: 3 Ticket: 0003 Name: Mr. Peter
Book: 4 Ticket: 0004 Name: Mr. Peter
......
......
Book: n Ticket: 000n Name: Mr. Peter


That is what I need to do

Thank you
 
Typically, you would not store that duplicate information in the related
table.

What is a "ticket"?

Why do you have the name storeed in the Ticket table? Just store it in the
Book table.

Storing it in the ticket table would make sense if you wanted to track
historical data, but since you are changing all of them, it would appear you
don't.

For example, you would store a books price in a SALES table if you wanted to
track the price at the time of each sale. If the price increased, you would
not go back and update all the historical sales. This is a valid reason to
store it in the related table.
 
Ok, but i need the vendor name in each ticket (Those tickets are bus prepaid
Fares)

I need in the Ticket (Table) the vendor info as well in the Book (Table) I
need the ticket vendor info

I have a Main screen using the Book Table (Records), and when i select a book
I can change the assign vendor name info for this book.
But I need to automatically change the vendor name info in the Tickets table
as well.

But I dont'n know how to do it. (using CODE)

Thank you
 
Then include both tables in any reports, forms, or queries you use. That is
the whole point of a RELATIONAL database. You store common items one time
in a separate table then pull that RELATED information to your forms queries
and reports.

You DON'T need the vendor name in both tables. Your "tickets" table is
relateed to your "book" table. REMOVE THE VENDOR NAME FROM YOUR TICKETS
TABLE.

Here...


BOOK TABLE DATA: BOOKKEY VENDOR NAME: John Doe Blah, Blah, Blah

TICKET TABLE DATA: BOOKKEY DATE TIME Blah Blah Blah


So you'd get....


12345 John Doe 123 Main street
01/01/05 $5.00
02/04/05 $6.00
03/05/05 $7.00

12589 Sue Johnson 555 Elm Street
02/11/05 $5.00
03/06/05 $3.00
04/21/05 $7.00


The date records above (in your ticket table) are all linked to the Book
number (12345 or 12589) so they know who the vendor is.
 
If you need more help, look at the Northwinds sample database that ships
with Access (or any of the templates for that matter) and notice that Vendor
data is stored in one table. In the invoice table, there is a vendor
number, but not all the other vendor data (it is related). Notice the
invoice details contains the line items for each invoice and relates to the
invoice master table, but there is no mention of the vendor or the vendor
name.

Access is not a spreadsheet. You don't ever repeat data in a record that
can be found in another table and linked.
 
Back
Top