none
Field Requirement RRS feed

  • Question

  • I am sure this will turn out to be something simple but I am struggling with it.

    I have a data entry form that already has a bunch of programming set behind it and now I need it to do something else.  I have three fields that I need to address - Status, Approved Date and Approved by.

    I had a macro that if the Status was set to Approved then the Approved date would automatically be updated to now() and then both the date approved and status field were set to enabled No.  The new ask is that the if the Status is set to Approved (and now Denied) that the approval date is update and disabled but also that the Approved by field cannot be left blank. I have a public variable that is set in the database when my user logs in called LogInUser.  I thought it would be as easy as to set the value of the Approved by to the variable but it isn't doing it.  I don't get an error, it just doesn't update the field.  I then tried to write it out it VBA thinking that would help as I call that same variable during the form on load and on current events (so I know it sees the variable) still I cannot get it to populate the Approved By field when the status is updated to Approved.  Here is my code:

    Private Sub Status_Change()

        If Me.Status = "Approved" Then
            Me.DateApproved.Value = Now()
            Me.DateApproved.Enabled = False
            Me.ApprovedBy.Value = LogInUser
       
        Else
       
            Me.DateApproved.Value = Null
            Me.DateApproved.Enabled = True
            Me.ApprovedBy.Value = Null
       
        End If
       
       
    End Sub

    I would appreciate any help with this.

    Thanks - TJK

    Thursday, January 18, 2018 2:39 PM

Answers

  • the Status is set to Approved (and now Denied) that the approval date is update and disabled

    Private Sub Status_Change()
        If Me.Status = "Approved" Or Me.Status = "Denied" Then
             Me.DateApproved.Value = Now()
             Me.DateApproved.Enabled = False
             Me.ApprovedBy= LogInUser
         Else
             Me.DateApproved.Value = Null
             Me.DateApproved.Enabled = True
             Me.ApprovedBy= Null
         End If
     End Sub

    but also that the Approved by field cannot be left blank

    You'll need to build a form level Before Update event to check for this (untested aircode)

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.Status = "Approved" Or Me.Status = "Denied" Then
             If IsNull(Me.ApprovedBy)=True Then
                 MsgBox "You must fill-in the Approved field", VbInformation Or VbOkOnly, "Missing Mandatory Information"
                 Me.ApprovedBy.SetFocus
                 Cancel=True
             End If
         End If
     End Sub

    That all said, since you are populating the field in your status change event, how could it be blank?

    Also, you should include error handling in all your procedures.

    As for the variable not populating the control, what happens if you add a line like

    Debug.Print LogInUser

    just before the Me.ApprovedBy= LogInUser line?  What gets printed to the VBA's immediate window?


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net




    Thursday, January 18, 2018 3:27 PM

All replies

  • the Status is set to Approved (and now Denied) that the approval date is update and disabled

    Private Sub Status_Change()
        If Me.Status = "Approved" Or Me.Status = "Denied" Then
             Me.DateApproved.Value = Now()
             Me.DateApproved.Enabled = False
             Me.ApprovedBy= LogInUser
         Else
             Me.DateApproved.Value = Null
             Me.DateApproved.Enabled = True
             Me.ApprovedBy= Null
         End If
     End Sub

    but also that the Approved by field cannot be left blank

    You'll need to build a form level Before Update event to check for this (untested aircode)

    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.Status = "Approved" Or Me.Status = "Denied" Then
             If IsNull(Me.ApprovedBy)=True Then
                 MsgBox "You must fill-in the Approved field", VbInformation Or VbOkOnly, "Missing Mandatory Information"
                 Me.ApprovedBy.SetFocus
                 Cancel=True
             End If
         End If
     End Sub

    That all said, since you are populating the field in your status change event, how could it be blank?

    Also, you should include error handling in all your procedures.

    As for the variable not populating the control, what happens if you add a line like

    Debug.Print LogInUser

    just before the Me.ApprovedBy= LogInUser line?  What gets printed to the VBA's immediate window?


    Daniel Pineault, 2010-2017 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net




    Thursday, January 18, 2018 3:27 PM
  • Hi seebert,

    If we try to see your description then your first condition is as below.

    (1) if the Status was set to Approved then the Approved date would automatically be updated to now() and then both the date approved and status field were set to enabled No.

    (2) if the Status is set to Approved (and now Denied) that the approval date is update and disabled but also
        that the Approved by field cannot be left blank.

    I have some questions here,

    If you disable the Status as mentioned in first condition then how will you select Denied if you want to change it again? As mentioned in condition 2.

    In second condition you had mentioned that  ,'Approved by field cannot be left blank'

    As per your description, you are fetching that value from your database. so do you mean that your data in database is also stored NULL?

    If yes, you can try to check the value of Textbox as suggested by Daniel  Pineault.

    code:

    Private Sub Form_Load()
    Me.Status.AddItem ("Approved")
    Me.Status.AddItem ("Denied")
    Me.Approved_by = ""
    Me.Approved_Date = ""
    End Sub
    
    Private Sub Status_Change()
    Dim LogInUser As String
    LogInUser = "Demo_User"
     If Me.Status = "Approved" Then
             Me.Approved_Date.Value = Now()
             Me.Approved_Date.Enabled = False
             Me.Status.Enabled = False
         
      Else
             Me.Approved_Date.Value = Now()
             Me.Approved_Date.Enabled = False
             Me.Approved_by.Value = LogInUser
     
         
         End If
    End Sub

    Further, you can try to post the code for fetching the value of LogInUser.

    We will try to make a test on our side and check why it is not fetching the value.

    you can also try to modify your table to do not store null values for LogInUser.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 19, 2018 2:55 AM
    Moderator