Solved Form Field and SendKeys

[Marshall Brooks]

Hopefully this will take longer to explain than to solve ...

I have a field on my form named "Status" The form has drop-down values and the values are locked. So for example, on of the values is "Complete" and if someone types something like "AY" there is a message that than is not on the list and to choose a value from the list.

When "Complete" is selected, the Sub Status_AfterUpdate() generates several E-mails.

Because I couldn't find a better way to apply Sensitivity labels, the E-mail code uses SendKeys to set the label: https://stackoverflow.com/questions/72230105/changing-sensitivitylabel-in-outlook-365-email-with-vba
Obviously kludgey, but the database creates the E-mail and sends Alt-H, followed by "AY", followed by Arrows, followed by Enter.

Usually, it works, sometimes it sends the code to the database, which means the status field gets Changed to "AY" and then gets Enter, which results in the pop-up to select a value from the list.

Is there anywhere I can add code so that if the value of the field is AY, to use the previous value?

Is it as simple as adding a check to the BeforeUpdate procedure?

The record is not saved, so the "Complete" is the pending value but not the current value, but that is what I want the field to remain as.

Thanks in advance.

 

[Minty]

Outlook - It appears you can set it in VBA

MailItem.Sensitivity property (Outlook)

Office VBA reference topic

learn.microsoft.com

I'm afraid I don't understand the rest of your question.
Perhaps a picture and some idiot guide arrows about the form items needing update might help?

 

[Marshall Brooks]

@Minty - I don't think the Outlook item can be set via VBA. It works (differently) for Excel and Word. It is supposed to work for Outlook, but it gives you an error. The labels are there, but from what I can tell the properties aren't exposed in Outlook to be changed by VBA.

Guide:
Here's the field on my form:

There is a dropdown to select values, and in properties for the field, the setting for Data>Allow Value List Edits is set to No, which means you can't set a value that is not in the list.

I reproduced my error by adding the following code to the Status_AfterUpdate() code as follows:

...
    ElseIf (Status = "Complete") Then

Dim WshShell As Object
            Set WshShell = CreateObject("WScript.Shell")
        '    WshShell.SendKeys "%h" ' Alt H - gets the home menu
            WshShell.SendKeys "AY" ' Sensitivity Label
                WshShell.SendKeys "{ENTER}"
            Set WshShell = Nothing


            Exit Sub
        ...

Alt-H opens the home menu in both Outlook and Access - apparently that code is running in Outlook and then AY is sent to Access, so I commented that line out.

Result (when I click Complete):

I tried adding:

Private Sub Status_BeforeUpdate(Cancel As Integer)
If Me.Status = "AY" Then
    Cancel = True
End If
End Sub

But it didn't work ...

 

[theDBguy]

Hello. On my phone right now, but I just gave it a try and was able to set the email's sensitivity to confidential where my default is normal. However, I didn't send the email to verify the assigned setting actually stuck. I just used the Display method to verify if the setting was changed.

All I used was:

With olMail
    .Sensitivity = 3
    .Display
End With

Sent from phone...

 

[Marshall Brooks]

Further clarification - I think there are two types of Outlook E-mail sensitivity.

What I am working with was added with Outlook M365 and was previously separately used as Azure Information Protection.

I didn't test, but I think what @theDBguy is referring to is more simplified Sensitivity information that was also in earlier versions of Outlook. I believe that CAN be set by VBA, but doesn't gain anything for my purposes.

 

[theDBguy]

Further clarification - I think there are two types of Outlook E-mail sensitivity.

What I am working with was added with Outlook M365 and was previously separately used as Azure Information Protection.

I didn't test, but I think what @theDBguy is referring to is more simplified Sensitivity information that was also in earlier versions of Outlook. I believe that CAN be set by VBA, but doesn't gain anything for my purposes.

You're probably right since I'm currently using Outlook 2016 and not M365.

 

[Marshall Brooks]

I just tested it and .Sensitivity Level 3 did not work for me in Outlook, but didn't give Me an error. Perhaps it is no longer used in Outlook M365.

To clarify, I am looking to change these options via VBA - well, specifically, I am able to change them via SendKeys, but I'd prefer a more robust solution:

On the message tab of the E-mail:

This can be accessed without using the mouse by pressing Alt-h, followed by "AY", followed by arrow keys, followed by Enter.

Or on the subject line:

 

[MarkK]

Cancelling BeforeUpdate does not undo the edit. You might need to do...

Private Sub Status_BeforeUpdate(Cancel As Integer)
    Cancel = Me.Status = "AY"
    If Cancel Then Me.Status.Undo
End Sub

 

[Marshall Brooks]

@MarkK - Sadly, that didn't work either. The field still changes to AY and I still get an error about AY not being a valid selection.

What I 'Might' be able to do is change the field Properties to Allow Values List Edits to Yes, and then change Status_AfterUpdate() to something like:
If Me.Status = "ListItem1" Then
Do Action1
Else if Me.Status = ListItem2" then
DoAction 2
...
Else ' Not in the list
Me.Undo
Exit Sub
EndIf.

I'm not quite sure if Me.Status.Undo is what I want either - b/c Let's say the record is saved with the status of "No Work Required". I change the status to "Complete". The code changes the status to AY. What I want is to change the status to "Complete" and I think your code would change it to "No Work Required". (but I could be wrong).

 

[Marshall Brooks]

Making progress ...

@MarkK Suggestion worked once I turned set Allow Value List Edits to Yes. That solves the issue with SendKeys sending AY.

However, I now have an issue where users can type anything they want into the field (except AY).

So what I need is some way to have beforeupdate say something like:
Cancel = Me.Status <> "Ready To Work" and Me.Status <> "Complete" ' and <> all the rest of the items.

I thought I had the solution with
Cancel = Me.Status <> Me.Status.RowSouce but I think that looks for the whole field, i.e. "Ready To Work"; ... "Complete".

 

[Marshall Brooks]

And I can't easily do it in AfterUpdate ...

Else
Me.Status = Me.Status.OldValue

removes the Complete Status

And
Me.Status = Me.Status.Undo gives me an error.

 

[Marshall Brooks]

What I think I can do, but I'm not exactly sure how to code it is something like this:

• In BeforeUpdate, Declare a String Array variable.
• Set the variable = Split(Me.Status.RowSource, ",")
• Set Cancel = True
• Loop through the array and if Me.Status matches the array variable, set Cancel = False.
• If Cancel = True, Undo the change.

 

[Marshall Brooks]

Solved!

This might not be the best solution, but it works for me:

• In the forms Design View, set the Field Properties to "Limit to List = No" (and optionally "Allow Value List Edits = No"). This allows anything to be typed in the fields and prevents SendKeys from causing the pop-up that will not allow code to run until a good status is selected. (We will handle non-allowed input in the field's BeforeUpdate Event.
• Add the following code:

Private Sub Status_BeforeUpdate(Cancel As Integer)
Dim RowSourceArray() As String
Dim I As Integer
RowSourceArray = Split(Me.Status.RowSource, ";")
Cancel = True
For I = LBound(RowSourceArray, 1) To UBound(RowSourceArray, 1)
    If (Chr(34) & Me.Status & Chr(34)) = RowSourceArray(I) Then
        Cancel = False
        Exit For
    End If
Next I
If Cancel = True Then Me.Status.Undo
End Sub

Thanks especially to @MarkK

Updated to change field properties.

 

[Minty]

I thought issuing Cancel = True cancelled the form update?
Can you really reverse it later with Cancel = False ??

Genuinely, never seen that done like that before.

 

[Marshall Brooks]

This is not the BeforeUpdate for the form. It is the BeforeUpdate for the Status Field.

I think basically Cancel = True Cancels the status event (which also Cancels the AfterUpdate() and subsequent event. It won't work, but it should be a simple change with a new variable. Not tested yet, but something like:

Private Sub Status_BeforeUpdate(Cancel As Integer)
Dim RowSourceArray() As String
Dim I As Integer
Dim NotInList As Boolean
RowSourceArray = Split(Me.Status.RowSource, ";")
NotInList = True
For I = LBound(RowSourceArray, 1) To UBound(RowSourceArray, 1)
    If (Chr(34) & Me.Status & Chr(34)) = RowSourceArray(I) Then
        NotInList = False
        Exit For
    End If
Next I
Cancel = NotInList
If Cancel Then Me.Status.Undo
End Sub

 

[Marshall Brooks]

Okay, I'm pretty lost now ...

Updated code:

Private Sub Status_BeforeUpdate(Cancel As Integer)
Dim RowSourceArray() As String
Dim I As Integer
Dim NotInList As Boolean
RowSourceArray = Split(Me.Status.RowSource, ";")
NotInList = True
For I = LBound(RowSourceArray, 1) To UBound(RowSourceArray, 1)
    If (Chr(34) & Me.Status & Chr(34)) = RowSourceArray(I) Then
        NotInList = False
        Exit For
    End If
Next I
If NotInList = True Then
    Cancel = True
    Me.Status.Undo
End If
End Sub

This works fine for the initial issue - i.e. if I type "AY" and Enter, or anything NOT in the Row Source, it ignores the input and keeps the old value. If I use the drop-down or type something the is in the list, it accepts the change. So far, so good.

But - the AfterUpdate event never fires.

If I set a break point and step through the code, skips over "Cancel = True", but it also never does anything after End Sub.

Help!!!

 

[Minty]

NotInList might be a poor choice of name for your Boolean variable - as it's also a property of a combo box?

 

[Marshall Brooks]

DRATS - seemed descriptive to me. Back to work!!!

 

[Marshall Brooks]

Changing it to NotInList_1 fails also ...

 

[Marshall Brooks]

Problem on my end somewhere - I commented out the entire procedure and the afterupdate didn't fire. Re-opening the database and trying again.