Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2016
    Posts
    14
    Provided Answers: 1

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

    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 that field, 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. That is where i am stuck.

    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

  2. Best Answer
    Posted by wao

    "Hi,

    Thanks for the response. Unfortunately that did not work, what happened was it locked the combo box to only allowing "NOI" not any of the other responses in the box. I want other users to be able to use other responses in the combo, but not that specific "noi" option because of the subsequent data that needs to be entered in the next form.

    Private Sub Order_Response_AfterUpdate()
    Dim Response As Integer
    If Me.Order_Response.Value = "NOI" Then
    Response = 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
    Me.Undo

    End If
    End Sub"


  3. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Although untested, I think this is what you're trying for:
    Code:
    Private Sub Order_Response_AfterUpdate()
        
     Dim Response As Integer
        
     If Me.Order_Response.Value = "NOI" Then
      
       Response = MsgBox("The option of 'NOI' should only be used by Keri, are you Keri?", vbYesNo, "Warning")
       
       If Response = vbYes Then
         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
        Me.Undo
       End If
     
     End If
    End Sub

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #3
    Join Date
    Jan 2016
    Posts
    14
    Provided Answers: 1
    Hi,

    Thanks for the response. Unfortunately that did not work, what happened was it locked the combo box to only allowing "NOI" not any of the other responses in the box. I want other users to be able to use other responses in the combo, but not that specific "noi" option because of the subsequent data that needs to be entered in the next form.

    Private Sub Order_Response_AfterUpdate()
    Dim Response As Integer
    If Me.Order_Response.Value = "NOI" Then
    Response = 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
    Me.Undo

    End If
    End Sub

  5. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    The code you posted is not the code I posted!

    IfResponse = vbYesThen

    is not the same as

    If Response = vbYes Then

    You're missing a space between

    If and Response

    and between

    vbYes and Then

    You're also missing the second End If.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    An alternative approach to asking users if they want to open a form is to control who xan open a form/report.
    The basic steps are...
    Know who the user is (google dev asish api call)
    Define which users can do ehat operations, thats down to you, but id exoect a table somewhere that associates users with user groups. Writing to that table requires good control, ie only approved users can view or make changes, any user can read in background.
    Then for each form or report that requires access control a mechanism that shows the object and a mechsnism that stops peopke opening the object if they have no rights.

    By associating a userid with a usergroup, and allowing a user to be a member if more than usergroup you can request if a user has permissions through their usergroup definition. If they have permission they can see the option to open the form or report

    But you also need to verify that the user has permissions to open that object in case the object is inadvertantly added to a menu or if its copued elsewhere.

    The real advantage of the network API call is that users dont have to have a userid and password INSIDE your access application. You dont have to worry about user security / authentication thats handled by your network administrators aka network trolls.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #6
    Join Date
    Jan 2016
    Posts
    14
    Provided Answers: 1
    Quote Originally Posted by Missinglinq View Post
    Although untested, I think this is what you're trying for:
    Code:
    Private Sub Order_Response_AfterUpdate()
        
     Dim Response As Integer
        
     If Me.Order_Response.Value = "NOI" Then
      
       Response = MsgBox("The option of 'NOI' should only be used by Keri, are you Keri?", vbYesNo, "Warning")
       
       If Response = vbYes Then
         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
        Me.Undo
       End If
     
     End If
    End Sub

    Linq ;0)>


    Thx Linq.

    Originally it was not letting me put a space in where you said, but I tried agai and it worked. Your help fixed my problem.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •