Results 1 to 6 of 6
  1. #1
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149

    Subform data not refreshing

    Hi Guys



    I've got a form called InventoryF and on it there is subform called DS. Inside DS I have InvntoryDataEntryF running to display the record selected via a combo box. If the user clicks on new item it opens the form to the new record and data can be entered. Clicking submit saves the record.

    In Submit button I have code refreshing the combo box: Me.cboInventory.rowsource = Me.cboInventory.rowsource
    (this works great)

    Problem is that If I click the combo box and select the new entry I just added it doesn't update the subform. However if I select any other entry that was entered it works. It seems I have to close the

    form every time and open it again to get the subform to refresh and read the selection

    Click image for larger version. 

Name:	inv.JPG 
Views:	20 
Size:	39.4 KB 
ID:	35500

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Control used to input search criteria should be UNBOUND. If purpose of combobox is to find existing record, it must be UNBOUND otherwise you change data in record. What is the code that finds record?

    Where is the 'Submit' button?

    Command commonly used to refresh combobox is: Me.cboInventory.Requery
    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
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Hi June7

    I didnt use vba to get the combo box to search the records for data, it was done for me by access when I created it. A new third option was given to show records on the form relating to the combovox so I choose that. Iuses a macro. What would the code look like if I changed it to unbound and use vba?

    The submit button is set to hidden until the user clicks on EDIT or NEW IEM. The form doesn't allow edits or additions until then.

  4. #4
    Freddie81 is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2018
    Posts
    149
    Okay, so I redone the combo box. Its unbound.

    I've tried this code in Event after update on the combo box:

    Private Sub cboInventory_AfterUpdate()
    Dim MyInventory As String
    MyInventory = "Select * from ProductsT where ([ProductID] = " & Me.cboInventory.Column(0) & ")"
    Me.DS.Form.RecordSource = MyInventory
    Me.DS.Form.Requery
    End Sub

    Not working

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I have code refreshing the combo box: Me.cboInventory.rowsource = Me.cboInventory.rowsource
    How is this bound? Rowsource property is the data source for the control list, not the ControlSource property, which indicates what the control is bound to.

    I don't get why you are altering the recordsource for a subform to match the rowsource. Plus, you're trying to use the selected item from a list by referring to the Column of that list rather than the value chosen from the control. If it was appropriate to refer to the column of the list, you would also be required to refer to the row, otherwise you'd always get what's in the first (?) row.

    Please don't say "doesn't work" - it doesn't help much. Try this (and note that I used code tags for more than a few lines)
    Code:
    Private Sub cboInventory_AfterUpdate()
     Dim MyInventory As String
     MyInventory = "Select * from ProductsT where ([ProductID] = " & Me.cboInventory & ")"
     Me.DS.Form.RecordSource = MyInventory
     Me.DS.Form.Requery
    End Sub
    However, I have to wonder why your subform isn't linked to your main form (see Child vs Parent properties for subform/form relationships) and the combo just chooses the value for that linked field. You'd still have to requery the subform, but why play around with form recordsources in this case?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Review this tutorial http://allenbrowne.com/ser-62.html
    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.

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

Similar Threads

  1. Refreshing Subform Data
    By swenger in forum Forms
    Replies: 16
    Last Post: 06-09-2016, 12:38 PM
  2. Refreshing a subform
    By RobRoy in forum Forms
    Replies: 8
    Last Post: 07-22-2013, 04:19 PM
  3. Replies: 7
    Last Post: 11-13-2012, 01:44 PM
  4. SubForm not refreshing
    By hawkins in forum Access
    Replies: 2
    Last Post: 08-16-2011, 04:30 PM
  5. Refreshing a subform?
    By Shag84 in forum Forms
    Replies: 4
    Last Post: 08-07-2011, 10:07 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