Prevent move to next record IF ... (1 Viewer)

liddlem · Feb 14, 2017

I am busy updating a database and need to force the user to update information IF some data in a subform is incomplete, before being allowed to move to the next record.

Bear in mind that the record is not dirty unless the user changes something. So I cant fire a Before/After update event.

I have tried setting me.dirty in the 'on current' event, but this doesnt seem top work.

Any ideas?

Beetle · Feb 14, 2017

How do the users currently navigate the form? Do you have your own navigation buttons created so you can control the flow, or are they just using the built in Access navigation options?

sneuberg · Feb 14, 2017

Maybe you could disable the Next button (Edit: assuming you have one) in the On Current event if the subform field is incomplete (Null?) and enable it in the afterupdate of the subform field. I guess you would want to put a message in a label explain why the next button has been disabled.

You might want to give the setting of the form to dirty another try. Another forum member needed to do the same thing you are and that was working for him. Which form did you make dirty (main or sub)? What not try both?

liddlem · Feb 14, 2017

Thanks Beetle and Sneeuberg
Unfortunately, I need to display the parent as a split form.
So the problem occurs if they select another record from the split form.

Beetle · Feb 14, 2017

This is not fully tested, but you might give it a try. Replace the text in red with your actual field names. Note that in this case I declared the lngRecordID variable at the top of the module, not inside the Current event. Might need some other tweaking but it did work with some minor testing.

Code:

Option Compare Database
Option Explicit
Dim lngRecordID As Long

Private Sub Form_Current()

If Nz(lngRecordID, 0) = 0 Then
    lngRecordID = Me.[COLOR="Red"]PrimaryKeyID[/COLOR]
ElseIf lngRecordID <> Me.[COLOR="red"]PrimaryKeyID[/COLOR] Then
    With Me.RecordsetClone
        .FindFirst "[COLOR="red"]PrimaryKeyID[/COLOR] = " & lngRecordID
        If Not .NoMatch Then
            If IsNull(![COLOR="red"]SomeField[/COLOR]) Then
                MsgBox "Previous record does not validate. Please complete before moving to another record."
                Me.Bookmark = .Bookmark
            Else
                lngRecordID = Me.[COLOR="red"]PrimaryKeyID[/COLOR]
            End If
        End If
    End With
End If

End Sub

liddlem · Feb 14, 2017

Thanks Sean
With a little tweaking that has done the trick.

Minty · Feb 14, 2017

Just as a follow up - as far as I'm aware you can't set a form to Me.Dirty = True, it a property set by a change on the form.

Prevent move to next record IF ... (1 Viewer)

liddlem

Registered User.

Beetle

Duly Registered Boozer

sneuberg

AWF VIP

liddlem

Registered User.

Beetle

Duly Registered Boozer

liddlem

Registered User.

Minty

AWF VIP

Similar threads

Users who are viewing this thread