How do I show fields from another table?

  • Thread starter Thread starter BC
  • Start date Start date
B

BC

I have a two tables that contains financial information.
One table (tblDetail) in my main file and contains the data that I wish
to update.
The other table (tblUpdates) contains information that I will use
periodically to update tblDetail.
I created a form (frmUpdates) which displays the fields of tblUpdates)
for editing.
The problem(s) came when I decided to display in textboxes, some of the
current information contained in tblDetail to use as a reference will
creating frmUpdates records.
At the moment, I have not created any relationships between the two
tables.
There is a common field contained in both tables ... "HA#".
I would like to have it so that when one enters the "HA" number while
editing tblUpdates using frmUpdates that the desired fields from
tblDetail appear in textboxes.
I do not wish to allow these textboxes to be used to edit the record in
tblDetail (only displayed for reference).
I am a newbee @ Access ... please give me plenty of details.
Thanks!
 
BC,

If you do have the common HA# in both tables you do have a
common field that you can base a relationship on. If that is
the case, display the second table in a subform on the main
form and set the Master/Child relationship to be the HA#.
Make that subform control 'Locked' if you don't want to be
able to modify the updates from the main form.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Gary,
I am new at Access programming ... would you giving me a few more
details on how to go about doing this?
If I do this will it impact the use of these tables by another routine?
Thanks for your help!
 
BC,

No, creating one relationship will not affect future ones
and relations are the crux of a 'relational database' such
as Access. It would be helpful to have more info on the
model of what you are trying to do first but, here is a
really quick primer and you can get back with more detailed
questions.

Each table you create should be designed to hold only info
that is unique to that item or subject.

Every table should have a unique primary key, usually an
Autonumber that you let Access assign, that will uniquely
identify each record it holds.

All fields that you will search, sort or link on should be
flagged as Indexed in your table designs.

Tables have relationships to each other that are usually a
one to many or a one to one relationship. In your case if
there are more than one 'Detail' for each of your Update
records, it would be a one Update to many Details about the
update. In rare cases where you can gaurantee that there
will only be one Detail about the Update it could be a one
to one relationship. I have a feeling that there is
something that you are updating so there is probably another
relationship between the Updates and whatever it is, most
likely a one from that to many Updates.

The many side ( or child ) table normally stores the Primary
Key of the one side ( or parent ) in a compatible field that
is known now as the Foreign Key. If your Details are many to
one Update you would want to have a DetailID Autonumber
field and then store that Primary Key of the Updates table
in another field that will be the Foreign Key. HA# may fit
this bill.

If you create a master form for the parent table and a
smaller form for the child table, you can now insert the
smaller form right on the master form through the use of a
subform control. When you do this you need to tell Access
what the Master/Child relationship is, which here would be
HA#. Once you do this you can now view/add/edit any child
records that will now be displayed in the subform and kept
separate from the parent data. This is commonly used because
it has the ability to show all the child records for that
parent record and when you enter a new one, Access will
automatically fill in the Foreign Key field with the ID of
the parent for you and your integrity of the relationship is
garaunteed.

If you turn the Wizard button on on your Form Design
Toolbar, it will help you set this up when you drop a
subform control on your form.

Play with this and come back with questions. We will
probably need more details of what you are doing at that
point.
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Thank You Gary ... you will likely hear from m shortly.
How do I flag you in the newsgroup?
Bob
 
No real need to as there are many here who are great experts
and honored by Microsoft as Most Valuable Professionals for
there contributions on the newsgroups. They also do a great
job of monitoring the threads to make sure that you are
getting the best advice. Nice thing about having a variety
of folks chip in is that you will get a variety of valid
perspectives and examples.

If you do want to communicate to my attention, I flag the
threads that I have contributed in so you can just reply to
this thread again and I will pick up that there is new
traffic on it. It is also OK to flag your subject with the
name of someone you have been communicating with.

Regards,

Gary

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Back
Top