Form Design Issue

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all
I woul like to know the best way (in terms of coding, complexity, etc.
to manage the following design issue, using MSAccess 2000 (or 2002)

I have three tables
1. Athlets: generic personal info such as name, address, telephone, etc
2. Sport: a list of different sport disciplines (e.g., running, highjumping, etc.
3. AthletsxSport: it models the M:N relation between Athlets and Spor
since an athlets can be associated to several sports

Thus I have the following relations

Athlets (1) -- (N) AthletsxSport (N) -- (1) Spor

Sport Table is pre-loaded with all available sport disciplines, thus
don't have to manage unavailable sport discipline

AthletsxSport includes two fields that are the foreign keys of Spor
and Athlets tables

The question is: how can I efficiently manage data entry for Athlet
and AthletsxSport tables? with a major emphasis on how to manage data entry
for N sport discipline in one shot, in terms of form, subforms, etc.

I would like to
1. insert athlets data and the associated sport disciplines in one form
1. use one or more combobox (???) to show (and choose from) all availabl
sport records (it allows me to automatically manage sport list changes)
2. get all sport disciplines associated to an athlets in one sho
(I mean, without opening N times a subform, but all at once)
3. avoid duplicated sport discipline for an athlet as soon as they ar
chosen
4. easily be able to change/delete/add the sport discipline associate
to an athlet

That's all: is it too much? for me is enough
I hope someone can point me in the right direction. Thanks in advance
Roberto
 
You are going to need 3 forms (ex. frmAthlets, which is the main form,
frmsubSport and frmsubAthletsxSport ).

First you create frmAthlets based on a query (ex. qryAthletsSport). The
query is based on table tblAthlets, tblAthletsxSport AND tblSport. Select
all fields from all tables make sure you select relation alternative 3
(Select alla records from table tblAthletsxSport) as a relation type to all
the other tables.

I hope you understand what I mean because this is the key to this feature!

Then you create frmsubAthletsxSport based on table AthletsxSport. At last
you create frmsubSport based on table Sport.

Then add frmsubSport as a subform on form frmsubAthletsxSport linked by
SportID (tblSport) and fkSportID (tblAthletsxSport) where fkSportID is the
foreign key.

At last you add form frmsubAthletsxSport to frmAthlets linked like this:

AthletsID (tblAthlets) --- fkAthletsID (tblAthletsxSport) AND fkSportID
(qryAthletsSport) --- fkSportID (tblAthletsxSport).


I don´t know if I was cleare enough so pleas don´t hessitate to ask again!

// Niklas
 
Hi Niklas
thank you for your answer
I understood what you wrote; as a matter of fact I don't need th
sub-form AthletsxSport because the purpose of AthletsxSport table i
to implement relation M:N between Athelts and Sport, thus it doe
not contain useful information for the final user.
How does it change your solution
Moreover, how do I drive the choice of sport discipline at add/mo
time in your solution
Thank you
Robert

----- Niklas Östergren wrote: ----

You are going to need 3 forms (ex. frmAthlets, which is the main form
frmsubSport and frmsubAthletsxSport )

First you create frmAthlets based on a query (ex. qryAthletsSport). Th
query is based on table tblAthlets, tblAthletsxSport AND tblSport. Selec
all fields from all tables make sure you select relation alternative
(Select alla records from table tblAthletsxSport) as a relation type to al
the other tables

I hope you understand what I mean because this is the key to this feature

Then you create frmsubAthletsxSport based on table AthletsxSport. At las
you create frmsubSport based on table Sport

Then add frmsubSport as a subform on form frmsubAthletsxSport linked b
SportID (tblSport) and fkSportID (tblAthletsxSport) where fkSportID is th
foreign key

At last you add form frmsubAthletsxSport to frmAthlets linked like this

AthletsID (tblAthlets) --- fkAthletsID (tblAthletsxSport) AND fkSportI
(qryAthletsSport) --- fkSportID (tblAthletsxSport)


I don´t know if I was cleare enough so pleas don´t hessitate to ask again

// Nikla
 
Hi!

Well you need the sub-form AthletsxSport to be able to relate (link)
sub-form Sport to the mainform Athlets. Because it´s table AthletsxSport
that is the link between the other two tables. Somehow you need to get
AthletsID AND SportID in to table AthletsxSport!

I don´t follow your other Q so I don´t know what you´r after, sorry!

If I understand you correct you want to change record source of the main
form Athlets at runtime showing just the athletics for a selected sport, or?


// Niklas
(Ill be off line until tomorrow so don´t expekt any answer today)
 
Hi Niklas
thank you again. I think I was not clear enough: let me try again

The AthletsxSport contains just a couple of foreign key to Athlets an
Sport table: I would like to hide that information to final user becaus
he/she does not know what they are; they understand a name not a code
Tha's why I wrote "I do not need the sub-form AthletsxSport": I wanted to sa
I do not want to show it to final user even if I know it is the way to relate th
other sub-forms: is it possible to hide sub-form AthletsxSport in the main form

The second question was: when I add a new athlet I should be able to provid
the final user with the list of available sports (all the records in tblSport); he/sh
will be allowed to select one only from there: should not I use a combo box
I hope I did better than before :o
Robert

----- Niklas Östergren wrote: ----
Hi
Well you need the sub-form AthletsxSport to be able to relate (link
sub-form Sport to the mainform Athlets. Because it´s table AthletsxSpor
that is the link between the other two tables. Somehow you need to ge
AthletsID AND SportID in to table AthletsxSport
I don´t follow your other Q so I don´t know what you´r after, sorry
If I understand you correct you want to change record source of the mai
form Athlets at runtime showing just the athletics for a selected sport, or
// Nikla
 
Back
Top