combo box in a sub-form

[ITempleman]

HI all,

I have created a form with a sub form that we will use to record the number of times a student comes to meet with a counsellor and why.

The top form has: studentID, name (f & l), program and academic year - this auto fills through a combo box using the student id and some code (I got the example from youtube - worked great as I am not a coder!)

The sub-form has date, name of counsellor, reason and a notes box. I used a query to set up the sub form - with the studentID as the link. I wanted to set up a combo box for the counsellor and reason (about 6 options in each) BUT when I do this it seems to auto fill all the rows with the same counsellor/reason.

I have been viewing lots and lots of youtube videos and no one seems to cover this problem. Should I use something other than a combo box and if so what, and if yes, how do I fix it?

Also - when the form autofills - is there a way it can automatically go to the sub form rather than having to tab through - but yet, I still need to, if the student is not in the main table (ie form another department) enter their data (I have an append query to update from the form to the table).

Eventually I hope to have it as a tabbed sub-form, where I will have forms for appointments, courses, grades etc for each student.

thanks in advance and I hope this makes sense.

Irene

 

[RuralGuy]

The symptom you describe is caused by using a Continuous Form and putting something in a field that is not bound to the RecordSource of the form.

 

[ITempleman]

is there a way I can fix it or should I change the design completely and not use combo boxes

 

[RuralGuy]

Do the two controls have fields to use as Control Sources for the Controls? You can still use ComboBoxes to fill in the controls.

 

[RuralGuy]

Did what I say make any sense? Forms do not hold data, fields in tables do. I would assume you wanted to preserve the selection the user made so it needs to be placed in a field in a table.

 

[arnelgp]

bind your comboboxes to the fields in your table.

 

[ITempleman]

I apologise for my complete ignorance to what you have said - I am trying really hard to pick things up from youtube videos and some really good books but even when I copy things exactly (using my record names!!) they do not seem to work - so far the one thing I have got right is the autopopulate using code within the 'on change' in the events tab.

In basic terms I have 5 tables:
2015_16 - this has all the information for my students
counselors - list of the 6 counselors,
reasons - list of the 4 reasons they come to the office
Tblappointments: this takes the info from the 201-15 table and includes: studentID, f & L name, program and year
DateSeen: this is autonumber, studentID, date_attended, counsellor, reason and notes

The tblappointment is used as the basis for the main form and when you enter the StudentID it autopopulates the other details -
the subform is the Dateseen, so I select the date from the calendar, and both the counsellor and the reason are combo boxes which are both linked back to their relevant table and then the notes area.

When I fill in the form the top works ok - but as soon as I select either the counsellor or the reason, it autopopulates multiple rows with the same data - if I try to change one or other it changes each row to the same name.

 

[RuralGuy]

The controls on the Datasheet need to have a Control Source. What is in there now?

 

[ITempleman]

whoop whoop I got it, I added in the control source under 'All". I thought linking it to the table when creating the combo box was enough. A small victory but eventually I will get the hang of this - one step at a time....

 

[RuralGuy]

Excellent. Keep up the good work! You can use the Thread Tools at the top of the thread to mark this thread as Solved.