Auto-Fill a Form

[Kim]

I am new to Access and am setting up my first database to track several
breakout sessions for one large conference. So far, I have a 'presenters'
table and a 'sessions' table. I want to create a form that I can enter the
session info and then select the presenter(s) from a dropdown list and then
once I select the presenter(s) it would automatically fill in their company,
title, phone number and email address. There could be just one presenter for
each session or there could be multiple presenters. Also, each presenter
could be teaching multiple breakout sessions. Any suggestions on how to make
this work would be greatly appreciated - thanks!

 

[Jeff Boyce]

Kim

It sounds like you are creating ... a spreadsheet!

If you have a table with Presenters (and their contact info), you don't need
to "fill in" that information because it is already available.

There's no advantage to duplicating the same information over and over, and
some serious disadvantages.

Look into using a query to pull together the information you need (e.g.,
Presenter's contact info).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

You need a third table, SessionPresenters to model the many-to-many
relationship type between Sessions and Presenters. Assuming Sessions and
Presenters have primary key columns SessionID and PresenterID (probably
autonumbers) the new table would have just two columns SessionID and
PresenterID, but not autonumbers this time, just straightforward long integer
number data types. The primary key of SessionPresenters is a composite one
made up of both columns, each of which is a foreign key referencing the
primary keys of the other two tables.

For data entry have a Sessions form in single form view based on the
Sessions table (or better still a query on that table sorted by session title
or whatever order you want the form to show the sessions in) and a subform in
continuous form view based on the SessionPresenters table. The
LinkMasterFields and LinkChildFields properties of the subform control will
both be SessionID.

The subform will contain a combo box set up like this:

Name: cboPresenter

ContrlSource: PresenterID

RowSource: SELECT [PresenterID], [Company], [Title], [Phone Number],
, [Presenter] FROM [Presenters] ORDER BY [Presenter];

BoundColum: 1
ColumnCount: 6
ColumnWidths 0cm;0cm;0cm;0cm;0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first 5
dimensions are zero to hide the first 5 columns and that the last is at least
as wide as the combo box.

If you have FirstName and LastName columns in the Presenters table rather
than a single Presenter column, change the RowSource to:

SELECT [PresenterID], [Company], [Title], [Phone Number], [Email],
[FirstName] & " " & [LastName] FROM [Presenters] ORDER BY [LastName],
[FirstName];

You'll have to change the column names in the above to your actual ones of
course.

Add 4 unbound text boxes to the subform with ControlSource properties of:

=[cboPresenter].[Column](1)
=[cboPresenter].[Column](2)
=[cboPresenter].[Column](3)
=[cboPresenter].[Column](4)

The Column property is zero-based, so Column(1) is the second column
(Company) and so on.

When you select a presenter from the combo box's list the four unbound text
boxes will show the company, title, phone number and email address for the
selected presenter. You can add as many presenters per session simply by
entering a new row for each in the subform.

You can use the form wizards to create the form and subform, then open the
former in design view and embed the latter in it as a subform.

You could create a report/subreport in much the same way of course for
printing out the sessions and their presenters. However, a better way would
be to join all three tables in query, base a report on this and group the
report by session title (or whatever), putting the session data in a group
header and the presenters data in the detail section. Once you've created
the query you can use the report wizard to build the report.

Ken Sheridan
Stafford, England