Solved

Possible to associate a single field on a form with multiple fields on another form ?

Posted on 2014-11-23
3
238 Views
Last Modified: 2014-11-23
Hello, I am a relative beginner to MS Access, having worked on my first database for the past couple of months. I am at a stage where I can manipulate VBA code, but not really write it.

1.      Database set-up
ClientTable –client information table
ClientForm -for data input into ClientTable
ProjectTable -project information table
ProjectForm -for data input into ProjectTable
ProjectFormContin -a continuous form listing project information from a ProjectTable-based query

Each project can have up to 4 clients associated with it. Client information is input into ProjectTable (fields: Client1, Client2, Client3 and Client4) via a combo box on ProjectForm which is pulling the information from ContactTable. ProjectFormContin includes 4 fields for displaying the contacts.

2.      What I would like to do
Have a button on ClientForm which would open up ProjectFormContin, displaying only these projects with whom the current ClientForm client is associated. If there was only one client associated with each project, it would be a simple matter to create a button linking the current ClientForm client to the client field on ProjectFormContin, however since there are 4 client fields on ProjectFormContin, I am not sure how this should be done. One possible way to do it would be to create 4 separate buttons on ClientForm, each associating the current ClientForm client with one of the 4 client fields on ProjectFormContin. However, this seems a poor solution in terms of usability. If anyone has any ideas on how this could be done more elegantly, they would be greatly appreciated. Thank you very much in advance for any help.
0
Comment
Question by:Paul McCabe
  • 2
3 Comments
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 500 total points
ID: 40460823
Hi,

the problem is that your data model is wrong. Each time where you begin to create more than one field of the same type in one table you can be relatively sure that your data model is not OK. So creating fields C1-C4 means: You are trying to create columns where you need to have rows.

What you need is a so-calles "m:n" table which connects projects and clients. The table consists usually of an auto id as primary key and two further fields for projectID and clientID. This makes it possible to link any project with any client (and additionally lets you add as many clients to any project as you want, not only 4).
Then you can delete the four client fields from the project table.

In a normalized model one table should only hold one class of information so the project table should describe only attributes belonging to the project, the client table should describe only attributes belonging to the client, the m:n table brings both classes together and itself describes no attribute of each of the other tables, it describes, which record of one table belongs to which of the other. It can also have additional attributes, i.e. to describe everything regarding to this reference, that could be the date when the client was added to the project or an information that the client was blocked in this project only or anything else strictly only belonging to the description of this reference between both. For example, if you want to describe that the client itself should be blocked in all projects, this would be an attribute for the client table.

In the end you have only one clientID field (in the m:n table) which you must use to search for so your button can simply query this m:n table to find out any projectIDs of this clientID which then can be used to filter the details in the project table (form).

This can also be done without a button, simply by adding a query on the m:n table as subform to the client form, Access links both tables and if you change the record in the client main form you automatically have a list of all projects in the subform - without any programming. You can then i.e. add a hyperlink to the listed project field of the subform to open the corresponding record in the project field or you can add a button instead if you want.

Cheers,

Christian
0
 

Author Closing Comment

by:Paul McCabe
ID: 40461360
Hi Christian,

Everything you says makes sense and has really helped put me on the right path. Thank you very much !
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40461491
You're welcome...:-)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now