Before Update (1 Viewer)

[mike60smart]

Hi Everyone

This is a follow on from a previous thread Here

I have Main Form which is Unbound named "Dispatch"

With a Subform named "frmCurrentLoadListsubform"

On the Subform I have a Combobox named "cboStatus"

What I want to be able to do is as follows:-

If the user tries to select a value which is Not Equal to "On Hold" or "Available" then

A Msgbox pops up with the Options of OK or CANCEL informing them they can only select "On Hold" or "Available".

Also If they try to select a value of "Billed" then

An Input Box Pops Up asking them to enter a Password.

If the Password is correct then allow the selection of "Billed"

I have the following code for entering a Password.

I get confused as to where to put the End If's when trying to code these types of process's.

Any help as to the layout / construction of this After Update of cboStatus appreciated.

Private Sub cmdMask_Click()

10        On Error GoTo cmdMask_Click_Error
       Dim x

20        x = InputBoxDK("Password Required", "Password Required")
30        If x = "" Then End
40        If x = "SCtb0825" Then
50        DoCmd.OpenForm "Administration", acNormal
60        MsgBox "Welcome " & Environ("UserName") & "!", vbExclamation

70      End
80        End If

       
90        On Error GoTo 0
100       Exit Sub

cmdMask_Click_Error:

110       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdMask_Click, line " & Erl & "."

End Sub

 

[Gasman]

So all you have posted is the same rubbish your started with? Even after @bastanu gave you corrected indented code?

Also from that thread post #43

You probably want to do this in the BeforeUpdate event of the combo so that you can cancel the event if the password is incorrect.

I give up.

 

[mike60smart]

So all you have posted is the same rubbish your started with? Even after @bastanu gave you corrected indented code?

Also from that thread post #43


I give up.

Hi Gasman
My apologies for the title which I have changed to Before Update.

David's solution for changing the Combobox to Billed was one stage that I needed and after lots
of patient guidance I eventually managed to implement this.

What I am now attempting to do is add additional Steps to the process as outlined. If you feel it is rubbish then so be it.

 

[mike60smart]

Hi Everyone
I have made the following attempt to cover the process mentioned.

The section requiring an Inputbox does not work it just allows the user to select "Billed"
and no InputBox pops up.

If anyone can highlight my error it would be appreciated.

Private Sub cboStatus_BeforeUpdate(Cancel As Integer)
        
Dim rtn As Long
If Me.cboStatus.Column(1) <> "Billed" Then
    rtn = MsgBox("You can only change the Status using this Combobox to ON HOLD or AVAILABLE!!. Click Yes to Continue. No to Cancel!", _
           VbMsgBoxStyle.vbYesNo)
If rtn = vbYes Then

      'do code here for yes

      Debug.Print vbYes

Else
    
If Me.txtStatus = "Billed" Then

    Dim x
    Dim ctrl As Control
    x = InputBoxDK("Password Required", "Password Required")
    If x = "" Then Exit Sub ' there is no End  :)
    If x = "SCtb0825" Then
        rtn = MsgBox("You have changed status to Billed!!. Click Yes to Continue. No to Cancel!", _
           VbMsgBoxStyle.vbYesNo)
End If
End If
End If
End If
End Sub

 

[mike60smart]

Hi Everyone
I have updated the Code as follows and it appears to do what I want.

Private Sub cboStatus_BeforeUpdate(Cancel As Integer)
       
Dim rtn As Long
If Me.cboStatus.Column(1) <> "Billed" Then
    rtn = MsgBox("You can only change the Status using this Combobox to ON HOLD or AVAILABLE!!. Click Yes to Continue. No to Cancel!", _
           VbMsgBoxStyle.vbYesNo)

End If

If Me.cboStatus.Column(1) = "Billed" Then

    Dim x
    Dim ctrl As Control
    x = InputBoxDK("Password Required", "Password Required")
    If x = "" Then Exit Sub ' there is no End  :)
   
   
If rtn = vbYes And x = "SCtb0825" Then
     rtn = MsgBox("You have changed status to Billed!!. Click Yes to Continue. No to Cancel!", _
           VbMsgBoxStyle.vbYesNo)
       Me.cboStatus.SetFocus
End If
If rtn = vbYes Then

      Me.cboStatus.SetFocus
End If
End If
End Sub

 

[bastanu]

Mike, I think the appearances are deceiving, please review this updated code:

Private Sub cboStatus_BeforeUpdate(Cancel As Integer)
      
Dim rtn As Long
Dim x
Dim ctrl As Control

If Me.cboStatus.Column(1) <> "Billed" Then
    rtn = MsgBox("You can only change the Status using this Combobox to ON HOLD or AVAILABLE!!. Click Yes to Continue. No to Cancel!", _
           VbMsgBoxStyle.vbYesNo)
'End If


Else          'If Me.cboStatus.Column(1) = "Billed" Then
    
    x = InputBoxDK("Password Required", "Password Required")
    
    If x = "" Then Exit Sub

    If rtn = vbYes And x = "SCtb0825" Then    'rtn will be 0 here because you didn't ask the question yet....
        rtn = MsgBox("You have changed status to Billed!!. Click Yes to Continue. No to Cancel!", _
           VbMsgBoxStyle.vbYesNo)
       Me.cboStatus.SetFocus 'so you set the focus regardless if the user answers Yes or No
    End If
    
    If rtn = vbYes Then 'WHY DO YOU HAVE THIS BLOCK AS IT DOESN'T DO ANYTHING DIFFERENT
      Me.cboStatus.SetFocus
    End If
End If
'you are not using the event properly as you do not have Cancel=True anywhere to stop the update
End Sub

Cheers,

 

[mike60smart]

Hi Vlad

In your revised Code where would I put the Cancel=True to stop updates?

 

[bastanu]

Mike,
The code I gave you is just yours with the proper indentation towards the bottom and a bit of streamlining in the middle (replaced two IF blocks with one IF\ELSE). I don't know the business rules, what values the combo can have and\or the sequence of status changes.

For the first case where the value to update to <> "Billed" you are providing the user with two options but you do not compare the value to any of them and do not act on any Yes or No answer.

For the second case where the value is "Billed" you are using the rtn variable before it was initialized so at that point it will hold the default value for a long variable which is 0. So the code inside that If block will never run. And even if it will you aren't doing much, just setting the focus on the active control which I don't think would work anyway as Access will move it to the next one in the tab order.

So now you need to analyse what you want to happen for each of the two cases. If the user answers no you should probably cancel (=True) and maybe add Me.Undo to reset the combo. If they answer yes ( meaning the confirm as per your questions) then accept the change. But for the first case you still need to see if the new status value is one of the two acceptable options you provided to the user in your msgbox.

Work on it and post back f you get stuck.

Cheers,