List box for data entry

[GingGangGoo]

I'm pretty sure the following is doable with code, but I'm not code fluent enough to write it all by myself yet.

On a form I have an unbound listbox set to simple multiselect . The listbox is filled from a query which filters a master table by a specific criteria.

I would like the choices I make from the list to create a new record in the underlying table where the record includes fields for each choice, so for the list box to enter the data in those two fields. Each record in the table would look like this: GroupID, GroupName, MemberID, Helper1, Helper2, etc...

BTW - The unique group is made up of 1 member and any two other members assigned to "help".

I'd appreciate any help with the code for this.

Thanks

 

[Pat Hartman]

You can do this without any code at all.
Create a subform based on the table that you want to insert records into. Use two combos, one for each helper. As long as the master/child links are set correctly on the subform control, Access will populate the MemberID. MemberName doesn't belong in this table so remove it. You can always get the MemberName by joining to the Member table in a query.

 

[GingGangGoo]

You can do this without any code at all.
Create a subform based on the table that you want to insert records into. Use two combos, one for each helper. As long as the master/child links are set correctly on the subform control, Access will populate the MemberID. MemberName doesn't belong in this table so remove it. You can always get the MemberName by joining to the Member table in a query.

Well, I'm trying to simplify for my end user so that he only has one object to work with, just one list box. I want it to be, click, click, done. Not.. look, click, move to next "field" look, click. It might seem like a small detail, but I'm trying to make every bit of this as UI friendly as possible. I also think it looks less cluttered. Hope that makes sense.

 

[kayleycunningham]

It sounds like you're looking to use a multiselect listbox to create new records in your table based on selections. While doable with code, your idea involves updating fields like GroupID, GroupName, and MemberIDs from the listbox choices. I'd recommend exploring VBA tutorials or consulting with someone proficient in VBA to help you implement this functionality. Good luck!

 

[MarkK]

GroupID, GroupName, MemberID, Helper1, Helper2, etc...

To model group membership you are dealing with a many-to-many relationship, which requires three tables, tGroup, tGroupMembership, and tMember.

Store your groups in tGroup. Store your members and helpers in tMember. Then to describe a relationship between a group and a member--which is its own discrete piece of information--use the tGroupMembership table, which would have fields like...

tGroupMembership
GroupMembershipID (PK)
GroupID (FK)
MemberID (FK)
IsHelper (boolean, describes the nature of the membership)

This is the only real elegant solution to what you are trying to do. Everything else is going to feel like paddling upstream.

 

[GPGeorge]

Well, I'm trying to simplify for my end user so that he only has one object to work with, just one list box. I want it to be, click, click, done. Not.. look, click, move to next "field" look, click. It might seem like a small detail, but I'm trying to make every bit of this as UI friendly as possible. I also think it looks less cluttered. Hope that makes sense.

Hm. In the interest of reducing effort for users, the approach is to make it harder to manage the data. That trade-off seems not to be in the favor of good data management. Perhaps users are smarter than we think sometimes. Perhaps users can learn how manage their work with a logical, effective interface designed to maximize data integrity.

 

[moke123]

12 year old post with a reviving post that looks AI generated.

 

[Pat Hartman]

12 year old post with a reviving post that looks AI generated.

Are we going to have to start answering questions for AI's now? Geeze.