Matching information for validation purposes

[JL Levis]

I am trying to do something that may be quite simply, but have had a problem
generating the desired result. I have two tables and forms which contain
information related to each other. Table A contains master data for several
grants. Table B contains data for invoices submitted against these grants.
When I enter an invoice into table B for payment, I start with the grant
number. I want Access to automatically populate the fields with other
information pertaining to this grant (e.g. recipient, project, amount, etc.),
from these fields in Table A. Then I would complete the other fields (e.g.
date, amount, etc.). By having Access automatically populate the
information, I can be sure that the invoice is linked to the correct grant.
If I put in the wrong grant number, I should get either a null value for the
"linked" fields, or values pertaining to another grant.

This is important because if an invoice is posted to the wrong grant, then
the available balance of the grant will be incorrectly reflected as well.

Thanks for any suggestions

 

[KARL DEWEY]

There is no need to repeat the same data in each table.
Either make Table A Grant Number field the primary key field or add an
autonumber field for primary key.
If you use an autonumber field then in Table B add a number - long integer
field and you must populate it with number matching the primary in Table A.
Set a one-to-many relationship from Table A primary key field to
corresponding field in Table B (Grant Number or long integer).
Use queries to feed a form/subform with Master/Child links using the Table A
and Table B fields.
After you open the form find the Grant Number and add records in the subform
for additional invoices.

 

[Larry Daugherty]

It sounds like the usual "one-to-many" relationship between the two
tables: tblGrant would list all of the grants and all of the
information about the grant. tblGrant is the "one" side of the
relationship. tblInvoice would be *related* to tblGrant by the use of
a Foreign Key field which would have the value of the Primary Key of
the parent record in tblGrant. tblInvoice is the "many" side of the
relationship. Life may be less confusing when you're working out the
concepts if you stay with Autonumber datatypes for the Primary Keys.
That way the *Foreign Key* field in tblInvoice will simply be a Long
Integer.

Note that there will be nothing labeled "Foreign Key" as you go about
setting things up. To help yourself keep track of what you're doing,
give the foreign key field in tblInvoice the same name as the primary
key field in tblGrant. Let's name that Primary Key "GrantID".

Once you have your tables designed go to the Relationships window,
bring in the two tables and then click on GrantID in tblGrant and drag
a line to GrantID in tblInvoice. DoubleClick the line you just drew.
A dialogue box will open that will help you establish the join. When
you have done that, check Referential Integrity and Cascade Delete
Related Records.

The most common way of displaying and managing a one-to-many
relationship is using the Form/Subform paradigm: I'll sketch it here
and there is sufficient guidance in the Help file to keep you going
....

Create a new form based on tblGrant. Tweak it a bit but not too much
because you're going to play with it and may want to change your
design based on new information ... Extend the bottom of the form's
detail section well below its initial setting. Close the form and
save it as "frmGrant".

Design a new form based on tblInvoice. Don't spend much time on that
form's design just yet. In its properties set Default Display to
"Datasheet". Save the form as "frmInvoice".

Open form frmGrant displaying at less than full screen. In the
Database/Forms window, click frmInvoice and drag it into the upper
left of the area you cleared at the bottom of the detail section of
frmGrant.

What you see at first may look like a mess but if you'll experiment
with it you'll begin to see the possibilities.

Before you consider the exercise complete you should go back and
re-design frmInvoice so that a single instance of the form looks like
a single row of the datasheet display. Change the Default Display
property to Continuous. That will give you significantly more power
and flexibility in the utilization of the subform to do what you want.

HTH