Solved

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

Posted on 2014-11-23
3
248 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

688 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