Option Group datasource help, please

[kulstad]

I have a database that is a very simplistic sports pool database:

Tables
- Participants
- memberID (PK)
- memberName

- Teams
- teamID (PK)
- teamName

- Schedule
- gameID (PK)
- gameDate
- visitorID
- homeID
*It is possible to have multiple games on the same day

- Predictions
- memberID (PK)
- gameID (PK)
- selectedWinnerID
- actualWinnerID

=======================================================

I am trying to create a form that does the following
- combo box that has list of participants
- for each participant

- show option group of games (from Schedule table)
- schedule default is current day
- list Visitor and Home
- allow the user to select winner using Radio Button
- update Radio Button if user previously selected a winner for the game
- no default value (if user does not select a winner, there is no selection for that game)
- SAVE button inserts into Predictions table
​

The piece I am having problems with is creating the option group for each game in the schedule. Once I get each game presented in an option group, inserting into the Predictions table is a walk in the park.

Any help is very much appreciated.

 

[MarkK]

Hey Chris, what I see with this is that the option group is a control with a finite set of items that are fixed at design time, and so driving that list with data is not what that control was designed for. What you want to use to offer choices from a table are a listbox, combobox, or even a subform. In those cases the selection list can grow and shrink without a re-design, and without a pile of code to hide/show/rename the options themselves.

What I can picture is offer two list boxes, the one on the left with a list of scheduled but unselected games, and the one on the right for selected games for the current user, and all that for the current day. Then, those lists are relatively simple to write queries for, and clicking on each list item can cause that item to jump to the other list. Handle the click event, update the table, requery the lists.

Anyway, that's one option, but my main point is that using the option group as a data-driven control is a bit of an uphill battle.

 

[kulstad]

Thanks for your reply. It's a shame that I can't use dynamic radio buttons (I've also read that there is a fixed number of objects that can be created on a form over its lifetime [768 I think it was], so that poses yet another horrible obstacle).

Would it be possible, then, to replace the option group with at DataTable (or similar object) where the table consists of the Home and Visitor names and ids (id columns would be hidden), and have the user select one field per record to choose the winner of the given game?

For example......

hometeam A ID | home team A name | visitor team B ID | visitor team B name |
hometeam C ID | home team C name | visitor team D ID | visitor team D name |

The user clicks "home team A name" and "visitor team D name" (ID columns are hidden) as their chosen winners, and the corresponding ID is assigned to the UPDATE query.

Does this sound like a viable alternative?

 

[MarkK]

What I recommended is that you look into a listbox, combobox, and/or subform.

But I'm not sure what you are working on there either. Are you looking at adding games to the schedule, or adding participants predictions? What you have there looks more like the former.