entering foreign keys from related records

  • Thread starter Thread starter nChicago
  • Start date Start date
N

nChicago

Hi,
I have created a two table MS Access 2003 database.
One table contains transaction data while the other table contains ledger
account information.
While entering a transaction into a form, I'd like to be able to select the
ledger account from a drop down box and link it (copy the foreign key?) to
the current transaction in the transaction table.

I'm trying to create a form that will do this with minimal programming (I
don't know VBA).

Any help would be appreciated.

Thank you.

N Chicago
 
nChicago said:
Hi,
I have created a two table MS Access 2003 database.
One table contains transaction data while the other table contains ledger
account information.
While entering a transaction into a form, I'd like to be able to select the
ledger account from a drop down box and link it (copy the foreign key?) to
the current transaction in the transaction table.

I'm trying to create a form that will do this with minimal programming (I
don't know VBA).
Click to expand...

Drop a combo or list box onto the form (make sure the wizard is turned on --
the little wand at the top right of the control tool box) -- the wizard will
step you through the process, asking which table you want to draw the
information from (in this case, your ledger account info table) and give
you the option to show information you want to see on the form from the
ledger account table (the name of the account) while inserting the
associated primary key in foreign key field of the transaction table. Be
sure to instruct the wizard to store the value in a field (and indicate the
foreign key field of the transaction table). The wizard constructs the
statements for you. This requires no VBA from you.

Hope that helps,
Carol
 
Carol,

Thanks so much.
I'll give it a shot later today.
Will the wizard handle populatingthe drop down box with content, too?

Thanks.

N
 
nChicago said:
Carol,

Thanks so much.
I'll give it a shot later today.
Will the wizard handle populatingthe drop down box with content, too?

Thanks.
Click to expand...

The drop down will populate for each _existing_ record in your accounts
table. You cannot enter a new account into this combo without some VBA.
You must populate the record in the accounts table first to have it appear
on the list.

A note -- if you include, say, the accountID (Primary Key) and a name for
the account (common) in the combo
and leave the "Hide Key Column" checked, what will show on your form is the
name,
but what is stored in your table is the Primary Key for the account (which
is what you
want) -- If you uncheck the Hide Key Column, what will show on the form
after the save is the ID (even though the name will show on the drop-down
during editing, and the ID is what will be what is stored in the table, too,
if you told the wizard to store it -- but this will force the user, who
might not remember accounts by ID, but rather by name, to have to click on
the combo to see the underlying name.

If you include several columns in your wizard-made combo (let's say
accountID, account name, and accountcontact and you hide the ID , what will
show in the drop down is account name and account contact, but after the
record is saved all that will show in the combo is the first column just the
account name -- the accountcontact would not show on the form after you save
the record.

If you want to show more that one field showing in the combo on the form and
keep it showing up there without entering the combo (for example, I have a
combo on my form frmContacts that I want to show first and last name, two
different fields, and include the two fields in a table-based combo -- this
will display only the first name after I exit the combo) -- you can create a
query, include the Primary Key field and a concatenated name field ( first
column of the query is ContactID from ContactTable, second column is
FullName:[FirstName]& " " & [LastName] ) -- then base your combo box on this
query instead of the table. The query gets its info from the table, and so
populates the combo and shows FirstName LastName in the combo. Query based
combos do not let you hide the key column by clicking -- but if you slide
the column divider all the way to the right so that the ID column is set to
0" (or set the first column width to 0" in the combo properties box under
column widths), you will get the same effect as the hide key column in a
table-based combo.

Happy comboing!
Carol
 
Thanks, Carol.

What a treasure trove of advice. You're the best!

What I ended up doing is basing the form on a query that pulls in the
related account record (if it exists). The combo box look-up (if i remember
correctly) is based on the ledger account table. I'm pulling in the account
PK and the description. Since the description doesn't show, I added a field
linked to the ledger account table to display the description once it's
looked up (or if there was a linked record it appears on form load). I like
the query idea of concatenating fields!

Regards,

N

CS said:
nChicago said:
Carol,

Thanks so much.
I'll give it a shot later today.
Will the wizard handle populatingthe drop down box with content, too?

Thanks.
Click to expand...

The drop down will populate for each _existing_ record in your accounts
table. You cannot enter a new account into this combo without some VBA.
You must populate the record in the accounts table first to have it appear
on the list.

A note -- if you include, say, the accountID (Primary Key) and a name for
the account (common) in the combo
and leave the "Hide Key Column" checked, what will show on your form is
the
name,
but what is stored in your table is the Primary Key for the account (which
is what you
want) -- If you uncheck the Hide Key Column, what will show on the form
after the save is the ID (even though the name will show on the drop-down
during editing, and the ID is what will be what is stored in the table,
too,
if you told the wizard to store it -- but this will force the user, who
might not remember accounts by ID, but rather by name, to have to click on
the combo to see the underlying name.

If you include several columns in your wizard-made combo (let's say
accountID, account name, and accountcontact and you hide the ID , what
will
show in the drop down is account name and account contact, but after the
record is saved all that will show in the combo is the first column just
the
account name -- the accountcontact would not show on the form after you
save
the record.

If you want to show more that one field showing in the combo on the form
and
keep it showing up there without entering the combo (for example, I have a
combo on my form frmContacts that I want to show first and last name, two
different fields, and include the two fields in a table-based combo --
this
will display only the first name after I exit the combo) -- you can create
a
query, include the Primary Key field and a concatenated name field ( first
column of the query is ContactID from ContactTable, second column is
FullName:[FirstName]& " " & [LastName] ) -- then base your combo box on
this
query instead of the table. The query gets its info from the table, and
so
populates the combo and shows FirstName LastName in the combo. Query
based
combos do not let you hide the key column by clicking -- but if you slide
the column divider all the way to the right so that the ID column is set
to
0" (or set the first column width to 0" in the combo properties box under
column widths), you will get the same effect as the hide key column in a
table-based combo.

Happy comboing!
Carol
Click to expand...
 
Back
Top