Results 1 to 12 of 12
  1. #1
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89

    NoInList woes

    Hi folks,
    I'm using the NotInList event of a combobox to open a popout form for users to add a record - I have the form opening and saving the record and closing down fine, but for the life of me I cannot seem to get anything to work for the original combobox control to update the list (Which is pulled from a table). If I hit escape to clear the entry that prompted the NotInList event and then press F5 to refresh the list updates. I've tried various iterations of me.Requery and DoCmd.Requery referencing the form, subform and control in a few places. I really want it to be in the OnClose event of the popout form.



    Forgive the lack of specificity, I've been down a rabbit hole of trying various things.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you should post the code (please use code tags). Forms!MainFormName.comboControlName.Requery ought to work (from the input form).
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by Micron View Post
    you should post the code (please use code tags). Forms!MainFormName.comboControlName.Requery ought to work (from the input form).
    Thanks for responding. Could you explain code tags? I looked through the forums and couldn't find a reference. Here's what's in there for the OnClose event of the form. I know I'm doing something stupid, but I'm learning, slowly:

    Private Sub Form_Close()
    Forms!frmMaterials.subfrmMTLChar.cmbMFGName.Requer y
    End Sub

    When the form closes I get 'Runtime error 438 - Object doesn't support this property or method'

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    code tags: see # on forum menu bar
    Your code suggests that the combo is on a subform. Is that the case? Then the reference syntax is Forms!MainFormName.SubformControlName.Form! then the form property or control reference if you're drilling down to subform controls and their properties/methods. If subfrmMTLChar is the name of your subform control (which may or may not be the same name as the subform it holds) then perhaps
    Code:
    Forms!frmMaterials.subfrmMTLChar.Form!cmbMFGName.Requery
    If that doesn't work, you need to clarify where things are.

  5. #5
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Quote Originally Posted by Micron View Post
    code tags: see # on forum menu bar
    Your code suggests that the combo is on a subform. Is that the case? Then the reference syntax is Forms!MainFormName.SubformControlName.Form! then the form property or control reference if you're drilling down to subform controls and their properties/methods. If subfrmMTLChar is the name of your subform control (which may or may not be the same name as the subform it holds) then perhaps
    Code:
    Forms!frmMaterials.subfrmMTLChar.Form!cmbMFGName.Requery
    If that doesn't work, you need to clarify where things are.
    Thank you! This is like learning Spanish, only more useful. With this it gets hung up because the combobox still has the new 'NotInList' entry and it wants the entry removed before requerying - I get Runtime error 2118 - You must save the current field bore running the requery action. How do I reset the combo to blank?

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Post the code from the point at which the user tries to enter a value that isn't in the combo list. The requery should be part of that process - right after the NotInListEvent code runs, not when that form is closed. Or look at examples, such as this one (make sure the code you use is appropriate for the row source type for the combo (e.g. value list, table/query).
    https://docs.microsoft.com/en-us/off...obox.notinlist

  7. #7
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    Code:
    Private Sub cboMFGName_NotInList(NewData As String, Response As Integer)    Dim strSQL As String
        Dim i As Integer
        Dim Msg As String
    
    
        
        If NewData = "" Then Exit Sub
    
    
        Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
        Msg = Msg & "Do you want to add it?"
    
    
        i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Manufacturer...")
        If i = vbYes Then
            DoCmd.OpenForm "frmAddMFG"
            Response = acDataErrContinue
        End If
    End Sub
    This is what I have for the NotInList event. I looked at the suggested link and I think I need to study that a bit - my first thought is that I could use one of those examples in some other areas, but for this particular one there are two fields - the second isn't strictly required but that's why I had the popout form, just to provide the option of adding that info. For full disclosure I did adapt the code above from an example I found online. AND... I think my earlier post I had typed the control name wrong, with cmb instead of cbo at the beginning.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, will wait until you review then decide how to modify the code accordingly. As I mentioned, the NIL event should be adding the value. There is no need to complicate things by opening another form to add a value that someone has already tried to enter.

  9. #9
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89

    Thank You!

    Quote Originally Posted by Micron View Post
    There is no need to complicate things by opening another form to add a value that someone has already tried to enter.
    Thank you again for the advice. I have several places where comboboxes draw from tables and all but one have only a primary key and a name or description field. I decided that the information in the second field on this particular one is not critical, so I'm going to take your suggestion and run with it. I tend to be overly cautious about getting bad data so I have a habit of having too many hurdles, thank you for helping me eliminate one.
    I did use the code in the link but it did not function right away, I had to go through, study and modify it a bit to get it to work, a great learning experience. Here's the code which does work:

    Code:
    Private Sub cboMFGName_NotInList(NewData As String, Response As Integer)        On Error GoTo Error_Handler
        Dim intAnswer As Integer
        intAnswer = MsgBox("""" & NewData & """ is not an approved Manufacturer. " & vbCr & vbCr _
            & "Do you want to add it now?", vbQuestion + vbYesNo, "Invalid Selection")
    
    
        Select Case intAnswer
            Case vbYes
                DoCmd.SetWarnings False
                DoCmd.RunSQL "INSERT INTO tblMFG (MFGName) " & "Select """ & NewData & """;"
                DoCmd.SetWarnings True
                Response = acDataErrAdded
            Case vbNo
                MsgBox "Please select an item from the list.", _
                    vbExclamation + vbOKOnly, "Invalid Entry"
                Response = acDataErrContinue
    
    
        End Select
    
    
    Exit_Procedure:
            DoCmd.SetWarnings True
            Exit Sub
    
    
    Error_Handler:
            MsgBox Err.Number & "," & Err.Description
            Resume Exit_Procedure
            Resume
    
    
    End Sub
    Last edited by khayward; 01-05-2020 at 07:27 AM. Reason: Spelling

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You don't need the last Resume - that line should never get executed. It is good that you turn warnings on in the exit portion so that they don't remain off if an error occurs, but you don't really need to do that in the Select Case block as well. Error or no error, the exit block will turn them on, but it's not wrong. Suggest you look into the .Execute method of the Database object rather than turning warnings on and off.

    https://docs.microsoft.com/en-us/off...ute-method-dao
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    khayward is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Apr 2017
    Location
    Oregon
    Posts
    89
    I'm reading up on the exit method now for running action queries, it's a step beyond for me, but I want to learn it because I don't want to have to turn warnings on and off all the time, but I do want to have some kind of confirmation that the query ran successfully. Based on what I am reading using .Execute is the only real way to do this.

    I appreciate the feedback on the code, as I mentioned, it was adapted from someone else's suggestion and I really am just getting my feet wet in VBA so I don't know these details yet, thanks again for your help!

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    NP. Glad you got it solved (and marked it as such too!).

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

Similar Threads

  1. Continuous Form Woes
    By Paul H in forum Forms
    Replies: 20
    Last Post: 03-14-2017, 03:03 PM
  2. Me.Filter with variable woes
    By chris.williams in forum Programming
    Replies: 3
    Last Post: 08-21-2012, 01:03 PM
  3. Combo Box Woes
    By tbassngal in forum Forms
    Replies: 2
    Last Post: 09-01-2011, 08:54 AM
  4. error message woes
    By ggs in forum Forms
    Replies: 4
    Last Post: 05-29-2011, 04:03 AM
  5. Combo box woes...
    By jonbonazza in forum Forms
    Replies: 3
    Last Post: 06-21-2010, 11:34 AM

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