Data entry problem using subforms

[Brobin]

As you will see I'm a beginner at Access 2007. I am trying to design a database from one that was designed by a "master" Access user but I'm not having the results he did. This db will be used to track students in a school program. I want to enter data using a main form with 3 subforms. I have four tables: StudentInfo, ContactHome, ContactEmployer, Courses_Stipends. Each table has StudentID as the Primary key, however only the StudentInfo table has the StudentID as an AutoNumber - all other tables have StudentID as a Number. A one-to-one relationship was established from StudentID table to each of the other tables. Each of the forms (which correspond to each table) are linked using the StudentID. However, when I enter information for one student into the main form and each of the subforms I end up with 4 different records generated for the same person! Can anyone please tell me what it is I am doing wrong!!! I've been struggling with this for a couple of months now and I'm getting nowhere.

[June7]

1. Master/Child links should be on the StudentID field alone. When I changed this, only one record is saved. For some reason, when I reopen the form, the subforms would not show data with the queries as RecordSource. I set all forms RecordSource to the tables and works. This means FirstName, LastName, MaineStreetID are not available in the subform RecordSources for display. If the subforms were sized so the form did not scroll, would not need to repeat them.
2. Set the StudentID textbox on each form to Locked Yes, TabStop No so users cannot even try to edit. You could even make them not visible. The FirstName, LastName, MaineStreetID fields should also be Locked Yes, TabStop No on the subforms. However, because of 1. will have to use alternative method to display info. Such as setting ControlSource equal to the corresponding fields of the main form.

Your subforms are too big, especially Courses_Stipends. It is so big because that table is not normalized. You can condense the subforms quite a bit. One thing that is unnecessary is the huge banner on each form that repeats what the tab says.

If you want an ampersand (&) to display, must double it, like: Course && Stipend. A single ampersand creates a shortcut key on the character that follows it.

Aside from these issues, fairly good start.

[Brobin]

Thank yo so much for getting back to me so quickly!

Re: 2: How do I edit the ControlSource for the FirstName, LastName, and MaineStreetID fields to equal the fields in the main form? Is there a code that needs to be written (I haven't begun to try VB yet!) The person entering the data really wants to have them show up on each subform.

3: I realize that the Course_Stipend table is not normalized (and therfore the subform is huge) but I do not have any other ideas on how to do that...any suggestions? I will remove the header from each of the subforms to make them smaller.

I'm trying to learn Access 2007 by reading books and doing online tutorials but I can see I have a very long way to go!

[June7]

2. One way is to set equal to controls/fields on main form:
=[Forms]![frmStudentInfo_Main]![FirstName]
=[Forms]![frmStudentInfo_Main]![LastName]
=[Forms]![frmStudentInfo_Main]![MaineStreetID]

3. more normalized would be (one-to-many relationship):
StudentCourses
StudentID
CourseID (SEI501, etc)
Memo
SemesterYear
Grade
Status
Course Notes
DateModified

StudentStipends
SemesterNum
StudentID
Amount
Notes
DateModified

These two fields might should be in StudentInfo_Status
12CreditsEarned
12CreditsEarnedDate

[Brobin]

Thanks again for your help with this. I figured out how to get the fields from the main form using the Expression Builder. However, I was not able to change the form record source to the tables because when I tried to enter information into a subform I got the message that there could not be duplicate index values (Student ID) so I had to go back to using the query. This works great now. As to the table suggestions, I'll give it a try. We need to keep track of when each student took which courses so that when they receive certain amounts of credits they receive a stipend. Very confusing.

Thanks for getting me this far, it has been a great help.

[June7]

I had no problem changing the form record sources to the tables. Did you change the Master/Child link properties?

[Brobin]

Yes, I changed to use the StudentID as the linked fields. I then went in and tried to enter information into the first subform, Home Contact, the StudentID, First, Last, and MaineStreetID all filled in as expected. I filled in some address information and tried to click on the Employer Contact tab and that is when I got the message: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the indexes, or redefine the index to permit duplicate entries and try again."

I then changed all tables (except StudentInfo_Status) to have auto-generated IDs and used the StudentID as the foreign key to create a one-to-many relationship between the StudentInfo_Status table and the other tables. This appears to have worked. See attached.

The issues I'm having now is how to move from one subform to another without having to keep scrolling to the proper fields to enter new records...I'm reading over my Access 2007 Inside Out book.

[June7]

As I said earlier, with your previous version, I changed the RecordSources to the tables instead of the queries and the forms worked.

How do you plan to add additional records on the subforms? The record navigation bars are not active.

Not sure I understand: "The issues I'm having now is how to move from one subform to another without having to keep scrolling to the proper fields to enter new records". Do you mean you have to scroll the main form to get back up to the controls? That's because the forms are so large.

[Brobin]

I have gone in and made the changes to the record source to the tables and was able to get it to work properly. I can start a new record on the StudentInfo_Status form and then move through the various forms to enter information pertaining to that student. The issue I was asking about was how to move back to the StudentInfo_Status form once I click on the "New Record" button at the bottom of the form. Currently I get taken back to the field that I entered info in last.

I'm working on shortening the forms as well as normalizing the Courses_Stipend tables per your suggestions - still have a way to go on that. I really appreciate your help, thank you so much.

[June7]

You mean the 'New Record' button on the Navigation Bar? Use this code in the Current event of main form StudentInfo_Status


Private Sub Form_Current()
If Me.NewRecord Then
Me.LastName.SetFocus
End If
End Sub

You have a caption on tab of 'Course & Stipend Info'. The & is a special character. It designates the character that follows it as a shortcut key. If you want to actually show the &, must double it: Course && Stipend Info

[Brobin]

You mean the 'New Record' button on the Navigation Bar? Use this code in the Current event of main form StudentInfo_Status


Private Sub Form_Current()
If Me.NewRecord Then
Me.LastName.SetFocus
End If
End Sub

You have a caption on tab of 'Course & Stipend Info'. The & is a special character. It designates the character that follows it as a shortcut key. If you want to actually show the &, must double it: Course && Stipend Info

June7, I created a new db similar to the sample one I posted earlier and now I'm having the original issue when entering data in the subform telling me: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the indexes, or redefine the index to permit duplicate entries and try again."

Would you be kind enough to review the file and see if you can tell me what I'm doing wrong again?

[June7]

I think you are over-using Yes/No fields.
1. A student is either active or inactive - one yes/no field not two. By having a field for both Active and Inactive, both can be selected. You have date fields to indicate these actions. Yes/No not needed. If no DateInactived they must then be active.
2. You have a DateDropped field for EChOS, why also have a checkbox for Dropped EChOS? If they haven't dropped there won't be a date.
3. Similar for Academic Leave. Also, only one Academic leave can be documented.
4. Other unnecessary Yes/No: OSEP Data Form, Scholar Data Form Completed, Personal Disability/Family Disability (maybe - can student disability be both Personal and Family?), Plan of Study Complete, 12 Credits Earned, Earned 282.
5. Yes/No fields used where date could be more informative: 282 Reminder Sent, 282 Applied For

I am really troubled by the structure of Courses and Course_Details and NIRSOSEP_Semester tables and the relationship you have set for them. Courses is not normalized and doesn't even make sense to have it in the qryCourse_Details. Will every student have all these courses? Course_Details foreign key is StudentID. This table should be linked to StudentInfo_Status, not to the foreign key StudentID field of Courses.

The subfrmCourse_Details is attempting to allow data entry to two tables (Course_Details and NIRSOSEP) that are related to StudentInfo_Status. That won't work, each table needs own subform. Set subfrmCourse_Details RecordSource to table Course_Details.

Then the subsubform subfrmNIRSOSEP_Semester is really confusing me. The table NIRSOSEP_Semester has a compound primary key composed of the fk StudentID and Semester, yet you don't use the Semester field in Master/Child linking. Either link on the StudentID pk/fk fields with StudentInfo_Status or link to record in Course_Details using both fields in both tables in the link. I try to avoid compound keys. To do so would need a unque ID field in Course_Details and that would be the fk in NIRSOSEP_Semester.

Values like TotalStipendAmount and TotalCountCompletedCredits should be calculated when needed, not saved to a table. Is table NIRSOSEP even needed? Could the non-Semester fields be in StudentInfo_Status?

Why does NIRSOSEP have multiple fields for Semester Stipends and then there is NIRSOSEP_Semester set up to have record for each semester and stipend? Is NIRSOSEP an abandoned approach and that is why those textboxes are not visible on the form?

[Brobin]

I think you are over-using Yes/No fields.
1. A student is either active or inactive - one yes/no field not two. By having a field for both Active and Inactive, both can be selected. You have date fields to indicate these actions. Yes/No not needed. If no DateInactived they must then be active.
2. You have a DateDropped field for EChOS, why also have a checkbox for Dropped EChOS? If they haven't dropped there won't be a date.
3. Similar for Academic Leave. Also, only one Academic leave can be documented.
4. Other unnecessary Yes/No: OSEP Data Form, Scholar Data Form Completed, Personal Disability/Family Disability (maybe - can student be both Personal and Family), Plan of Study Complete, 12 Credits Earned, Earned 282.
5. Yes/No fields used where date could be more informative: 282 Reminder Sent, 282 Applied For

I am really troubled by the structure of Courses and Course_Details tables and the relationship you have set for them. Courses is not normalized. Will every student have all these courses? Course_Details foreign key is StudentID. This table should be linked to StudentInfo_Status, not to the foreign key StudentID field of Courses.

FYI: I am replicating a previous database for another program - the person who created it no longer works for us, and therefore I can not ask why it was created this was.

1.-5. I see what you mean about overusing the Yes/No fields. I'll try to get them to agree to change this. Thanks for the suggestions.

As to the structure of the Courses table, yes, all students will be taking at least 14 of the listed courses to complete the program. The Course_Details table keeps track of each course, Semester/Year they took it and the grade in order for the student to receive the stipend (to pay for the course). There needs to be a running total of this for yearly reports for the program.

My biggest problem right now is that I can not enter information into the subforms without it telling me that there are duplicate StudentID values in the records. You helped me on the Sample db I sent you before but for some reason I can not replicate it.

[June7]

Apparently I was editing my post while you added yours. Might look at it again.

Tell 'them' that the checkboxes with associated date fields create a situation where data can become 'out of sync'. If user enters date they have to remember to also check the box (and vice versa). Or else need to control this data integrity with VBA code. Saving dependent data is bad design. Makes data unreliable for filter/sort actions.

Even with my 20" monitor, the form is big enough to cause it to scroll up when moving to some tabs. I was able to do some more rearranging to eliminate that (because it was driving me nuts). App window has to be full screen to allow entire form to show. I suggest another tab for the data from StudentInfo_Status to compress the form even more.

Need to set TabStop to No for LastName on EChOS tab.