Auto-fill textboxes?

[flushfire]

I have this form here:

What I want to do is give the end-user the option to input a preset of subjects that is also customizable. One way I know of doing it would be using a table then appending it to the table which the subform Subjects is based off of, however i think that would skip the rules I had setup for it (it only accepts entries from the drop-down list, and the list is based off of multiple queries, the subjects available are unique for each student). Another would be to use VBA, but I don't know of a way to make VBA code customizable by the end-user (and of course it has to be shown in a way that it does not look like code at all). What would be the best solution for this? TIA

 

[ROgden]

Bear with me for my thought foundation here. You would need to have a master 'Subject' table in order to retain all of the viable selections, a 'Student' table, and one additional table would be a 'StudentSubject' table.

With this foundation, a student would come into the form for the first time with the sub-form empty. Give them a small plus '+' sign above the sub-form that, when clicked on, will display the master list of Subjects (I'll talk more about that below). The student can then select the Subject(s) they want, click 'OK', then you can have VBA code behind run through the list, find the selected values, copy them into the 'StudentSubject' with t he student's ID, then refresh the sub-form.

For the subject selection, you would create a small modal, pop-up form that will could contain a List Box that allows multiple selection. When they click 'OK' on the pop-up form, the VBA code will cycle through the List Box items, and every time it finds a selected one, write the value out with something like:

Currentdb.Execute ("INSERT INTO tblStudenSubject (StudentID, SubjectID) VALUES (" & Form_frmPopUp.lstSubject.Selected(itemIndex) & "," & Form_frmMain.txtStudentID & ")

Where I think this differs from the form you have is that the sub-form would be the list of the student's subject, and not the master list of subjects.

You can also add icons in the main sub-form to remove subject they don't want.

Hope this helps. ~R

 

[flushfire]

Thanks for the suggestions! I never thought about using a listbox. I'll see if I can manage to use it to replace the solution I have right now. I made a little workaround, I made separate tables for each preset, then I append that table after applying checking with the same queries I use to generate the drop-down list which makes the a db bit cluttered since there are 8 presets.

 

[ROgden]

No problem. If you need further help, give me a shout. ~R