Coummunity Management Database issue (issue in a Form)

[dugaucher]

Hi Folks,

I'm new to this forum and just took Access Training. I consider my access skills a 15%. I have no knowledge of coding and I apologize if this has been answered in a different section.

I have a specific issue regarding a project with a tight deadlinen (Next Week): BUILDING A COMMMUNITY MANAGEMENT DATABASE which the purpose is to track Standard Development Activities.

I have attached a zip file which includes:

1. Table Relationship
2. Overview of all forms
3. Contact Form
4. Member Form
5. CommityMemberLinks Form (Form with the issue)

Process/Requirement:
I walked you through in the creation and assignment of a Contact (Cerilli, Marina) to a Committee. You will notice that:

• Before a Contact is assigned to a Committe, it becomes a Member (In the Members tbl).
• 1 Member can be in multiple committees and 1 committee can have multiple Member.
• 1 Member can represent one or more Organizations in one or more Committees

ISSUE:
HOW DO I ASSIGN A MEMBER TO A COMMITTEE IN THE COMMITTEEMEMBERLINKS FORM BY SELECTING THE MEMBER'S NAME (Located in the Contact Tbl) INSTEAD OF HIS NUMBER? :banghead:

Thank you so much for helping me with you knowledge.

Dugaucher-

 

[Frothingslosh]

My first suggestion would be to use a combo box. Pull both the person's ID and their name, and set the ID as the bound column, but give it a column width of 0. Show the name as normal. The box will show the name, but the database will actually use the ID.

You can actually use the same trick with a list box.

 

[dugaucher]

Thanks for your prompt response, Frothingslosh

When you say Person ID, you mean: "MemberNumber" or "ContactNumber" in my project?.

My Contact table is the only table that have FullNames. My Contact Table is linked to my Members Table, which is linked to the CommitteMembersLinks Table that I have trouble with.

The problem is: The CommitteMemberLinks Tbl is linked the Member Table with MemberNumer (ID), not names.

 

[Frothingslosh]

I have to admit, I actually couldn't look at your project - can't download stuff at work.

By PersonID, I really mean whatever you're using to uniquely identify your person in the row source. That can be a table or a query pulling data from multiple tables.

What I'm gathering is you want a combo box/drop box that lists people by name, and you can select a person from it to assign to a committee.

Your combo box would have a row source that is a query which includes the MemberNumber and the FullNames. This query could be based off one or multiple tables and can have any number of conditions; it's only important that it return the ID and the name, either as one name field or separate last and first names, doesn't matter.

Let's say you have ID and FullName, in that order. In this case, in the combo box's properties, you would set 'Bound Column' to 1. Column Count would be 2, and Column Widths would be 0";X" with X replaced by whatever width you want for the name display. List width would be the width of all the displayed columns - of which there is one in this example.

What this does is create a combo box that displays the full name, but actually saves the ID assigned to that person. Then you can bind the box itself to whichever field in CommitteeMembersLinks you're using to save the ID of each person assigned to each committee.

If your problem is that you have two fields, each with a different ID number for each member, then you have a normalization error that must be resolved. Each person should only have one single ID assigned to them personally. There may be other values unique to that person as well (I work in health care, and one person can have different ID numbers in each of several plans), but you always need one unique identifier with no other purpose than to identify that person.

Sorry about the lecture mode if you already knew that - I'm about to jet, and didn't want to assume anything.

 

[Frothingslosh]

And no, I'm not actually going to give every step needed because this is obviously a homework assignment.

Process/Requirement:

I walked you through in the creation and assignment of a Contact (Cerilli, Marina) to a Committee. You will notice that:

• Before a Contact is assigned to a Committe, it becomes a Member (In the Members tbl).
• 1 Member can be in multiple committees and 1 committee can have multiple Member.
• 1 Member can represent one or more Organizations in one or more Committees

I've given you the tough step - how to display a person's name while storing the ID. If you're stumped on how to assign multiple people to multiple committees, with no person unique to any given committee, my suggestion is to start by looking up junction tables. That should be enough to get you through this.

 

[dugaucher]

Frothingslosh,
You are very generous with your knowledge. Thanks one more time.
I will read through all this and make sure I understand everything
I will get back to you shortly with my test results.

Talk to you later.
Dugaucher-

 

[dugaucher]

Just want to let you know that I tested and it worked. Thanks million times. You just saved my life. I will pass on the knowledge to the next person.

 

[dugaucher]

Oh no, my query was working fine and I just noticed that it doesn't update automatically with the changes I make to the Members' table. PLS HELP!��

 

[dugaucher]

Hi Frothingslosh,

Pls ignore my message it works great!!!!!!! I did something wrong earlier with the programming. It's perfect!!!
Tks again bro.