Help with Event Procedure, Open form based on selection with a message box

[wesao]

Hi,

I am new to building a database and need some help.

I have a form that when a certain item is selected in a drop down box, a new form opens and pre-populates some of the data for the user. With our current personnel structure though, I only want 1 designated person to use the option in the field that opens the new form, so I am creating a message box to remind people. What I want is if someone answers "yes" to the message box than the next form opens. However if they answer "no" I want the entry they just made to clear, and the subsequent form to not open. I am stuck at the form always opens whether they answer yes or no.

Code:

Private Sub Order_Response_AfterUpdate()
Dim Response As Integer
If Me.Order_Response.Value = "NOI" Then
MsgBox "The option of 'NOI' should only be used by Keri, are you Keri?", vbYesNo, "Warning"
IfResponse = vbYesThen
DoCmd.OpenForm "f_NOI"
Forms!f_NOI.txt_lo_name = Me.txt_13260_Owner_1
Forms!f_NOI.txt_lo_addr = Me.txt_13260_Address
Forms!f_NOI.txt_lo_city = Me.txt_13260_City
Forms!f_NOI.txt_lo_st = Me.txt_13260_State
Forms!f_NOI.txt_lo_zip = Me.txt_13260_Zip
Forms!f_NOI.txt_APN = Me.txt_key_apn
Forms!f_NOI.txt_acres = Me.txt_acres
Forms!f_NOI.SUBMIT_DATE = Me.txt_Response_Date
Else 'Do nothing
Cancel
End If

End Sub

 

[Micron]

Change to If Response = vbYes Then.
Plus:
- you do not cancel an event by writing Cancel; you must write Cancel=True.
- you cannot cancel the opening event of a form from this event, you can only prevent it
- there is no cancel option for the AfterUpdate event
- you are missing an End If

Dim Response As Integer
If Me.Order_Response.Value = "NOI" Then
  MsgBox "The option of 'NOI' should only be used by Keri, are you Keri?", vbYesNo, "Warning"
[COLOR=#ff0000]    If Response[/COLOR] = [COLOR=#ff0000]vbYes Then[/COLOR]
      DoCmd.OpenForm "f_NOI"[COLOR=#008000]
      With Forms!f_NOI
        .txt_lo_name = Me.txt_13260_Owner_1
        .txt_lo_addr = Me.txt_13260_Address
        .txt_lo_city = Me.txt_13260_City
        .txt_lo_st = Me.txt_13260_State
        .txt_lo_zip = Me.txt_13260_Zip
        .txt_APN = Me.txt_key_apn
        .txt_acres = Me.txt_acres
        .SUBMIT_DATE = Me.txt_Response_Date
      End With
[/COLOR]    End If
End If

If the answer is NO, the code to open the form should not run, thus there is nothing that needs "canceling". If you need the combo selection to change back to something else, you will first have to capture what it was. One way is to have a hidden unbound textbox on the form that you set to the value of the combo in the Before_Update event. To reset it, you make the combo value equal to that texbox value. This assumes your combo box is updateable - change the end of my code to

End With
End If
Me.Order_Response = Me.hiddenTextboxNameHere
'you may need Me.Repaint here
End If

You can also use the Before_Update event to set the value of a variable at the top of your form module and refer to that instead.