Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-15-2018, 01:54 AM   #1
rainbows
Newly Registered User
 
Join Date: Apr 2017
Posts: 65
Thanks: 11
Thanked 0 Times in 0 Posts
rainbows is on a distinguished road
unable o exit form with the status changing

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

rainbows is offline   Reply With Quote
Old 09-15-2018, 04:59 AM   #2
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 6,881
Thanks: 55
Thanked 2,195 Times in 2,106 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: unable o exit form with the status changing

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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
Old 09-15-2018, 07:19 AM   #3
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,719
Thanks: 318
Thanked 427 Times in 412 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: unable o exit form with the status changing

TBH, I would have thought it was better to set the status to Closed if the user puts in a Closed Date?

__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is online now   Reply With Quote
Old 09-15-2018, 04:44 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,449
Thanks: 13
Thanked 1,422 Times in 1,355 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: unable o exit form with the status changing

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 09-15-2018, 09:33 PM   #5
rainbows
Newly Registered User
 
Join Date: Apr 2017
Posts: 65
Thanks: 11
Thanked 0 Times in 0 Posts
rainbows is on a distinguished road
Re: unable o exit form with the status changing

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 is offline   Reply With Quote
Old 09-16-2018, 01:38 AM   #6
rainbows
Newly Registered User
 
Join Date: Apr 2017
Posts: 65
Thanks: 11
Thanked 0 Times in 0 Posts
rainbows is on a distinguished road
Re: unable o exit form with the status changing

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
rainbows is offline   Reply With Quote
Old 09-17-2018, 08:16 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,449
Thanks: 13
Thanked 1,422 Times in 1,355 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: unable o exit form with the status changing

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.


__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
After changing record source of subform shows status filterd masoud_sedighy Forms 2 11-01-2017 02:04 AM
Question Access consuming 100% CPU and unable to exit gracefully on Windows Server 2008 (MS Ac vaibhav1985 General 6 02-23-2015 06:58 AM
Changing a table's status field if another field changes thardyjackson Tables 4 07-02-2013 01:10 PM
changing outlook mail status jalldridge Modules & VBA 2 05-23-2005 08:18 AM
Changing text color at status bar aqif Forms 1 04-17-2002 04:45 AM




All times are GMT -8. The time now is 06:28 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World