Creating Forms

[Lari]

Hi... I have MS Access 2003. I am creating a database (or attempting to
). In the form screen, I wanted to make it so that when I enter the name of
a company (I already have the tables set up) in one area of the form, that
their information will automatically come up (i.e. address, phone #, etc...)

I know how to do it in excel - as a conditional formula and don't seem to
get it right with Access.

Thanks for your assistance

 

[Jeff Boyce]

Lari

One approach would be to add an unbound combobox control in the header of
the form. Base that combobox on the table holding company information (by
the way, that table is well-normalized, right?), using the primary key field
(i.e., ID) and the CompanyName field, but only displaying the CompanyName.
You do that by setting the bound column to column 1 (the ID) and setting the
column widths to something like "0, 3" (make the CompanyName width as wide
as you need).

Then create a query against the company information table (normalized,
right?!) and set the Selection Criterion of the ID field to point at the
form's combobox ... it will look something like:
Forms!YourForm!cboYourSelectACompanyCombobox
Use this query to "feed" the form.

So, when you first open the form, the query runs, finds no value in the
combobox, and loads (?!NO?!) record in the form.

You select a Company from the combobox and nothing happens ... Access
doesn't know yet to requery the form. Add an event procedure to the
combobox's AfterUpdate event that looks something like:
Me.Requery

Close the form, open it again, select a Company from the combobox, and watch
the form load up that Company's information.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ron2006]

An equally usable alternative is

an unbound combobox on an unbound form created exactly as Jeff
suggests, and then place a subform on that form and have it bound to
the company table.

But then manually enter the master/child field names on the properties/
data tab for the subform.

With this setup you do NOT have to issue the requery in the
afterupdate. It will be automatic.

In both of our examples you may want to turn off the ability to add
and/or delete properties of the created forms/subforms.

Ron

 

[Jeff Boyce]

Ooooh, slick trick!

Jeff

 

[Lari]

Hi Jeff:

thank you for your response. One more question - What do you mean by
"normalized"?

 

[Jeff Boyce]

Lari

Microsoft Access is a relational database. It's features and functions work
best when the data is well-normalized.

The concept of normalization is not something most folks ever run across in
the real world ... 'normal' isn't NORMAL!

If you feed Access 'sheet data, it won't be able to best use its
features/functions.

There are several great sources describing the process of normalization -- a
quick search via your favorite online search engine should list many,
especially if you include "MS Access" and "normalization".

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Lari]

I must be a complete dolt... I understand the first step (adding unbound
combobox, etc...) but I get lost in the create a query.

I don't suppose you know where I could find a sample of what you are telling
me (I am better after I see examples).

Thank you so much for your help and patience

 

[Lari]

Hi ron:

I would also like to thank you! Is there somewhere I could see an example...

Thank you for your time and patience!!

 

[Ron2006]

One way to do that is to simply use the tools tool bar and drop the
subform icon on your form.

When it asks you to use a form or existing table/query, select the
table/query.
Then select the table that contains the information you want to show,
and select all of the fields.
That will esentially create the query you need. This will allow you to
sort the information if desired.

Ron