How do you pass data to a new record from an existing record?

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

Guest

I am using Access 2000, and what I would like to do is pass data previously
entered in the table to a new record. I have a field "ID" (primary key) ;
when the users enter an "ID" that is already in the table I would like to
pass data from three fields data "name" "info" "location". What would be the
best way to go about doing this? Is there some code out there that I could
modify to make this work? (I am not a programmer but i could work my way
through it if there is some avail.)
 
If I understand correctly, my answer would be "DON'T!"

If you have three fields, "name", "info", "location" stored once in your
relational database, you ONLY need the ID from that record to reference
these fields. You do not need to store the same information again.

By the way, Access considers the word "name" to be reserved -- if you use it
as a field name, you will only confuse both Access and yourself. "Date" is
another reserved word (check Access HELP for "reserved" words).
 
Well I need that information to show up on the form. Sounds weird but I
actually need to store that information more than once. So I need to know how
I could get the information to automatically show up once they enter the ID.

The names of the fields are not actually "name", "info" and "address" I just
used those names as a generic reference so it would be easy to understand
what I'm trying to do. So would you know you know how to make this possible?
 
If you haven't spent time learning about relational databases and
normalization, I can understand how you might think you need to store the
same facts more than once (spreadsheets come to mind!).

It is not only possible, but desirable, in relational databases, to use
queries to re-associate the "information to show up on the form", without
having data duplicated.

There may be/are rare instances when data would be stored twice, but nothing
you've described yet argues for it.

If you have a main table and a "supporting" table (e.g., Orders, with
Customers), you wouldn't need to re-store customer name, address, ... in the
Order table. Instead, create a query that includes Orders and Customers,
joined on CustomerID (you'd use the CustomerID in the Order table to show
who placed the order). Select the fields you need to show.

Now base your form on the query.
 
So when users enter an ID that is already in the table how could I get the
other fields to be populated without having to re-enter that data that's
already in the table. The reason why I need it to show up on the form is b/c
there is a print button on the form so when the users are finished they can
print out the form if needed.
 
A couple issues:

First, if you use an unbound combobox in the header of your form, you can
use this to select a person (who can remember ID#s?!), then, in the
AfterUpdate event of that combobox, have the form load the record.

Second, forms are meant for on-screen data add/edit/display. Printing a
form is ... poor form <g>.

Instead, create a report definition that will show what you want, then add a
button to your form that lets you print that report. You can add syntax to
the code behind that button that let's you limit the report to only print
the record you are displaying on the form.
 
Back
Top