Adding records with a many to many reltionship

[str33ty]

Hello

Sorry if this ends up long-winded and complicated but i've been thinking for the last few days how i would do this and i still don't have a clue, so i was hoping that maybe you could help!

I am building a database for my local scout group. This DB is mainly to keep track of who has what badges and which ones i need to order. Each Scout can have many badges but each badge can be given to many scouts.

So far i have 3 tables:

tblScout
scoutID (autonumber), name, dob, address etc

tblBadge
BadgeID (autonumber) Badgename

tblScoutBadge
scoutID, badgeID, received (yes/no), instructors (yes/no), plus (yes/no)

The idea is that a Scout can get a badge, when this happens the scoutID and badgeID gets matched accordingly once I've ordered it and given it to them then it gets the received 'yes' and the instructors and plus are just advances of the badge.

This then allows me to run reports such as "has earned but not received".

However, the form i am having trouble with. I would like a form that has the name at the top and all the badges listed on labels. next to the labels 4 tick boxes (earned, received, instructors, plus). This is all well and good. What i really want to know is when i tick the 'earned' box, how do i get it to add the scout ID and badge ID to the tblscoutbadge?

Also would it be possible (i know its possible, more how is it done?) to have a form where i can navigate via the badges and add Scouts that way?

Thank you very much in advance!

 

[Tiro Cupidus]

Instead of labels listing all of the badges a scout has, you should use a subform that is tied to tblScoutBadge. The Child and Master Fields on the subform should be scoutID. The subform itself should have all the fields you want, using combo boxes with scoutID and badgeID so you can set their rowsource to display the scout or badge name instead of the ID.

To navigate by badge, you would do the same thing, but the Child and Master fields would be badgeID (your main form would be tied to tblBadge).

 

[str33ty]

you've confused me with the child and master things. Can i change what you've suggested?

So i have a main form which, for example has first name and last name.

I then have a subform which has all the badges listed (this would be easier then a combobox) and i just want to be able to tick or select yes or no for the 4 critiria. How would i do this? tie it to a query?

 

[georgedwilkinson]

Check out this article. It almost perfectly fits what you're trying to do:

http://allenbrowne.com/casu-23.html

 

[str33ty]

thank you very much - that helps a lot.will play with it sometime and get back to you if i have any questions!

 

[str33ty]

hmm, looking at it closely its not quite what i'm after. in that case you either have the disese or you don't, where as the badges have 4 stages. I've changed the tick boxes to a combo-box but i would still like a form with a list of all the badges with a combo box next to each badge so i could change the status quickly and easily from one child to the next without having to scroll through a combo box

 

[Tiro Cupidus]

I've attached an example database I think will help you.

 

[str33ty]

ahh thank you very much - but i found an example database using Artists and Albums which helped greatly. Thank you Tiro, its much appreciated!

Now [next problem!], i have a form and subform (very similar to the one that you supplied).

I have over 125 badges in the database, and it could get very confusing as to whether a little child has already got the badge (be it Twit of the Year, or Anteater Tamer!)

I would like to be able to sort the subform alphabetcially, so i could easily check to see if they have one, or change its status.

Attached is a screenie of the current form, as you can see the badges are in alphabetical order! How would i do this?

EDIT: Not to worry, i've found it myself!

 

[str33ty]

Me again!

I have a combo box with a list of scout names, in alphabetical order, but i also have Next and Previous record buttons which cycle through the records in scoutID number, which i don;t want. How do i get them to go to the next record alphabetically?

 

[Tiro Cupidus]

If you have the subform sorted by Scout Name, then using the navigation buttons should move you from record to record the same way. How did you solve your problem of sorting the subform?

 

[str33ty]

i went to the form properties and did 'Order By' but it doesn't seem to work for the buttons

 

[Tiro Cupidus]

I'm still not sure what your problem might be. I've modified the sample database I made, and attached it. Try looking at the Badges form and using the sort button on it, as well as the record navigation buttons in the subform to see if it functions in the way you're looking for.

 

[str33ty]

looks exactly the same., but can't work out what i've done wrong! i've attached my databse - its the frmEditScout form i'm having trouble with, if you have a spare second could you please have a look?

thank you kindly

 

[Tiro Cupidus]

I'm not sure why it's not sorting, but one way to fix it is to assign the Record Source of the form to a query instead of the table. In the query, you can specify to "ORDER BY surname, firstname".

 

[str33ty]

works a treat!

thank you!