Tricky Form Question

[Therese]

Hi all - I am not sure if this is even possible, but I know if it is someone here will know how to do it. I have a table with a list of dogs names. The dogs run in a team so I have another table with their run record. Currently, the form would have a text box where you select the dog's name and then another text box where you select the position in the team so it looks like this:

name1, position1
name2, position2

and so on. I would like to create the form where the text boxes are aligned to look like the team and the person would just have to select the dog's name that ran that position and the code behind would create a record that looks like the one above. For example, here is what the form would look like:

Position1 Position2
Position3 Position4
Position5 Position6

I would like the person to be able to select the dog's name in the text box labeled Position1 then move on to select the dog's name in Position2 and so on. I am imagining the record will still look like the one above. Meaning,

name1, position1
name2, position2, and so on.

Does anyone know if this is possible? Thanks so much for all your help. This forum has been so helpful in the past, I thought I would just put this out there.

Therese

 

[RuralGuy]

You may want to have a MainForm for the team name and a SubForm in continuous form mode for the positions and use a ComboBox that looks at all of the dogs bound to the name field.

 

[Therese]

Thanks. I am not sure I understand completely. I do have a mainform with many fields, but then the subform has the team. I have tried to have the position1, position2, etc... be combo boxes that have the dog names as their control, but when I choose a name for position1, it fills that name into all the text boxes. Likewise if I choose a different name for position2 ,etc.... I am assuming there will be some sort of code that says if I choose "spot" in the position1 textbox then it will create a record in the runtable that has "spot" in the dog name field and position1 in the position field, then when I choose "rover" in the position2 text box it will create a second record that has "rover" in the dog name field and position2 in the position field, and so on. Does that make sense?

Therese

 

[RuralGuy]

I am afraid that Access does not work that way. As I see it you need a table with 6 records for each team named Pos1...Pos6 (or whatever you want). It will have a Foreign key of the Team name ID and another field with a Foreign key to the DogNames table.

 

[Therese]

Thanks. I think I get it. I will give it a try and let you all know how it works. Thanks again. I would have wasted a lot of time.

Therese

 

[RuralGuy]

You're welcome and continued success with the rest of your project.