Automatically getting a form populated from other data that has be

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

Guest

Firstly, I am a total novice with Access and therefore please be gentle.

I have a form that I intend to use for tracking people training.

I have two main tables, one that holds course providers along with their
name, 3 address lines and post code and the other that holds information
about the courses that people have been on..

The main form is designed to populate a lot of fields and one of them is the
Course Provider (which is a drop down).

How can I automatically populate the address and post code when a Course
provider is selected.

I am using Access 2002.

Any assistance offered would be appreciated.

Thank you in advance.

Regards
 
You have courses, you have trainers, you have people-who-may-take-courses,
you have courses-taught, and you have courses-taught-and-taken. You need
five tables, not two.

Keep person info in your Person table (i.e., firstname, lastname, phone#,
....).

You could even use that same table (now you only need 4) to keep
person-related info about your trainers (I assume they are persons, too).

Keep a table of Courses, with course-only information (e.g. subject, cost,
....).

Keep a table of Courses-Taught (these are the instances of courses for which
you have a designated trainer).

Keep a table of Courses-Taken (this is a "junction"/"resolver"/"relation"
table that holds:)
Course-TaughtID
PersonTakingCourseID
PersonTeachingCourseID
RegistrationDate
...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Jeff Boyce said:
You have courses, you have trainers, you have people-who-may-take-courses,
you have courses-taught, and you have courses-taught-and-taken. You need
five tables, not two.

Keep person info in your Person table (i.e., firstname, lastname, phone#,
....).

You could even use that same table (now you only need 4) to keep
person-related info about your trainers (I assume they are persons, too).

Keep a table of Courses, with course-only information (e.g. subject, cost,
....).

Keep a table of Courses-Taught (these are the instances of courses for which
you have a designated trainer).

Keep a table of Courses-Taken (this is a "junction"/"resolver"/"relation"
table that holds:)
Course-TaughtID
PersonTakingCourseID
PersonTeachingCourseID
RegistrationDate
...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...

Jeff,

Firstly, thank you for your words of wisdom that it should be 4-5 table to
hold all the appropriate information and not just 2 that I have.

Unfortunately, the design (i.e. tables) are already in place and therefore I
am stuck with making do with two tables (don’t want to get into the politics
about the bad design!!).

Therefore can you help with my original question:-

I have two main tables, one that holds course providers along with their
name, 3 address lines and post code and the other that holds information
about the courses that people have been on..

The main form is designed to populate a lot of fields and one of them is the
Course Provider (which is a drop down).

How can I automatically populate the address and post code when a Course
provider is selected.

As an extension, once the providers name is selected and the relevant
address information populated, we will move onto allowing the person to
select a course that the provider provides (via a drop down and from a new
table called Courses). Therefore I need a mechanism that will allow to only
selecting courses offered by the provider, along with the cost of the course
that will need to be automatically populated once the course is selected.

I am using Access 2002.
 
If you intend on using MS Access, you'll have to come up with some
convoluted work-arounds to get Access to do something it isn't designed to
do. Sorry, but I don't have any experience in the area you are describing.
Perhaps one of the other newsgroup readers has had to build a similar
work-around and can share his/her experience.

Is there a reason why you couldn't create a better-normalized data structure
and migrate the existing data to that new structure? Given that you really
don't want to have users mucking about in the data structure, they might
never realize that the data is "in a better place."<g>

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
It sounds as if you have one provider per course. If that is the case,
I would recommend that you only store the primary key from the provider
table in the courses table. Then all you need is to join the two tables
when you need the additional information.

HOWEVER, if you are entering information using a form you can choose to
populate unbound controls on the form with the relevant information. If
you really feel the need to store the information (bad idea) then you
have a couple options.

Add some columns to the combobox query, so that you have the address
information, etc. included. You can use the column widths setting on
the combobox to hide the additional columns by using 0 as the width.

In the after update event of the combobox you can use vba code to set
the values of the controls on the form. Columns are numbered starting
with zero, so if you had an address in column 2 and a phone in column 3
your VBA would look something like the following.

Me.txtAddress=Me.CboxProvider.Column(1)
Me.txtPhone = Me.CboxProvider.Column(2)


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Jeff, John,

Firstly, many thanks for your input and time.

Jeff, I have told the users that the current DB design is questionable and
should be redesigned to give a better "fit". However, they have various other
departments using the DB andd therefore a change would be a big change that
would have to be co-ordinated which they don't want to do.

John,

I will try out your suggestion and hope that that delivers what I want.

Thank you to both.

Regards
 
Back
Top