Solved

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

Posted on 2014-11-23
3
240 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

914 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

20 Experts available now in Live!

Get 1:1 Help Now