filling in primekeys through forms

  • Thread starter iputanameherealready
  • Start date
I

iputanameherealready

I have a form that connects several tables together. The main table - say
table 1, has a prime key and that prime key is in all the other tables. When
I bring up the form, the 1st record appears. I want to put in a second
record and have the generated prime key of table 1 in all the associated
tables so that all the information will display when the information in
table1 is diesplayed. How is this accomplished? Is there a auto fill or
something to have this done. (I did not see this, thus the question)

Thank you.
 
B

BruceM

I really can't figure out what you are trying to do. Are the tables related?
Why do you want the same primary key (assuming that's what you mean by
"prime key" in all of the tables? If the tables are related the primary key
in Table1 could be the foreign key in the other tables, but I won't go too
far down that road without knowing what you are trying to do. To get a
helpful answer you should provide some specifics.
 
I

iputanameherealready

Bruce,

Thank you for your reply. Yes, the tables are related. I want the same
primary key in all the tables so there is a relationship between the tables.
You are correct, I used the wrong terminology - the correct terminology is
foreign key (which is the primary key of table1). How do I get the foreign
key filled with the value of the primary key of the main table, table1? I'm
not sure what other specifics you would need that would influence the answer.
If you could ask them, I will try to answer them.

Craig
 
B

BruceM

The short answer is that establishing relationships between the tables is how
a record in one table with a record or records in another. The best choice
(the only choice, really) for entering and working with data is a form, but
you need to start with tables and proper database design. It would help if
you could describe what you need to do, providing some specifics. For
instance, are you keeping track of payroll, inventory, sports teams, school
grades, or what? Without knowing that I would have to say something like
"suppose that the main table is for employees", when in fact you are trying
to keep track of spare parts for the Mars rover.
 
I

iputanameherealready

Bruce,

Ok. I am trying to build something in Access that will allow someone in a
hospital to keep track of patients, what type of things they made the visit
for and the medicine that was given out. This hospital is located in Ghana.
It is run by the missionaries. It is not a very big hospital, but it handles
about 1000 people a week. It is the only hospital for hundreds of miles
around.

I have the ‘main’ table called Name. The fields are as follows: Last Name,
First Name, Middle name, Miscellaneous, and Name_prime_key. The prime key is
called name_prime_key. This prime key will be in almost every table.
The next table is named Address. The fields are as follows:
Address_primary_key, address1, address2, Country and Name_primary_key.

The next table is named Contact. The fields are as follows:
contact_primary_key, Contact_number1, Contact_number2, contac3, and
Name_priamr_key.

The next table is named History. The fields are as follows:
History_primary_key, date_of_vist, Reason_fir_visit, Treatment, and
Name_primary_key.

The next table is medicine. The fields are as follows: Medicine_prime_key,
History_prime_key, and medicine_issued.

The first 4 tables are connected by the primary key in the Name table,
Name_prime_key. Since there is a 1 to many relationship for the history and
the medicine, there is no need for the name_primary_key to be there.

What I want to do is have the primary key fill in automatically when a new
record is added. It simply can NOT be that hard to do. Other products fill
it in automatically, or at worse there is a filed that you click on and then
it is filled in automatically. I am trying to find out where or what I have
to do in order to have the name_prime_key filled in automatically and then of
course the history_prime_key in the medicine table.

I am not sure that there is anything other information that I could provide
to you. However, if you ask for more detail, I will try to provide.

Your help is much appreciated.

Thank you.
 
B

BruceM

I assume that you have a separate address table because several people could
live at the same address. If that is the case I need to tell you that I am
unsure how best to proceed. The only thing I can think of is to store
AddressID in the name table, but with no relationship between the fields.
You would select the address from a combo box with its row source a query
based on the address table. However, that means you would need either to
create the address first if there is not one in tblAddress, or the AddressID
number would appear in the combo box field because it is the bound column.
The bound column is the field that is stored, so if you are storing AddressID
that is what you would see after making the selection from the combo box.
There are ways around either of these problems, but I just don't know what is
the most effective design. I also can't quite figure out what would happen
if a family moves. You could create a new address, but then you would have
to update each record that contained the old address, and that doesn't seem
quite right somehow.
I suggest you start a new thread with something like "One address, several
people" as the subject line. Describe the situation just as you did in the
most recent post. That is exactly the kind of information that is needed to
elicit an effective response. I will be watching for the question and any
replies, as it is something I would like to learn about as well. Good luck.
 
I

iputanameherealready

Bruce,

The separate address table allows the people to move from 1 villiage to
another. I did not want a many to many relationship. we could probably have
a whole discussion about that, and yes that 'should' probably be under
another subject. Getting back to the original subject, which is hard to keep
on at times, how does one fill in the foreign keys on tables in a form, or as
I put it the first time - filling in the prime keys through a form? Can you
help with that question? I have provided all the answers to what you asked,
although I could not see what those questions had to do with the getting to
the answer, I provided them to you. Will you help me with this issue?

I know that the answer MUST be simple or this would not be such a hard thing
to answer. Most products do this automatically, and as such, I thought that
MS would do the same thing. Perhaps they do and I just have something very
simple set up wrong, which is what I expect. I know that answer is there
somewhere and that I just missed it somehow.

Craig
 
J

John Vinson

Getting back to the original subject, which is hard to keep
on at times, how does one fill in the foreign keys on tables in a form, or as
I put it the first time - filling in the prime keys through a form? Can you
help with that question?
Click to expand...

By using a Form for the main table, a Subform for the related table,
with the Primary Key as the Master Link Field and the foreign key as
the Child Link Field, typically.

John W. Vinson[MVP]
 
B

BruceM

Ignoring address for a time and looking at names and history, your table
structure would be something like this:

tblName
NameID (primary key, or PK) - use autonumber as the data type
FirstName
LastName
etc.

tblHistory
HistoryID (PK) - autonumber
NameID (foreign key, or FK) - use Number as the data type
VisitDate
Reason
etc.

Click Tools > Relationships. In the Relationships window select each table
and click add. Close the box. Drag NameID from one table to the other, and
drop it on NameID in the second table. Click Enforce Referential Integrity,
then Create. Close the Relationships window.
Make a form (frmNames) based on tblNames. Better yet, make a query
(qryNames)based on tblNames, sort the names alphabetically, and base the form
on the query. You can also use a query to combine FirstName and LastName.
Help has more on that if you are interested, but it's not to the immediate
point. To base the form on either the table or the query, select Create Form
in Design View in the database window. A blank form will show up. Double
click the small box with a solid rectangle in the middle at the top left of
the form. It is where the rulers would intersect. That will open the form's
property sheet. Click the Data tab, click Record Source, click the down
arrow, and select the table or query. A list of fields will appear in their
own box. Drag the fields you need onto the form. You needn't drag NameID
and such. They are part of the form's record set, whether or not they appear
on the form. Leave room on the form for history, and save the form. Repeat
the process to build a form based on tblHistory. Save it as fsubHistory. Go
to the database window (the window with tabs for forms, queries, etc. that
appears when you start Access). Pressing F11 should bring it to the front.
With frmNames open in Design view, drag the icon for fsubHistory onto
frmNames. Switch to form view and take a look. Try entering some names and
histories.
You do not need to enter primary keys or foreign keys. The autonumber PKs
are assigned automatically. If you create a record, then start to create
another record but stop before you have entered any data, it will still "use
up" a number. Later, when you go back to enter a new record you will see
that the first record is 1 and the new one is 3. 2 is gone. It doesn't
matter. If you need a sequential number there are ways of doing that, but
let's not get into that just now.
The autonumber PK is assigned automatically as soon as you start a new
record. When you add a record to History (fsubHistory is the subform to
frmNames), it will automatically, because of the relationship between the
tables, have NameID filled in for the FK field. Your don't need to do
anything else to make this happen. If NameID is 1, every record created in
fsubHistory will also have 1 in the NameID field. Those history records will
always be associated with that Name record. No other History records will
have the same number for NameID.
You could do the same general process to establish a relationship between
History and Medicines. The Medicine form would be a subform of the History
form, which is in turn a subform of the Name form. These are nested
subforms, like one of those Russian wooden dolls.
There are lots of additional features, like using a combo box to select a
name and view all of the related information (rather than having to scroll
through all of the records to find a name). If the basic design works we can
get to that.
I'm still not quite sure what's going on with the Address table. Address is
often part of something like a Patient table, along with name and other
personal information unique to an individual.
 
Top