Trouble with list boxes

[JChandler22]

View attachment db2.zip
Hello, please see the attached zip file for your reference. I'm rather new to access, so please bear with me.

On the 2nd tab (Program Interest) of the form called "Contacts," I have a multi-select list box where the user can select what program(s) the person is involved in.

The problem is that when you select multiple entries for a particular record, then go to another record, those entries are not saved. What am I doing wrong? Is there a better way to go about this?

Eventually, I will also need to build a report that shows the programs associated with each record (ie what programs each contact is involved in).

Thanks in advance for your advice ...

 

[Bilbo_Baggins_Esq]

Well, your structure is not good for this task.

I would have one table with employees and thier ID number
I would have a table with the programs, thier descrition, etc, and a program ID (also add a check box)

finally, there should be another table (welll call it Program_Members) with one entry for each program_ID per employe_ID.

If Joe has ID 0001 and he is in Program_1, Program_7, and Program_15
there should be 3 seperate records in the Program Members table.

This way in your main form you can add a sub form that shows the available programs and their check box.
Put a check in eacah appropriate box.
Then, add an append to the Program_Members table for each program that member is affiliated with.

You can also add another sub form that would be tied to a query that matches up programs and employee through the Program_Members table

 

[Bilbo_Baggins_Esq]

Ooops, I forgot to add ,that you'll want to create an update query, that updates all the check boxes to false after the employees entries have been made to the program_members table.

 

[JChandler22]

Thanks Bilbo, I'll work on that.

I like your checkbox idea for the main form. But I'm a bit confused about where to place the checkboxes in the 'Programs' Table you were talking about.

Can you explain a little more?

 

[Bilbo_Baggins_Esq]

Open the Programs table in design view and add columns for:
Program_ID = AutoNumber
Description = text
Selected = Yes/No (Format = true/false)

Remove the Programs column from the contacts table

create a new table called Program_Members
Program_ID = number
contact_ID = number


Now if you want to get a little bit more indepth, you could add another column to the Program_Members for Member_Type and you could define your contacts as Directors, Consultants, participants etc.
You could manually type that info in, but it would more tightly control the quality of the data by creating a seperate table of member types.

 

[JChandler22]

finally, there should be another table (welll call it Program_Members) with one entry for each program_ID per employe_ID.

If Joe has ID 0001 and he is in Program_1, Program_7, and Program_15
there should be 3 seperate records in the Program Members table.

Thanks again for the advice, Bilbo. A couple more questions as I go along here. For this Programs_Members table as you've described it above, is this what you're suggesting it should look like?:

Contact_ID | Program_ID
0001 | Program_1
0001 | Program_7
0001 | Program_15

...so that if a contact has multiple programs selected, that contact has one record for each program selected?


Also, for this same table, should those two columns use the Lookup feature from the other tables? or do I need to setup a relationship? Again, I appologize that I'm asking such elementary questions, but I really appreciate your help! I'm definitely making progress thanks to you.

 

[Bilbo_Baggins_Esq]

Contact_ID | Program_ID
0001 | Program_1
0001 | Program_7
0001 | Program_15

...so that if a contact has multiple programs selected, that contact has one record for each program selected?

That's it exactly.

I don't think Lookup is needed.
You can define the relationships if you like, but that too is not really needed.

To be honest, only seldom do I use relationships.