unable o exit form with the status changing (1 Viewer)

rainbows

Registered User.
Local time
Today, 01:14
Joined
Apr 21, 2017
Messages
425
hi



the code below works great but I need some help to modify it

what I also need is that if the user enters the dateclosed and does not change the status field to closed the strMsg = "status field must be changed to closed " will prompt the user



Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)


Dim strMsg As String
    If [Status] = "Closed" Then
    
      If Len([targetDate] & "") = 0 Then
      
      '
          strMsg = "targetdate is mandatory" '& vbCrLf
            Cancel = True
        End If
          
        If Nz([Dateclosed], 0) = 0 Then
            strMsg = "Date Closed is mandatory"
            Cancel = True
        End If
        If Cancel = True Then
        
        
        If Nz([Action Taken], 0) = 0 Then
            strMsg = "action taken  is mandatory"
            Cancel = True
        End If
        If Cancel = True Then
        
       
        
        
          MsgBox strMsg
          
          End If
       End If
    End If


 
End Sub

thanks for your help

steve
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:14
Joined
May 7, 2009
Messages
19,169
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)


Dim strMsg As String
    If [Status] = "Closed" Then
    
      If Len([targetDate] & "") = 0 Then
      
      '
          strMsg = "targetdate is mandatory" '& vbCrLf
            Cancel = True
        End If
          
        If Nz([Dateclosed], 0) = 0 Then
            strMsg = "Date Closed is mandatory"
            Cancel = True
        End If
        If Cancel = True Then
        
        
        If Nz([Action Taken], 0) = 0 Then
            strMsg = "action taken  is mandatory"
            Cancel = True
        End If
        If Cancel = True Then
        
       
        
        
          MsgBox strMsg
          
          End If
       End If
    Else
       If [DateClosed].OldValue <> [DateClosed].Value And [Status] <> "Closed" Then
           Cancel=True
           MsgBox "You need to set the [Status] to 'Close' if you put a Closing Date"
       End If
    End If


 
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 08:14
Joined
Sep 21, 2011
Messages
14,050
TBH, I would have thought it was better to set the status to Closed if the user puts in a Closed Date?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:14
Joined
Feb 19, 2002
Messages
42,981
Having two fields that provide the same information violates first normal form and leads to data inconsistencies if your coding is even a little sloppy.

Since when closed date is not null, you know that the item is closed, there is no need for a closed flag.

The only time it makes sense to have both a flag and a date is when the flag is actually a combo rather than Y/N and indicates status - Open, Closed, Pending, whatever and StatusDate specifies the date of the status change.

If you foresee the potential for a third (or more state other than Open and Closed, make the change now to Status and StatusDate to give yourself more flexibility. If you can't see that far, just remove the ClosedFlag and use CloseDate to derive "closed" status.
 

rainbows

Registered User.
Local time
Today, 01:14
Joined
Apr 21, 2017
Messages
425
when I try to put an iff statement in the status field it will change the status to closed
but I can then just go to the next record , even if I did put in the action taken of targets date in which is wrong so not sure what I am doing wrong

steve
 

rainbows

Registered User.
Local time
Today, 01:14
Joined
Apr 21, 2017
Messages
425
hi
thanks for you help
after trying to do what everyone suggested
I did this and it is working
is the correct way to do it ?

Code:
Private Sub dateclosed_Exit(Cancel As Integer)

       If [Dateclosed].Value And [Status] <> "Closed" Then
       [Status] = "closed"
                 End If
End Sub

steve
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 04:14
Joined
Feb 19, 2002
Messages
42,981
No.

Most validation belongs in the FORM's BeforeUpdate event. That way it is executed no matter what causes a record to be saved. In some cases, you can use the control's BeforeUpdate event but that generally results in validation code being in two events because you can't for example verify "presence" in the control's BeforeUpdate event since control level events do not fire unless a control actually gets the focus.

A better solution would be:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)

        If IsDate(Me.[Dateclosed]) And Me.[Status] <> "Closed" Then
           Me.[Status] = "closed"
        Else
            MsgBox "Date closed and status conflict.",vbOKOnly
            Me.DateClosed.SetFocus
            Cancel = True
            Exit Sub
        End If
End Sub
However, As I mentioned earlier, this is not a good design.
If all you want is the most recent status, then you can use one Status field and one StatusDate field. However, if you want to track ALL status changes, then you must create a separate status table where you can insert one row for each previous status.

I keep the current status in the main table. When the status changes, I write the previous status to the status table and change the current status. Others, might always store all status' in the status table. That design choice complicates queries and processing and frequently results in queries that you need to be updateable not being updateable because you have to use a subquery with Max() to obtain the current status.
 

Users who are viewing this thread

Top Bottom