Results 1 to 11 of 11
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    56

    Question Trying to get Fancy ... pass NotInList data to Pop-up form

    Hello All! I volunteered (yikes) to do a presentation on ways to add values used in Combo Boxes on the fly.



    I have an INSERT INTO example and I have a "List Items Edit Form" example {no VBA coding}. I even have a value list edit (even though it makes me cringe!). But, now I'm trying to get fancy and it isn't working!

    I should let it go and stick with what I have, but now it's bugging me.

    What I want to have happen is when the user enters a distributor that's not in the list, the user is asked if they want to add the distributor. If they choose "yes", then a new form pops up for them to do so. I want the text they entered that wasn't in the list to populate the company name field on the popped-up form. AND I want the code to stop running while the user fills in the form, then start again when the popped up form is closed AND re-query the combo box so the new distributor is listed.

    Is this a pie-in-the-sky ask? I can have a message alert the user. I can have the form pop up if the user wants to enter a new company ... AND there's where I get stuck.

    Has anyone done this?

    My code:

    Code:
    Private Sub Distributor_NotInList(NewData As String, Response As Integer)
        On Error GoTo Distributor_NotInList_Err
        Dim intAnswer As Integer
        Dim strSQL As String
        'Display a message asking user if they want to add the new data to the list
        intAnswer = MsgBox("The company " & Chr(34) & NewData & _
            Chr(34) & " is not on file." & vbCrLf & _
            "Would you like to create a record for " & Chr(34) & NewData & Chr(34) & " ?" _
            , vbQuestion + vbYesNo, "HUG Presentation")  'displays a question mark and yes & no buttons. The box has a custom title
        'If the answer is yes, open the Distributor form as a pop-up and modal
        If intAnswer = vbYes Then
            DoCmd.OpenForm "Distributors - Pop-up", , , , acFormAdd, , NewData
            Forms![Distributors - Pop-up]!CompName = Forms!Products!NewData    '<-- THIS DOESN'T WORK AND, AS SOON AS IT FIRES, THE NEXT MESSAGE BOX APPEARS
            'tell user the new data has been added
            MsgBox "The new company has been added to the list." _                    '<-- I DON'T WANT THIS TO APPEAR UNTIL THE POP-UP FORM IS CLOSED
                , vbInformation, "HUG Presentation"
            'tell Access the data has been added and to requery the combo box.
            Response = acDataErrAdded
        Else
            'Remind user to choose from the list
            MsgBox "Please choose a company from the list." _
                , vbInformation, "HUG Presentation"
            'tell Access to ignore the NotInList error and continue
            Response = acDataErrContinue
        End If
    Distributor_NotInList_Exit:
        Exit Sub
    Distributor_NotInList_Err:
        MsgBox Err.Description, vbCritical, "Error"
        Resume Distributor_NotInList_Exit
    End Sub
    I have also tried putting something in the On Open Event of the pop-up form, but that also doesn't work ... possibly because I'm not passing the new data correctly.

    Do I need to step out of this subroutine and work with the pop-up form only?

    Thanks in advance!


    Susie
    Kansas

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,745
    A popup is appropriate approach. OpenForm acDialog parameter of WindowMode argument will suspend primary form code execution until popup closes. This means the popup form will need code to set its CompName field with NewData value. Since you are passing NewData with OpenArgs, should do that anyway.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    374
    Since you are using NewData in OpenArgs I would put an OpenArgs statement in the On_Open event of the form, i.e.

    Code:
    If IsNull(OpenArgs) Then Exit Sub
    Me.CompName = Me.OpenArgs
    Then you can eliminate the line that doesn't work anyway.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  4. #4
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    56
    Thank you June7 ....

    I've always had some trouble wrapping my brain around how to use OpenArgs. I don't know why ... I've read books, lots of different websites and I still don't seem to get it.

    (and I've searched this forum several times and still don't grasp the concept of OpenArgs)

    However, it sounds like the correct VBA flow would be:

    Give the user a message about the info not existing and give the user a choice
    If the user selects "yes" to add the new info, open the form and ON THAT form's open event, set the company name to equal the data entered. On THAT form's close window event, tell the user the info was successfully added and requery the combo box on the original form.
    If the user selects "no" to not add the new info, a message pops up reminding the user to select from available information.
    End the code.

    So, if the popped up form is used to finish out the process, do I need to do something to end the original NotInList event code, or does the DoCmd to open the new form end the NotInList event code?

    As far as the OpenArgs ... the very first line of the subroutine includes "NewData as string", so that defines the info typed into the combo box as a string. I know the NewData contains the correct information because the message box that appears has the correct wording.

    Is this the correct way to send the NewData to the popped up form?

    Code:
    DoCmd.OpenForm "Distributors - Pop-up", , , , acFormAdd, , NewData


    Then, in the Open Event of the new form, I put:

    Code:
    Private Sub Form_Open(NewData as String, Cancel As Integer)
    
        [Distributors - Pop-up].CompName = NewData
      
    End Sub
    In the close event of the new form, I put:

    Code:
    Private Sub Form_Close()
        
        'tell user the new data has been added
        MsgBox "The new company has been added to the list." _
            , vbInformation, "HUG Presentation"
        'tell Access the data has been added and to requery the combo box.
        Response = acDataErrAdded
        
    End Sub

    But, this coding doesn't work ... I did a breakpoint at DoCmd.OpenForm of the original code and then step through with F8. The code jumps to the error at the end of the original code and stops. The second form doesn't even pop-up. And then I get "The OpenForm action was cancelled."

    Hints???

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,745
    OpenArgs is an argument of OpenForm method. It is also a property of form/report.

    Did you note my comment about acDialog?

    DoCmd.OpenForm "Distributors - Pop-up", , , , acFormAdd, acDialog, NewData

    Then code in popup form.

    If Me.NewRecord Then Me!CompName = Me.OpenArgs

    Strongly advise not to use spaces nor punctuation/special characters in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    585
    see if the generic NIL code at the bottom of this post gives you any help

    https://access-programmers.co.uk/for...0&postcount=10

  7. #7
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    56
    Well, I'm about to give up on this ... I'm just not getting this to work.

    My code as it currently stands:

    Code:
    Private Sub Distributor_NotInList(NewData As String, Response As Integer)
        On Error GoTo Distributor_NotInList_Err
        Dim intAnswer As Integer
        'Display a message asking user if they want to add the new data to the list
        intAnswer = MsgBox("The company " & Chr(34) & NewData & _
            Chr(34) & " is not on file." & vbCrLf & _
            "Would you like to create a record for " & Chr(34) & NewData & Chr(34) & " ?" _
            , vbQuestion + vbYesNo, "HUG Presentation") 'displays a question mark and yes & no buttons. The box has a custom title
        'If the answer is yes, open the Distributor form as a pop-up and modal
        If intAnswer = vbYes Then
            DoCmd.OpenForm "DistributorsPopup", , , , acFormAdd, acDialog, NewData
    
    
            'tell Access the data has been added and to requery the combo box.
            Response = acDataErrAdded
        Else
            'Remind user to choose from the list
            MsgBox "Please choose a company from the list." _
                , vbInformation, "HUG Presentation"
            'tell Access to ignore the NotInList error and continue
            Response = acDataErrContinue
        End If
    Distributor_NotInList_Exit:
        Exit Sub
    Distributor_NotInList_Err:
        MsgBox Err.Description, vbCritical, "Error"
        Resume Distributor_NotInList_Exit
    End Sub
    Code for the DistributorsPopup form:

    Code:
    Private Sub Form_Open(NewData As String, Cancel As Integer)
    
    
        If Me.NewRecord Then
            Me!CompName = Me.OpenArgs
        End If
      
    End Sub


    I did change the name of the form so there were no spaces.

    I get an error that states "The OpenForm action was canceled."

    I may try the code in the message from Moke123.

    Thanks all, for your patience with me.

    Susie
    Kansas

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    49,745
    Open event does not have NewData argument. Remove it.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Posts
    585
    heres an example of the NIL code I refered to.

    NIL.accdb

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,013
    catluvr,

    See this Richard Rost link re Not in list, it may be relevant.

  11. #11
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    56
    Ah ha!

    I finally got it! (much to hubby's amusement last night as I was shouting at the computer when it worked!)

    I moved the code for the popped up form from the On Open event to the On Load event and everything magically came together!

    Thank you all for your help and comments. I really appreciate it! In researching this, I found that lots of people want to do this action and there are many, many different ways to go about it. I'm thankful that your responses weren't just "it's been done, Google it"!

    Susie
    Kansas

Please reply to this thread with any new information or opinions.

Similar Threads

  1. NotInList Edit Form Question
    By 480hoyle in forum Forms
    Replies: 4
    Last Post: 03-08-2018, 04:24 PM
  2. Pass Data From Form To Report
    By Juan4412 in forum Forms
    Replies: 9
    Last Post: 02-07-2016, 09:13 PM
  3. Replies: 9
    Last Post: 06-27-2014, 08:23 PM
  4. Pass data from one form to another
    By Bruce in forum Forms
    Replies: 16
    Last Post: 02-25-2013, 03:59 PM
  5. Anyone fancy a challenge????!!!!!!
    By gregh in forum Database Design
    Replies: 1
    Last Post: 03-14-2011, 05:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums