Forms In Access

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

Guest

I am trying to build a form which will automatically enter information in a
field when the first field information is entered. Example I have one table
which list regions by number ( P. Key) and then the next record is a
description of tha region. I then have a table for entering order information
I have the the region field and the description field as forgin keys. The
relation ship is one to many from the region table to the order table. I
would like to have it when the region field is propagated the corrosponding
region description comes up automatically so we do not have to enter it every
time. I wanted to avoid drop down or field list boxes if possiable.
 
There is actually MANY approaches to use here.

The approach I would use in your case (since you don't want to use a combo
box) is to simply base the form on he main table, and LEFT JOIN in the
additional table(s) that have values you need to display (you then simply
include those additional description fields in the query, and base the form
on that query. Now, additional fields will simply show when you go
view->field list). Then, you simply place the description text box on the
form, and when you type in the PK, the description will show.

You could also use a combo box for the regions, as that does make things
somewhat easier for users. I would use the wizard, and have the combo box
display BOTH columns when you expand it. Note that the wizard does tend to
hide the pk value, so after you use the wizard, edit the combo box, and
change the length of the first column in the combo so it displays the pk.
Then, you simply a text box on the form (presumably right beside the comb
box), and you bind this box to the 2nd column of the combo.

=(mycombo.column(1))

Then, when you type in the region, or select it by dropping down the combo,
the description field will also be filled.

Which approach you use will depend on if you want a combo or not.

A 3rd and often suggested solution is to use a dlookup field, but I find
those a bit messy to setup. Thus, you could bind the text box to a dlookup
expression.

=(dlookup("DescriptionField","ReginTable","PK = " & ME!myFKfield)
 
On Mon, 18 Jul 2005 20:22:01 -0700, Teri Bridges <Teri
I am trying to build a form which will automatically enter information in a
field when the first field information is entered. Example I have one table
which list regions by number ( P. Key) and then the next record is a
description of tha region. I then have a table for entering order information
I have the the region field and the description field as forgin keys. The
relation ship is one to many from the region table to the order table. I
would like to have it when the region field is propagated the corrosponding
region description comes up automatically so we do not have to enter it every
time. I wanted to avoid drop down or field list boxes if possiable.
Click to expand...

You're missing the point of how a relational database works.

The description field should exist in the Regions table, AND ONLY IN
THE REGIONS TABLE.

It is redundant, unnecessary, and pointless to store the description
field in your second table. If you need to see the description in
conjunction with the data in the second table, *use a Query* joining
the two tables; the region field is all you need to link to the
regions table.

If you wish to *see* the description on the form, you can either make
it the first nonzero width field in the combo box (in which case the
combo box will show the description instead of the code), or put a
textbox on the form with a control source

=cboRegion.Column(n)

where cboRegion is the name of the combo box, and (n) is the
zero-based subscript of the description field in the combo's
recordsource.

John W. Vinson[MVP]
 
John,
Yes it is clear that I am new to databases, but am trying hard to grasp the
concept. When I made my query I was able to get the descripton to show up,
and I could scroll through them but when I entered my Region code the
description code did not change. I think the hint about not being redundant
and checking my relationship may help. I will try this. Fields-- OrgCode
OrgDescription, phone, contact then Fields Asset Id Date Org code , build a
query based on the relationship of OrgCode, put a text block on my form and
control source it to Org Description. Am I following your advice clearly? I
do thank you for your help and advice.
 
Back
Top