Got So Far

[gary.newport]

I have attached my database for anyone to have a look at.

This is a registration system for clubs and activities at my school. The basic system works (data structure and the basic data forms) but I am having an issue with one form in particular (or two forms since it comprises of a parent and child form).

Registration Form and Register Students form the primary input form; allowing any staff member to select their name, their activity and then as many students as have attended the activity/club. This last part needs to be a continuous form since more than one student will be attending.

I have based these on existing queries or direct within SQL since I wanted to use a more understandable data structure for the fields than the structure available directly from the tables (So, I'd rather have "Mr Newport - Business Studies" than "Newport" in the field - or worse still "Mr").

I have broken a golden rule of mine and used descriptive names for my forms and tables (haven't gotten around to doing this with the queries) but feel that the original reason for not doing so (avoiding spaces to make SQL and VBA coding easier) is probably still valid and I should not have listened to others.

Can anyone help by making my registration form work? If they could and give me pointers on what they have done/where I went wrong I would be ever so grateful.

 

[pbaldy]

I would expect the subform to be bound to the table that joins students and activities. It's bound to the same list of students the combo gets its selections from.

Personally I think the new form and table names are horrible. Spaces and symbols in names are a pain. Your queries are in what I've heard called CamelBack format, which is what I use. I'd go back to following your golden rule.

 

[HiTechCoach]

I have broken a golden rule of mine and used descriptive names for my forms and tables (haven't gotten around to doing this with the queries) but feel that the original reason for not doing so (avoiding spaces to make SQL and VBA coding easier) is probably still valid and I should not have listened to others.

I would listen to these people:

The Ten Commandments of Access

 

[gary.newport]

I would expect the subform to be bound to the table that joins students and activities. It's bound to the same list of students the combo gets its selections from.

Personally I think the new form and table names are horrible. Spaces and symbols in names are a pain. Your queries are in what I've heard called CamelBack format, which is what I use. I'd go back to following your golden rule.

I agree about the names; it feels wrong! I will revert back.

So, I join the form to the Students and Activities Table (soon to be tblStudentActivities) but keep the combo attached to the existing query?

How does this make this work continuously and how can I make the query listg selectable?

 

[gary.newport]

I would listen to these people:

The Ten Commandments of Access

Well, unless I am missing something I have only broken Commandment 3; and felt incredibly uncomfortable whilst doing so.

I should defend myself by stating that I broke with over 20 years of programming convention with reservation and only because a book I recently read implied that this was the new way of doing things (don't want to be a dinosaur).

 

[pbaldy]

Yes; in my view, the main form would be bound to the staff/activity table and the subform to the student/activity table. With Master/Child links on activity, you can add students as desired. You might also consider a listbox and code:

http://www.baldyweb.com/MultiselectAppend.htm

 

[gary.newport]

Yes; in my view, the main form would be bound to the staff/activity table and the subform to the student/activity table. With Master/Child links on activity, you can add students as desired. You might also consider a listbox and code:

http://www.baldyweb.com/MultiselectAppend.htm

Okay, the first part makes sense, since these are the tables that need to be updated. I have now made the name changes and implemented the above; exlcuding the coding since I cannot see how this would work nor where to put it.

I note that when I run the form the Staff and Activity combo boxes are easily selectable but I cannot select anything from the student one at all. I know that once I have this working I will have to attach code to update the relevant tables and will get there soon, but currently I need to resolve the unselectable students and the continuous model.

I am grateful for all this help. I am VERY rusty and RDbMSs are the worst thing to get out of practice with. I am learning though; and fast.

Have attached latest version.

 

[pbaldy]

The subform is bound to the table, but the combo is still bound to a now non-existent field from the query. It should be bound to the Student field.

 

[gary.newport]

The subform is bound to the table, but the combo is still bound to a now non-existent field from the query. It should be bound to the Student field.

Thanks Paul

So, I now have a working combobox. I have, however, lost the very structure I needed ("Donald Duck - Year 7") and gotten simply the Student field value from within the tblStudentsActivities table (which links back to the table tblStudentDetails). This also means that the value given are repeated for every instance they appear within this linking table.

Can I not simply use a query to display the data (as I have for Staff and Activity) and then use code to place the values into the correct areas once the form is closed?

Hohum, I am getting VERY confused now. Head hurts. Going to annoy wife until I feel better and then try again.

 

[gary.newport]

I lied!

The Staff Member and Activity fields are driven directly by SQL and store the key fields for those areas. I tried that for the Students field but, though I had the structure I wanted, I could not make it work as a continuous form; hence all this.

I know you know all this but now I know you know I know. Ooooo, my head!

 

[pbaldy]

That's all your data was storing anyway, wasn't it? That's appropriate design; you store the key value and then get the name with a query join for reports and such. When I tested, the numeric value was saved but the form displayed the text.

I would also expect that if I took part in 5 activities, my student ID would be repeated 5 times in that table.

 

[gary.newport]

That's all your data was storing anyway, wasn't it? That's appropriate design; you store the key value and then get the name with a query join for reports and such. When I tested, the numeric value was saved but the form displayed the text.

I would also expect that if I took part in 5 activities, my student ID would be repeated 5 times in that table.

Okay, now a little confused since my form is only showing the value field.

The reason why duplicates are not desirable is that this list is to be selected from by the tutor to register attendees and so would not expect nor want to see the same student appearing lots of times. We have 1200 students, with over 20 activities each week. A student can move between activities week after week and so we could end up with lots of duplicated names in this field. It feels better if I linked the combo box to the Student IF field in tblStudentDetails and then stored this in the tblStudentActivities table on closure of the form.

But then I return to the issue that I have bound the combobox to the Student ID field and therefore only seeing the Student value.

 

[pbaldy]

In my test, I left the row source of the combo as it was (duckling query I think). I just changed the control source. In essence, that query is getting its selections from the student table.

I think I get what you mean. I would expect to be in the table more than once, but only once for any given activity? Unless you want to lose the history and only show them in a single activity?

 

[gary.newport]

In my test, I left the row source of the combo as it was (duckling query I think). I just changed the control source. In essence, that query is getting its selections from the student table.

I think I get what you mean. I would expect to be in the table more than once, but only once for any given activity? Unless you want to lose the history and only show them in a single activity?

I told you I was learning fast! I have gotten it working (along with your enormous help, obviously). The form uses the tblStudentsActivities as its ControlSource so that there is a link between the parent and child forms. However, the combobox;s controlsource is in fact the tblStudentDetails and this has allowed me to create the SQL for the names. I now have the names as required (and can swap between first name or surname listing) but the key value is the studentID.

The reason for only having the student appear in this list once is because the tutor is only selecting (registering) the students attending THAT instance of the activity; previous history is of no consequence for registration - simply a list of every student in the school.

The fun part now is that I have to update the relevant tables with the correct data:

Into tblActivityStaffDate needs to go the Activity ID, Staff ID and Date
Into tblStudentsActivities needs to go the key field from the tblActivityStaffDate and the Student ID

Activity ID, Staff ID, Date and Student ID are all from the two forms whilst tblActivityStaffDate's ID field will be created upon a new record creation; so it's transferance has to occur once the first three entries have been stored.

Any help?

 

[pbaldy]

In my testing, I bound the main form to the staff activity table and the subform to the student activity table, and set the Master/Child links to the activity field. I also bound the controls on the main form to their respective fields in the staff table. At that point, when I made selections in the main and subforms, those selections were saved in their respective tables.

To prevent duplication, you can put code in the before update event of the student combo to check to see if the chosen student is already in the selected activity, and if it is stop the update (Cancel = True). You could use a DCount or open a recordset, whichever you feel comfortable with.

 

[gary.newport]

In my testing, I bound the main form to the staff activity table and the subform to the student activity table, and set the Master/Child links to the activity field. I also bound the controls on the main form to their respective fields in the staff table. At that point, when I made selections in the main and subforms, those selections were saved in their respective tables.

To prevent duplication, you can put code in the before update event of the student combo to check to see if the chosen student is already in the selected activity, and if it is stop the update (Cancel = True). You could use a DCount or open a recordset, whichever you feel comfortable with.

Any chance you could send me your test file back because I believe I can see what you have done but not utterly sure.

 

[gary.newport]

Ignore me! It all works.

Thanks Paul; you are a God Send!

The solution, like so much, is simpler than I could see. Thanks again.

 

[pbaldy]

Which one?!? I have like 12 copies of it here!

I think this one is set up that way.

 

[pbaldy]

Oops; too slow. Glad we got it working for you.