Form issue with change entries

[DJRhino1175]

I have a form which is linked to a subform. Whenever we go back and try and change an entry or put something in one of the fields after entering data in the subform, sometimes it will not accept the change or additional data. We click on the sidebar but the pencil stays there. I have actually click it 5 to 10 times and nothing. As soon as I press the ESC key it accepts the changes sometimes others it deletes the changes and goes back to what was in the field to start with. I created a new form and copied all my fields and relinked everything and I still have this issue. Has anyone ever had a similar issue and if so how did you fix it?

[twinnyfo]

DJ,

Is the subform in a parent/Child relationship with the main form? is the related key somehow one of the editable fields on the subform? Is there any code on the subform that performs any activity on the current record (OnCurrent property)?

Just tossing out some possibilities...

[DJRhino1175]

Subform is linked to Main form as a child. Currently no issues with entering data in the subform only the mainform. There is code on the "onCurrent" the code is as folows

Private Sub Form_Current()
 
    ReSizeSubform Me
 
End Sub

Which fires this:

Option Compare Database
Option Explicit
 
Const MaxRecs = 18
 
Function ReSizeSubform(frm As Form)
    ' Runs on the OnCurrent of the MAIN Form (FrmLine)
 
    Dim NoRecs As Integer
    Dim TotalHeight As Single
 
    With frm.subfrmDrawing.Form
        If Not .Recordset.EOF Then
            .Recordset.MoveLast
            NoRecs = Nz(.Recordset.RecordCount) + 1
            If NoRecs > MaxRecs Then
                NoRecs = MaxRecs
'                .ScrollBars = 0             ' Show scrollbar
'            Else
'                .ScrollBars = 0             ' end if
            End If
            TotalHeight = .Section(acHeader).Height + .Section(acFooter).Height + (.Section(acDetail).Height * NoRecs)
            If Not .Recordset.BOF Then
                .Recordset.MoveFirst
            End If
        End If
 
    End With
 
    frm.subfrmDrawing.Height = TotalHeight
    frm.Repaint
 
End Function
 
Function ReSizeForm(frm As Form)
    ' Runs on the After Update & After Delete of the SUBFORM (SubFrmDrawing)
 
    Dim TotalHeight As Single
    Dim NoRecs As Integer
 
    With frm
        If Not .RecordsetClone.EOF Then
            .RecordsetClone.MoveLast
            NoRecs = Nz(.RecordsetClone.RecordCount) + 1
            If NoRecs > MaxRecs Then
                NoRecs = MaxRecs
'                .ScrollBars = 0             ' Show scrollbar
'            Else
'                .ScrollBars = 0             ' end if
            End If
            TotalHeight = .Section(acHeader).Height + .Section(acFooter).Height + (.Section(acDetail).Height * NoRecs)
        End If
    End With
 
    frm.InsideHeight = TotalHeight
    frm.Repaint
 
End Function
 

[twinnyfo]

Very strange! Usually, changes to a main form don't affect anything on the sub-form, as long as your not making any changes to the related key (and vice versa).

It definitely sounds like when you make changes to the subform and return to the main that the record is being locked. Not sure if this will help, but try adding this to the Main Form's VBA:

Private Sub SubFormName_Exit(Cancel As Integer)
On Error GoTo EH
 
    Me.[SubFormName].Form.Refresh
 
    Exit Sub
EH:
    MsgBox "There was an error exiting the Subform!  " & _
        "Please contact your Database Administrator.", vbCritical, "WARNING!"
    Exit Sub
End Sub

This will force the Subform to refresh (and save) the data on the subform every time you return to the main form.

Let me know if that hepps!

[DJRhino1175]

Thanks, I'll let you know ASAP.

[DJRhino1175]

Ok, I got an error box that says:

There was an error exciting the subform! Please contact you Database Administrator.

When I clicked ok, went and attempted to change an entry and still wont "send to the table(Clicking sidebar).

[twinnyfo]

Sounds like the Error handling is doing what it is supposed to do. Now we gotta figure out what the issue is.

Change lines 8-9 from post #4 to:

    MsgBox "There was an error exiting the Subform!  " & vbCrLf & vbCrLf & _
    "Error Number: " & Err.Number & vbCrLf & _
    "Description:  " & Err.Description

Let me know what the error number/description are. I think I have some ideas, but want to confirm.

[DJRhino1175]

Error: 2465
Description: Microsoft Access cant find field '/1' referred to in your expression

[PhilOfWalton]

Unless this is a pure coincidence, the code submitted by DJRhino is identical to the code in a Db passed to me a couple of days ago for some help.

I have been unable to reproduce the fault mentioned above.
If it is the same person, he did report he had a corrupted record in one of his tables, but thought he had deleted it. There may still be a corruption in his BE

Phil

[DJRhino1175]

I was able to get the corrupted data out, I work with Phil Stanton this weekend to help with something else and we caught what it was and took care of it.

[twinnyfo]

And this error only occurs when you: 1) Open the main form, 2) edit data on the subform and then 3) try to edit data on the main form?

Does the subform allow you to add/delete records from the sub form? Does the same thing happen when you do?

Are the main and sub forms using data from the same table?

Any VBA on the subform afte any data updates?

[DJRhino1175]

Main form and sub form have two different tables. the subform does allow me to Add/Delete with no issues. The only links are the ID #'s
Here is all the code listed for After Update:

Option Compare Database
Option Explicit
 
Private Sub Apprentice_BeforeUpdate(Cancel As Integer)
 
    If Nz(Apprentice) = "" Then
        MsgBox "Please enter the Apprentice name, Select *None* if you do not have one!", vbCritical
        Apprentice.SetFocus
        Cancel = True
        Exit Sub
    End If
 
End Sub
 
Private Sub Form_AfterUpdate()
 
    If Shift = 3 Then
 
        If LineDate = Date And Hour(Now) >= 18 And Minute(Now) >= 30 Then
            LineDate = DateAdd("d", 1, Date)
        Else
            LineDate = Date
        End If
    End If
 
End Sub
 
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    If Nz(GaurdsInPlace) = False Then
        MsgBox "Please verify your guards are in place and check off this box!", vbCritical
        Cancel = True
        GaurdsInPlace.SetFocus
        Exit Sub
    End If
 
    If Nz(LineDate) = 0 Then
        MsgBox "Please today's date!", vbCritical
        Cancel = True
        LineDate.SetFocus
        Exit Sub
    End If
 
    If Nz(Shift) = 0 Then
        MsgBox "Enter Your Shift Number", vbCritical
        Cancel = True
        Shift.SetFocus
        Exit Sub
    End If
 
   If Nz(CboLineNoId) = 0 Then
        MsgBox "Enter the Line Number", vbCritical
        Cancel = True
        CboLineNoId.SetFocus
        Exit Sub
    End If
 
   If Nz(TechID) = 0 Then
        MsgBox "Enter Tech's name", vbCritical
        Cancel = True
        TechID.SetFocus
        Exit Sub
    End If
 
    If Nz(Apprentice) = "" Then
        MsgBox "Please enter the Apprentice name, Select *None* if you do not have one!", vbCritical
        Apprentice.SetFocus
        Cancel = True
        Exit Sub
    End If
 
   If Nz(SupervisorID) = 0 Then
        MsgBox "Enter Supervisor's name", vbCritical
        Cancel = True
        SupervisorID.SetFocus
        Exit Sub
    End If
 
    If Nz(ScheduledHrs) <= 0 Then
        MsgBox "Please Enter the hours you are scheduled to run on this line!", vbCritical
        Cancel = True
        ScheduledHrs.SetFocus
        Exit Sub
    End If
 
End Sub
 
Private Sub Form_Current()
 
    ReSizeSubform Me
 
End Sub
 
Private Sub form_Open(Cancel As Integer)
 
    DoCmd.Maximize
 
End Sub
 
Private Sub ScheduledHrs_BeforeUpdate(Cancel As Integer)
 
    If Nz(ScheduledHrs) <= 0 Then
        MsgBox "Please Enter the hours you are scheduled to run on this line!", vbCritical
        Cancel = True
        ScheduledHrs.SetFocus
 
    End If
 
    If Nz(ScheduledHrs) > 720 Then
        MsgBox "Please Enter the Correct hours you are scheduled to run on this line!", vbCritical
        Cancel = True
        ScheduledHrs.Undo
 
    End If
 
End Sub
 
Private Sub Shift_BeforeUpdate(Cancel As Integer)
 
    If Nz(Shift) <= 0 Or Nz(Shift) > 3 Then
        MsgBox "Please Enter Your Shift!", vbCritical
        Cancel = True
        Shift.SetFocus
        Exit Sub
    End If
 
End Sub
 
Private Sub subfrmDrawing_Exit(Cancel As Integer)
 
On Error GoTo EH
 
    Me.[SubFormName].Form.Refresh
 
    Exit Sub
EH:
     MsgBox "There was an error exiting the Subform!  " & vbCrLf & vbCrLf & _
    "Error Number: " & Err.Number & vbCrLf & _
    "Description:  " & Err.Description
    Exit Sub
End Sub
 

[PhilOfWalton]

Sorry to but in, but I think the problem is on the AfterUpdate of the main form where you immediately dirty the record by altering the LineDate. Hence the continual pencil mark.

There are 2 alternatives to try.
1) Play with the LineDate on the BeforeUpdate
2) Add the following code after the final "End If" in the AfterUpdate

   Me.Dirty = false
 

That should save the record

Phil

[DJRhino1175]

Phil
I added the Me/Dirty=False to the after update and went in and changed several records and each one took. I think this fixed it. I fixed the message in the before update so now it makes since.

[twinnyfo]

Good catch, Phil! I hadn't even had a chance to review the code.

[PhilOfWalton]

I personally think Option 1 is better, as the second option requires 2 write statements (twice the chance of an error an a longer operation)

Phil

[DJRhino1175]

Turns out this started causing issues also. So now I'm back to the drawing board. Going to look at option 1 now, but not sure what I can play with here. I do have the table putting in a date here automatically through the default function as Date(). So I tried 'blocking out this code, and if I made a change to a record it was causing an error that states "Run-time error '2115':
The macro or function set to beforeUpdate or Validation rule property for this field is preventing Microsoft Access from saving the data field.
So I click on debug and it takes me to Me.Dirty = False.

This issue only seems to effect data entered when the "3" is entered in the shift box, which ids controlled by the afterupdate portion that the Me.Dirty = False was entered. Any ideas?

[PhilOfWalton]

OK, assuming that the Shift Number always has to be entered, run the date calculation on the AfterUpdate of the Shift

Phil

[DJRhino1175]

Sweet, so far that is working. I had to remove the Me.dirty=False, because once I left the shift box it triggered the error. Once I did that I went through the whole process, closed the database down, then changed the info in the record and it excepted all changes with no issue.

Thanks again.