One to many on a single form

[Oregoncoder]

I am trying to create a form that another user could use for a one to many relationship. I would like the form to be a simple form as I have shown above. I know that one to many usually requires a subform, but I find that awkward for the end user. I would think using vba this could be pulled off or am I crazy? Or is there some other way to make a simple form to get this one done? Any help for an old(76 years) programmer to get this done would be appreciated.

[Welshgasman]

Honestly cannot see why that is awkward.
What is awkward, is trying to code it some other way.
Likely could be done, as most computer tasks can, but is it worth all the effort involved, for so little gain?

[xps35]

A problem can be that your model allows multiple persons on one position. Your form suggests one person for each position.
Sort that out first.

[Welshgasman]

Wrong thread.

[orange]

Oregoncoder,
For clarity, describe the process that this form will support.
Is this to record the "executives" for the next session or some other process eg voting??

[Oregoncoder]

A problem can be that your model allows multiple persons on one position. Your form suggests one person for each position.
Sort that out first.

If I want only one person to be president, one VP, etc. should this be a one to one relationship?

[Oregoncoder]

Oregoncoder,
For clarity, describe the process that this form will support.
Is this to record the "executives" for the next session or some other process eg voting??

This would be for the recording of new officers. My thought would be to perform a delete query for the old officers before the assignment of the new officers.

[CJ_London]

Based on what you are describing I would have thought you need an FK field in your board table to link to the PK field in your roster table

then have a continuous form based on the board table with the FK field as the control source to a combo using the roster table as a rowsource

But your requirement still seems to be very limited

[ArviLaanemets]

The solution for your form depends on what you want to have.

I assume, you have tables like:
tblBoardMembers: MemberID, ForeName, LastName, ...;
tblBoardMemberPositions: PositionID, PositionName, ...

Now in case you want to register, which member of board has which position currently (i.e. at today) - without any history, you have in one of those tables the foreign key field linking it to other of those tables (NB! In one of them only!). Like:
either tblBoardMembers: MemberID, ForeName, LastName, ..., PositionID
or tblBoardPositions: PositionID, PositionName, ...,MemberID
In the form based on the table with foreign key, you will have combo box as control linked to this foreign key, and displaying either member full name, or the position name this member holds currently. In case you want in the form based on another table the linked info to be displayed too, you must use an unbound control with formula as source to get this info displayed there too. And no subform is needed at all!

In case you want for every board position to keep the history of all persons who had or have this position (or for every board member to keep the history of all board positions he/she had or has), you need a third table
tblBoardMemberPositions: BoardMemberPositionID, MemberID, PositionID, ValidFrom (where ValidFrom contains the date the member got this position). The position will be assigned to this member (or the meber will be asiigned to this position) until today when there is no later changes, or until there will be a later ValidFrom, which negates the previous entry.
NB! This table defines many-to-many relationship! To avoid e.g. same position to be assigned to several members at same time, you have to apply some additional controls, like unique indexes, or some validation checks!
And now you'll need a subfom to manage data. Wou will have a form (usually a single one) based either on tblBoardMembers or on tblBoardPositions, and a continous subform based on tblBoardMemberPositions, where all positions this member was assigned to, or all members who were assigned to this position (depending om main form you are usung) are displayed.

[Oregoncoder]

I appreciate everyone’s help. I fixed my issue by creating a field in the exit I’ve table that would store the memberID for that position. I was initially trying to go the other way and store the position in the Member table. So essentially backwards of how it worked best.

thanks for all of your patience.

[mike60smart]

If you want to keep a History of Who Held which Position, then you need to use the solutions suggested.