Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    ZdenniZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    6

    Update bound textbox after notinlist event complete


    Hi! I have a form with a combo box and a textbox next to it. The textbox displays a vendor name upon selection of a vendor number in the combo. The combo has a notinlist event doing the following. If the user enters a vendor number which is not in the list (e.g. a new vendor), a form shows up where the user enters all needed vendor data. After data entry is complete, the form closes and combo updates with a new vendor number, that works ok. But I want the textbox immediately show the new vendor name next to the new vendor number in the combo however that doesn't happen. If I choose another vendor in the combo and then switch back to that newly added vendor number, then the textbox updates with the new vendor name. Tried to requery the recordset, the combo, the textbox, to no avail... Would appreciate any help. Thanks!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You typically wouldn't (shouldn't) store the name in addition to the number, but you may need similar code in the close event of the form that opens up that you have behind the combo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Tried to requery the recordset, the combo, the textbox, to no avail
    A bound form gets loaded with a set of records. If you change underlying data then start requerying controls such as textboxes, you can't expect to see new values because the 'out dated' recordset is still what is loaded into the form. You need to requery the form. An exception to this behaviour is lists such as combo box lists. Those will refresh if you requery them, but then that's not really the same thing.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    ZdenniZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    6
    Thank you, guys. So I tried to do the following:

    1. The combo box has the following query as Row Source:
    Code:
    SELECT Vendors.ID, Vendors.Vendor_no, Vendors.Vendor_title FROM Vendors ORDER BY Vendors.[Vendor_no];
    2. The text box next to the combo displays Column(2) of the Combo based on the query above which is Vendor_title on the On_Change event of the combo:
    Code:
    Private Sub Combo225_Change()
    Me.Text207 = Me.Combo225.Column(2)
    End Sub
    The textbox shows the existing vendor names chosen in the combo okay.

    3. On NotInList:
    Code:
    Private Sub Combo225_NotInList(NewData As String, Response As Integer)
    Dim oparg As String
    Dim dbsProc As DAO.Database
       Dim rstVnds As DAO.Recordset
       Dim intAnswer As Integer
       Dim cbo As Control
    On Error GoTo ErrorHandler
       intAnswer = MsgBox("Add " & NewData & " to the list of vendors?", _
          vbQuestion + vbYesNo)
       If intAnswer = vbYes Then
       oparg = NewData
       Set dbsProc = CurrentDb
          Set rstVnds = dbsProc.OpenRecordset("Vendors")
          rstVnds.AddNew
          rstVnds![Vendor_no] = NewData
          rstVnds.Update
          rstVnds.Close
    DoCmd.OpenForm "AddVendor_Form", , , , , acDialog, oparg //this is done with OpenArgs so that the AddVendor form already includes the new vendor number when opened 
     MsgBox ("Vendor added successfully")
         Response = acDataErrAdded
    Else
    Response = acDataErrDisplay
    End If
    Set rstVnds = Nothing
    Set dbsProc = Nothing
       Exit Sub
    ErrorHandler:
       MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    End Sub
    So now if I try to requery the AddContract form in the AddVendor form close event, the notinlist event fires up again. Requery inside the notinlist code also repeats the notinlist event... I wonder why the text box doesn't show the updated Column(2) of the Combo... I am at a loss )).

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    what form is this?
    DoCmd.OpenForm "AddVendor_Form", , , , , acDialog
    The same one you already have open? Names of things might help rather than just "I have a form...".
    I get the picture that you have a form open, you might add a vendor number to a table but rather than just going back to the combo with the added value and picking it, you're not only expecting the new value to be in the textbox but you're trying to open a form that is already open.

    To simply update the combo list, a requery on the combo should be sufficient. To put the new value in the textbox (assuming the notinlist event is on the same form) the notinlist event should take care of that after the append as in
    Me.textBoxName = NewData. However, you don't need a recordset to do the append as a simple Docmd.RunSql would suffice.

    EDIT - I think I just realized you're adding values to one field when two are required in the table. Not sure if that has anything to do with your issue because user is adding vendor no (column 1) and you're trying to get vendor title (column 2) in the textbox. Rather than dealing with a second form for this (if that is what's going on) why not just prompt for both values in the notinlist event?

    Me.textBoxName = NewData. However, you don't need a recordset to do the append as a simple Docmd.RunSql would suffice.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This is comical, I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by pbaldy View Post
    This is comical, I'll get out of the way.
    That is directed to whom?

  8. #8
    ZdenniZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    6
    what form is this?
    DoCmd.OpenForm "AddVendor_Form", , , , , acDialog The same one you already have open?
    No . The form which is open is AddContracts_Form (bound to Contracts table). This form is to add a new contract details where you can either choose an existing vendor in the combo or type a new vendor number (which is not in list) and the AddVendor form opens up (bound to Vendors table) which in its turn is to add vendor contact info etc. So what I expect is that after the AddVendor form closes, the new Vendor number in the combo and the new vendor name in the textbox of the AddContracts form are immediately displayed.

    I think I just realized you're adding values to one field when two are required in the table.
    Both of those fields are added in the AddVendor form while notinlist event is on (AddVendor form is modal). So upon AddVendor form closure the combo's Column(1) updates ok, but the textbox remains blank.

  9. #9
    ZdenniZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    6
    you may need similar code in the close event of the form that opens up that you have behind the combo.
    I tried doing that, but then the NotInList event fires up again as the form behind the combo closes...

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you'll have to post a zipped copy of your db because your post comments don't jive with you code. Your code only seems to add data to one field

    rstVnds![Vendor_no] = NewData

    yet you're trying to put 1 field value in a combo and 1 field value in a textbox. Or just assign the value to the textbox as I said in last line of post 5. Not sure where you will get it from though, assuming NewData holds only one value and it's not the one you want.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    ZdenniZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    6
    Quote Originally Posted by Micron View Post
    Your code only seems to add data to one field
    rstVnds![Vendor_no] = NewData
    I resolved my issue, but for the sake of clarity, I'll try to explain once again.

    I have two tables - AddContracts and AddVendors. AddContract form:
    Click image for larger version. 

Name:	Contform.png 
Views:	15 
Size:	42.4 KB 
ID:	39611

    AddVendor form:

    Click image for larger version. 

Name:	AddVnd.png 
Views:	14 
Size:	25.5 KB 
ID:	39612

    There is Vendor FK in the Contracts. The Add Contract form besides contract-related fields has also fields from the Vendor table (a combo and a textbox), so you can choose an existing vendor for a new contract. The textbox is just for user interface purposes, you do not enter data in there, it is only to display the vendor name corresponding to the number chosen in the combo. So the order of actions is as follows: Open AddContract form -> enter new contract details - > choose vendor from the list (combo) -> if vendor is in the list textbox displays its name, if not in the list - AddVendor form opens up where you enter Vendor name and contact details (the new vendor number is transferred from the combo through OpenArgs). When AddVendor form is closed the combo and the textbox in the AddContract form gets populated with the new vendor number and vendor name.

    So the way I resolved this is:
    Code:
    Private Sub VenSvRecrd_Click()Dim VenNm As String
    
    
    On Error GoTo VenSvRecrd_Click_Err
        On Error Resume Next
        DoCmd.RunCommand acCmdSaveRecord
        Me.Visible = False
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
                End If
    VenSvRecrd_Click_Exit:
        Exit Sub
    VenSvRecrd_Click_Err:
        MsgBox Error$
        Resume VenSvRecrd_Click_Exit
    End Sub
    Code:
    Private Sub Combo225_NotInList(NewData As String, Response As Integer)Dim oparg As String
    Dim dbsProc As DAO.Database
       Dim rstVnds As DAO.Recordset
       Dim intAnswer As Integer
       Dim cbo As Control
        Dim rst As DAO.Recordset
        Dim ff As String 
       On Error GoTo ErrorHandler
       intAnswer = MsgBox("Add " & NewData & " to the list of vendors?", _
          vbQuestion + vbYesNo)
       If intAnswer = vbYes Then
       oparg = NewData
       Set dbsProc = CurrentDb
          Set rstVnds = dbsProc.OpenRecordset("Vendors")
          rstVnds.AddNew
          rstVnds![Vendor_no] = NewData
          rstVnds.Update
          rstVnds.Close
    DoCmd.OpenForm "AddVendor_Form", , , , acFormEdit, acDialog, oparg
    ff = Forms!AddVendor_Form!Vendor_title
     Response = acDataErrContinue     
    Me.Text207 = ff
    Response = acDataErrAdded     
         DoCmd.Close acForm, "AddVendor_Form"   
    
    
    Else   
    Response = acDataErrDisplay
    End If
    Set rstVnds = Nothing
    Set dbsProc = Nothing
       Exit Sub
    ErrorHandler:
       MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
    End Sub
    Basically after the new vendor details are added in the AddVendor form, I do not close it, but just hide it and then catch the new vendor title in the AddContract form and manually insert it as value into the text box without doing a requery. This suits my needs because the correct vendor name is already in the table, it's just does not appear in the dataset. Any requery before the NotInList event finishes makes the event fire up again and again. English is not my first language so please bear with me, my way of describing things might not be the clearest

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You explained it fine, I knew what you were trying to do, probably didn't explain it well. I was talking about "pushing" it from AddVendor_Form before it closed, like:

    Forms!OriginalFormName.Text207 = Me.Vendor_Title

    You "pulled" it from the original form, which is a perfectly good solution. I'd still point out that you would normally not save the name in related tables, just the ID. If it gets changed in the vendor table, you've got the wrong name saved in your data table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Originally Posted by pbaldy
    This is comical, I'll get out of the way.
    Quote Originally Posted by Micron View Post
    That is directed to whom?
    Are you going to provide an explanation regarding your comment or should I just take it that it pertains to my attempt to assist?

    To anyone who cares, I have dealt with this privately. If it results in my expulsion in retaliation, so be it. I would leave you all to draw your own conclusions from that action should it occur.
    Last edited by Micron; 08-30-2019 at 08:30 AM.

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  15. #15
    ZdenniZ is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2019
    Posts
    6
    Quote Originally Posted by pbaldy View Post
    I'd still point out that you would normally not save the name in related tables, just the ID.
    That's probably my bad explanation again, but I don't save the name in the Contracts table where the Add_contract form is bound. I save it in the Vendors table with bound AddVendor form only. I just needed the textbox on the AddContract form to display the name next to the new vendor number in the combo (the only two fields in the AddContract form that carry information on the vendor of the contract).

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. ClsModule and a NotInList Event
    By d9pierce1 in forum Programming
    Replies: 4
    Last Post: 08-04-2019, 05:16 AM
  2. Use NotInList to Add Item to a Bound ComboBox
    By CharissaBelle in forum Forms
    Replies: 3
    Last Post: 01-16-2018, 05:12 PM
  3. Replies: 7
    Last Post: 01-01-2016, 11:17 PM
  4. NotInList event
    By jgelpi16 in forum Programming
    Replies: 2
    Last Post: 04-13-2011, 09:10 AM
  5. Cancelling the NotInList event
    By Remster in forum Programming
    Replies: 12
    Last Post: 11-21-2010, 10:12 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