Re: Automatically getting a form populated from other data that ha

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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



"John Spencer" wrote:

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 Boyce wrote:
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

"Pank" <Pank@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E6443ABE-F683-4589-A3A9-67193AB6BCD5@xxxxxxxxxxxxxxxx
"Jeff Boyce" wrote:

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

"Pank" <Pank@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:04D286C6-48DC-4F99-A66C-941EBAA8EE6E@xxxxxxxxxxxxxxxx
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



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.






.

Relevant Pages

  • Re: Automatically getting a form populated from other data that ha
    ... I would recommend that you only store the primary key from the provider table in the courses table. ... if you are entering information using a form you can choose to populate unbound controls on the form with the relevant information. ... the design are already in place and therefore I ... selecting courses offered by the provider, along with the cost of the course ...
    (microsoft.public.access.gettingstarted)
  • Re: OT - Cant we all agree????
    ... The reason is that so many decent, honorable, ... I've observed your thoughtful and measured responses to Powell over ... Jeff is who he is. ... Is he the most important design engineer in the ...
    (rec.music.classical.recordings)
  • Re: Can you decrypt this?
    ... How will you keep the ministerial mixed packages before Jeff does? ... It can a little design as opposed to violent calm highways. ... Many arbitrary dry projects will most mix the stages. ...
    (sci.crypt)
  • Re: Automatically getting a form populated from other data that ha
    ... Course Provider. ... the design are already in place and therefore ... How can I automatically populate the address and post code when a Course ... selecting courses offered by the provider, along with the cost of the ...
    (microsoft.public.access.gettingstarted)
  • Re: "No objects need to be updated"
    ... As Jeff pointed out, Name AutoCorrect is designed to do this, but it doesn't work. ... Some of the other "updates" from design view can be useful. ... For example, whatever you enter as the Description of the field in table design, Access uses for the StatusBarText property in a text box. ... If you change the Description later, Access 2003/2007 offers to find the text boxes bound to this field, and update the StatusBarText for you. ...
    (microsoft.public.access.tablesdbdesign)