Split Form frozen after requery

[casinc815]

I have a split form used in the data entry process for school teachers. The data is related to students test taking and progress monitoring. Generally, there is a test date and some other information. The important point here is the test date is always the first field of the form and the focus is set to that date. This is also a mandatory field because reporting and graphing is driven off of the date field.



So a teacher sits at their desk, starts-up the application, finds a student, and navigates to the desired form. The teacher may have three sets of results to add for that student. I put an ADD RECORD button on the form. It inserts a record into the desired table using the known keys from the form and all other fields are blank for data entry.

The code for the Add Record push button is below.

Dim dbs As Database
Dim rst As Recordset

Dim StrSQL100 As String
Dim ThisTerm As String
Dim SchYr As String

Dim StdID As Integer
Dim TchID As Integer
Dim SchID As Integer
Dim SchYrID As Integer

StdID = Me!StudentID
TchID = Me!TeacherID
SchID = Me!SchoolID
SchYrID = Me!SchoolYearID

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("NewTerm", dbOpenTable)
ThisTerm = rst!Term

StrSQL100 = "INSERT INTO FountasP(StudentID, StudentName, TeacherID, SchoolID, SchoolYearID, SchoolYear, Term, Grade)" _
& "VALUES(" & Me!StudentID & ",'" & Me!StudentName & "'," & Me!TeacherID & "," & Me!SchoolID & "," _
& Me!SchoolYearID & ",'" & Me!SchoolYear & "', '" & ThisTerm & "','" & Me!Grade & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL StrSQL100
Me.Requery
DoCmd.GoToControl ("DateAdmin")
DoCmd.SetWarnings True

MsgBox "Record Added!"

rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing

ALL WORKS WELL, up to a point!

A teacher called me and indicated after the addition of a new record, no data can be entered. When I looked at the form, I can navigate from field to field but cannot enter data. This is/will be an interesting
problem. The code was simply straightforward for the addition of blank rows to be used for a particular student's data entry. Not sure where else to look?

The questions is, how to release the form for data entry? It appears to be frozen. All fields can be navigated to but, no data can be entered?

Your attention to this problem will be appreciated. - Jim

[June7]

Why is INSERT sql required as opposed to simple data entry into a bound form? The INSERT involves more than just the key fields. Why is student name saved and not just student ID? Is there not already a table with records to associate student and class - a class roster?

No idea why this should freeze any form. If you want to provide db for analysis, follow instructions at bottom of my post. Also, review link on debugging techniques at bottom of my post.

[Missinglinq]

Having trouble following your scenario, but are you sure that the 'freezing' is caused by the Requery, as opposed to the Form simply being Read-Only? What is the Form's RecordSource?

Linq ;0)>

[casinc815]

Having trouble following your scenario, but are you sure that the 'freezing' is caused by the Requery, as opposed to the Form simply being Read-Only? What is the Form's RecordSource?

Linq ;0)>

Linq:

Interesting comment? I went back and made sure the form was not Read-only. It wasn't.

The record source is the driving table plus four tables for reference. Hence this is a four table join created in QBE and all DML work is to the driving table.

The error occurs on all forms where the Add Record button was placed on a form. The reason was the same code was copied to each button and the table names changed accordingly. We (the students and I) were able to reuse the code and change the table names. There are 13 assessments that teachers grade K-6 administer and 3 CBT's from grade 2-10. So there are roughly 15 unique forms registering the uniqu data requirements from each assessment/test.

The primary key on the table is an identity, hence tblTeacher has TeacherID. This ID is applied for uniqueness and identifies each teacher. Even though the FK's are County, District, School, SchoolYear and Grade, the teacher ID identifies a unique teacher per grade. If a teacher handles more than one grade he/she will have more than one row in the teacher table. This model continues throughout the recording of tests and assessments. A student is identified in the same manner as a teacher with district, school, teacher, grade being the FK associated with a student. StudentID is the identifier for a single student per grade. There is a junction table associating teacher-student and that is how we identify a classroom structure.

The test recordings handles these keys and from the beginning of this thread, you can see the INSERT for a blank record is handled accordingly.

That would be my long winded answer to fill in any gaps.

We did check the property sheets and made sure the Allow's were all yes. The DataEntry is No. Ironic and not explainable. No one has changed those properties since the creation of the separate forms. Last night we recreated a Simple Form, along with a AddRecord button. The Add Record process works. A record is added to the bottom of the table and data is entered. We checked the property sheet and all is well. We checked the rights all is good, no locking and the table is updated properly. We compared it to the Split Form and the split form is frozen.

Interesting problem!

The next step I will have the student's perform will be to test a toggle from SimpleForm to SingleForm. The Split Form is a slick method of viewing the data of a student and then move directly into graphing within Access. Hence, the importance of the split form. Can this be done with reporting? Absolutely! But the users want a split form. They are spoiled and change is often difficult, to accomplish in schools.

Thanks to all who are reading this thread. I will try to keep everyone updated should a solution be found.

Have a good day! Jim

[casinc815]

Linq:

Your comment about Read Only is running around in my head. While the OpenForm command is set to acNormal,acFormEdit
Could there be something else I need to look for that may not be set correctly.

Very Interesting stuff!

The form may be in ReadOnly mode and do not know how to correct it.

The DoCmd.OpenForm("Frm_FountasP"),acNormal,,FullName, AcFormEdit is the current command.


Jim