Subform, Joined Table, and Adding Records

[HeatherRowan]

I am working on building a database for student retention data. There are various bits of data I am collecting, but there are three data points where the student can have multiple values: holds, attributes, and reasons for leaving.

I do not want to use the MVF function, as I can not export the data properly to import into either Tableau or some other data visualization software. You can find my database here: Retentiondatabase I have removed any confidential information and I know very little about coding - I have used examples and tutorials to get the coding for this project.

I have four tables: Students, Holds, Majors, StudentHolds (which is the joined table). And I have the relationships set up as shown below.
I have a main form "Student Details" that will eventually display all the relevant information for the student. On this main form, I have the"qryHoldList subform" that shows all the different holds a student might have, separated by commas.
I also have a "frmStudentHolds" which holds the "frmsubStudentHolds" to properly display the multiple values/holds.

What I am trying to do is to have a cmd button "Edit Holds" to open the "frmStudentHolds" for the specific student to add/edit the student's holds. I can edit existing holds for the specific record, but if the student does not have any holds listed (and therefore no record on the StudentHolds table) I get the error "You must enter a value in the 'StudentHolds.StudentID' field." How can I get the frmStudentHolds to add a record to that table based on the StudentID? I feel as though I am missing something simple but I'm lost.

Any help would be wonderful. Especially since I will have to go through this process two more times for the attribute and reasons for leaving.

 

[June7]

INNER JOIN requires related records in both tables for data to display. Really makes no sense to have a main form RecordSource that is a query joining Students and StudentHolds tables. Also makes no sense that there are no fields on main form. Conventional approach would be to have main form bound to Students and subform bound to StudentHolds with combobox to select Holds. Alternatives:

Single form bound to StudentHolds with comboboxes to select student and hold.

Main form bound to Holds, subform bound to StudentHolds with combobox to select student.

Why not just use StudentDetails as main form with StudentHolds subform? This form arrangement can be used to add/edit records for both tables.

The CSV string of Holds really doesn't seem useful on a form. It is common to do this on a report.

 

[HeatherRowan]

INNER JOIN requires related records in both tables for data to display. Really makes no sense to have a main form RecordSource that is a query joining Students and StudentHolds tables. Also makes no sense that there are no fields on main form. Conventional approach would be to have main form bound to Students and subform bound to StudentHolds with combobox to select Holds. Alternatives:

Single form bound to StudentHolds with comboboxes to select student and hold.

Main form bound to Holds, subform bound to StudentHolds with combobox to select student.

Why not just use StudentDetails as main form with StudentHolds subform? This form arrangement can be used to add/edit records for both tables.

The CSV string of Holds really doesn't seem useful on a form. It is common to do this on a report.

The main form is bound to Students and will have fields on it from the Students table, the RecordSource for that table is a query that combines first name and last name together. The frmStudentHolds and frmsubStudentHolds are there to pull the data from the Holds table to the main form. The qryHoldList subform is there because my users barely understand Excel much less Access and want to visually see the holds all in one field.

I can't use a combobox to select a student, as there are over 2000 students that will be in the database initially and will increase each year. Believe me, if I could that would make my life much easier.

I have a single form bound to Holds, frmStudentHolds, with a subform bound to StudentHolds - FrmsubStudentHolds. And I do have a combo box that allows you to choose a hold, but the problem is binding the FrmStudentHolds to the student main form when a record does not exist on the StudentHolds table.

Also, I realized last night that I broke several functions when I uploaded the file to the PublicFolder. Here is the correct link to the database. Retentiondatabase Sorry about that.

 

[MajP]

I can't use a combobox to select a student, as there are over 2000 students that will be in the database initially and will increase each year. Believe me, if I could that would make my life much easierI can't use a combobox to select a student, as there are over 2000 students that will be in the database initially and will increase each year. Believe me, if I could that would make my life much easier

I did not read the whole thread, but do not know why you cannot use a combobox with thousands of fields. You may want to look at my FAYT class. I think one demo is with 10,000 records and pretty instantaneous.

Developing Custom Classes to Simulate User Controls

Access OOP examples wanted Some people have shown interest in custom classes lately, and was asked for some more examples. I use custom classes extensively, but the most important use in Access is the ability to extend control functionality and basically simulate building a user control. Once...

www.access-programmers.co.uk

 

[HeatherRowan]

I did not read the whole thread, but do not know why you cannot use a combobox with thousands of fields. You may want to look at my FAYT class. I think one demo is with 10,000 records and pretty instantaneous.

Developing Custom Classes to Simulate User Controls

Access OOP examples wanted Some people have shown interest in custom classes lately, and was asked for some more examples. I use custom classes extensively, but the most important use in Access is the ability to extend control functionality and basically simulate building a user control. Once...

www.access-programmers.co.uk

Thanks MajP, but the examples on your post are not what I am trying to do; the closest is the MultiSelectListBoxControl V2. Using the combobox as a control to select the student record would work for this project if there was a way to group the students in a logical manner to make finding the specific record easier. Even the FAYT wouldn't work because we don't know which student to look up.

To select and find the students I have a datasheet form with code to open the specific student's record next to the individual's name. I know there are different ways to navigate to the student but I've based this database off the Access Template "Students."

 

[MajP]

I will look at the DB. But you should be able to do what you are asking. I got to figure out what you mean to select a student when you do not know which student looking for.

 

[MajP]

Is there a way to zip the db and put it here? Cannot and normally do not go to Google drives.

 

[mike60smart]

Hi Heather

I would second June's approach. See the attached example.

 

[June7]

And I will repeat:

Makes no sense that frmStudentHolds has a RecordSource that joins Students and StudentHolds tables. The INNER JOIN requires related records in both tables for data to display. If a student has no holds then there will not be records in that dataset for that student. Also makes no sense that that there are no other controls on fromStudentHolds than the subform.