Form Before Update

[D Willett]

Hoping I get one of those moments as yesterday, solving the problem before I get an answer, my code below is not capturing the fact that the field "DelayReason" is null.
I set the field to Null on opening the form so that I get a new "DelayReason" every time the "BookOut " date is changed.
As it is now, the code prompts the user that the field is empty then carries on processing the rest of the code, therefore the user doesn't have an option to populate "DelayReason" !!

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!DelayReason) Then
    MsgBox "Please Enter A Delay Reason", , "Delay"
    Me.DelayReason.SetFocus
        
        
        
      If MsgBox("Changes Have Been Made To This Record." _
        & vbCrLf & vbCrLf & "Do You Want To Save These Changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
            'DoCmd.Save
                Dim DB As DAO.Database
                Dim RST As DAO.Recordset
                Set DB = CurrentDb
                Set RST = DB.OpenRecordset("tblDelays")
                    RST.AddNew
                    RST!JobID = Me.JobID
                    RST!OriginalDate = tempDate
                    RST!NewDate = Me.BookOutDate
                    RST!DelayReason = Me.DelayReason
                    RST.Update
                Set RST = Nothing
                Set DB = Nothing
                    Me.DelayCount = Me.tmpDelayCount + 1
    
        Else
            DoCmd.RunCommand acCmdUndo
    End If
End If
End Sub

[HansV]

Try this:

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If IsNull(Me!DelayReason) Then
        MsgBox "Please Enter A Delay Reason", , "Delay"
        Me.DelayReason.SetFocus
        Cancel = True
    ElseIf MsgBox("Changes Have Been Made To This Record." & _
            vbCrLf & vbCrLf & "Do You Want To Save These Changes?", _
            vbYesNo, "Changes Made...") = vbYes Then
        Dim DB As DAO.Database
        Dim RST As DAO.Recordset
        Set DB = CurrentDb
        Set RST = DB.OpenRecordset("tblDelays")
            RST.AddNew
            RST!JobID = Me.JobID
            RST!OriginalDate = tempDate
            RST!NewDate = Me.BookOutDate
            RST!DelayReason = Me.DelayReason
            RST.Update
        Set RST = Nothing
        Set DB = Nothing
        Me.DelayCount = Me.tmpDelayCount + 1
    Else
        DoCmd.RunCommand acCmdUndo
    End If
End Sub

If DelayReason is Null, Cancel is set to True to cancel the update.
ElseIf is used to ensure that the rest of the code is not executed if DelayReason is Null.

[D Willett]

Mmm
If I open the form and just close it without changing or adding data, I get the prompt:

MsgBox "Please Enter A Delay Reason", , "Delay"

Then I get a warning of "You Can't Save record....
PNG attached ??

[HansV]

If you get the prompt even if you haven't entered or changed any data, it must be because you have set the value of one or more bound controls in code. Otherwise, Access wouldn't see the record as changed.

[D Willett]

Yes, I set the DelayReason to Null: The form is bound on all fields also....

Code: Select all

Private Sub Form_Open(Cancel As Integer)
Me.tempDate = Me.BookOutDate
Me.tmpDelayCount = DCount("JobID", "tblDelays", "JobID=" & Me.JobID)
Me.DelayReason.SetFocus
Me.DelayReason.Value = Null

End Sub

[HansV]

This means that the record will always be seen as "changed" even if the user hasn't changed anything. In general it is not a good idea to pre-fill a record...

You could place a command button cmdClose on the form with caption Close and the following event procedure:

Code: Select all

Private Sub cmdClose_Click()
    DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

If that doesn't help, we'll need more complicated code.

[D Willett]

The "Please Enter ..... " prompt still fires. I'll revisit the form and try to rebuild it another way.

I might look at the Forms Dirty Event or better still the ctrl [BookOut] dates dirty event, as thats the important one.
If BookOut changes then record a reason.

Cheers

[HansV]

Try to avoid setting values unconditionally.

[D Willett]

I thought I had it for a minute....: Take out the If Dirty line it works

Code: Select all

Private Sub BookOutDate_AfterUpdate()
If Me.Dirty = True Then

    If MsgBox("Do You Want To Save The Date Changes?", vbDefaultButton1 + vbYesNo) = vbYes Then
        If ((IsNull(DelayReason) Or (DelayReason) = " ")) Then
            MsgBox "Please Enter A Delay Reason", vbOKOnly
            DelayReason.SetFocus
            Exit Sub
    End If

   
    Dim DB As DAO.Database
    Dim RST As DAO.Recordset
    Set DB = CurrentDb
    Set RST = DB.OpenRecordset("tblDelays")
    RST.AddNew
    RST!JobID = Me.JobID
    RST!OriginalDate = tempDate
    RST!NewDate = Me.BookOutDate
    RST!DelayReason = Me.DelayReason
    RST.Update
    Set RST = Nothing
    Set DB = Nothing
    Me.DelayCount = Me.tmpDelayCount + 1
Else
    MsgBox "Dates Not Saved", vbOKOnly
    End If
End If

End Sub

[HansV]

Is that a question?

[D Willett]

Sorry Hans... just ready jump through the window with this ..tried to trap this so many different ways..Here's another version.

Code: Select all

Option Compare Database


Private Sub BookOutDate_AfterUpdate()

    Dim DB As DAO.Database
    Dim RST As DAO.Recordset
    Set DB = CurrentDb
    Set RST = DB.OpenRecordset("tblDelays")
    RST.AddNew
    RST!JobID = Me.JobID
    RST!OriginalDate = tempDate
    RST!NewDate = Me.BookOutDate
    RST!DelayReason = Me.DelayReason
    RST.Update
    Set RST = Nothing
    Set DB = Nothing
    Me.DelayCount = Me.tmpDelayCount + 1


End Sub

Private Sub Form_Close()

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[JobID]= " & Me.JobID
If rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
End Sub

Private Sub cmdSave_Click()

If IsNull(Me!BookinDate) Then Exit Sub
  If Me.BookinDate > Me.BookOutDate Then
    MsgBox "BookIn Date Cannot Be Greater Than BookOut Date", , "Date Error"
    Me.BookOutDate.SetFocus
    Cancel = True
    Exit Sub
End If

If IsNull(Me!DelayReason) Then
    MsgBox "Delay Reason Cannot Be Blank", , "Delay Reason"
    Me.DelayReason.SetFocus
    Cancel = True
    Exit Sub
End If
  
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name

End Sub

Private Sub Form_Open(Cancel As Integer)
    Me.tempDate = Me.BookOutDate
    Me.tmpDelayCount = DCount("JobID", "tblDelays", "JobID=" & Me.JobID)
    Me.DelayReason.SetFocus
    Me.DelayReason.Value = Null

End Sub

I only want to save the record into tblDelays when BookOutDate is changed because then it is a delay.

[HansV]

You'll keep on having problems if you set the value of bound controls in the On Open event of the form.

[BenCasey]

Sorry Hans... just ready jump through the window with this ..tried to trap this so many different ways..Here's another version.

Code: Select all

Option Compare Database

Dave, just glancing through your code. Here are some notes that might help. I haven't looked at the logic as Hans is helping you with that.

1, I suggest that you always set Option Explicit so that everything is properly declared.
Option Compare Database
Option Explicit

2. What is this next bit about? If you are closing the form then why set the bookmark to something that is going to disappear the moment the form closes?
Private Sub Form_Close()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[JobID]= " & Me.JobID
If rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
End Sub

3, You cannot use Cancel in this construction. (appears twice) Try 'Exit Sub'
Private Sub cmdSave_Click()
If IsNull(Me!BookinDate) Then Exit Sub
If Me.BookinDate > Me.BookOutDate Then
MsgBox "BookIn Date Cannot Be Greater Than BookOut Date", , "Date Error"
Me.BookOutDate.SetFocus
Cancel = True
Exit Sub
End If
If IsNull(Me!DelayReason) Then
MsgBox "Delay Reason Cannot Be Blank", , "Delay Reason"
Me.DelayReason.SetFocus
Cancel = True
Exit Sub
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Close acForm, Me.Name
End Sub

[D Willett]

Hi Ben, thanks for the advice here.
I've kind of put myself in a Hamsters wheel with this and its now difficult to get out ...Hans is trying to steer me out ( as always ) and inspires me to see my errors, so I'm always grateful, just doesn't sink in straight away, he knows what I'm like.

Anyway, the purpose of the form.
The form's table is tblEST, activated from a main form.
The code:

Code: Select all

Private Sub Form_Close()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[JobID]= " & Me.JobID
If rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
End Sub

Returns to the record [JobID] which the form was called from.

So the bound form frmDelayDate has two purposes.
1> Allow the user to see BookinDate, BookoutDate from tblEST,(Thats why its bound).
It doesn't matter if BookinDate is changed because that may be down to the client. But if BookoutDate changes then I want a reason because the change of this date will be down to our workshops.

2> When Bookout date changes, the record is automatically changed in tblEST because thats the underlying table of the form, no issue there, but!!.
When it does change, save the details of the changes to tblDelays, most importantly the reason, thereby giving me a history. So when I want to see the history of date changes I have them in the delays table.

In summary.
Open form frmDelayDate ( source tblEST )
Change BookoutDate.(making it Dirty)
Save contents of the form to tblDelays.(Do not allow Null in the field "DelayReason"

Code: Select all

    Dim DB As DAO.Database
    Dim RST As DAO.Recordset
    Set DB = CurrentDb
    Set RST = DB.OpenRecordset("tblDelays")
    RST.AddNew
    RST!JobID = Me.JobID
    RST!OriginalDate = tempDate
    RST!NewDate = Me.BookOutDate
    RST!DelayReason = Me.DelayReason
    RST.Update
    Set RST = Nothing
    Set DB = Nothing
    Me.DelayCount = Me.tmpDelayCount + 1

[D Willett]

Here goes.... I think I may have cracked it.

Form frmDelayDate(tblEST) is called.The open event is:

Code: Select all

Private Sub Form_Open(Cancel As Integer)
    Me.tempDate = Me.BookOutDate
    Me.tmpDelayCount = DCount("JobID", "tblDelays", "JobID=" & Me.JobID)
End Sub

The cmdSave event is:

Code: Select all

Private Sub cmdSave_Click()

'We might not have a BookOutDate at this point
If IsNull(Me!BookinDate) Then Exit Sub

'This tests if BookOutDate has changed, if not then Get Out because nothing has changed.
If Me.BookOutDate = Me.tempDate Then GoTo GetOut:

'Do not allow DelayReason to be Null
If IsNull(Me!DelayReason) Then
    MsgBox "Please Enter A Delay Reason", , "Delay"
    Me.DelayReason.SetFocus
    Cancel = True
    Exit Sub
End If

'Standard Date Check
If Me.BookinDate > Me.BookOutDate Then
    MsgBox "BookIn Date Cannot Be Greater Than BookOut Date", , "Date Error"
    Me.BookOutDate.SetFocus
    Cancel = True
    Exit Sub
End If

'If all conditions are correct, then create a new record in tblDelays
    Dim DB As DAO.Database
    Dim RST As DAO.Recordset
    Set DB = CurrentDb
    Set RST = DB.OpenRecordset("tblDelays")
    RST.AddNew
    RST!JobID = Me.JobID
    RST!OriginalDate = tempDate
    RST!NewDate = Me.BookOutDate
    RST!DelayReason = Me.DelayReason
    RST.Update
    Set RST = Nothing
    Set DB = Nothing

'Set the delay counter to add 1
Me.DelayCount = Me.tmpDelayCount + 1

'Make sure the record is saved also in tblEST
DoCmd.RunCommand acCmdSaveRecord

GetOut:
DoCmd.Close acForm, Me.Name

End Sub

The close event is:( To return to the calling record)

Code: Select all

Private Sub Form_Close()

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[JobID]= " & Me.JobID
If rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If
End Sub

I've set the field "DelayReason" to unbound so it is always Null on opening the form.

What do you thing guys? If you see I've not wrapped the code correctly in cmdSave then feel free to advise.

[HansV]

What is the record source of the 'main form'? tblEst?

[D Willett]

The main form is frmEST which the record source is qryEST( based on tblEST).
The main form is a continuous design where the user can only view records. If they want to change a record, or add one then there are dblClick events on the visible fields on frmEST.

[BenCasey]

Dave,
Your code is simply wrong.
If you are setting the recordset to it source clone, then the me. refers to the current open form running this code. Therefore when you close the form (as you are doing) the bookmark disappears with it.
Also, you cannot use 'Cancel' in the sub as it hasn't been declared.
Try compiling your code and see what it throws up.

[D Willett]

Yes, compiled after adding Option Explicit, it raised the issues with "Cancel=True". Commented both out of the routine.

I'll check into the recordset clone routine also.
Thanks for pointing this out.

[HansV]

Instead of using a popup form, you could allow edits and additions on the main form, even if only temporary - you can set AllowEdits and AllowAdditions to True and to False in code. It would save you a lot of headaches.