Results 1 to 11 of 11
  1. #1
    DarkWolff is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    29

    Question Changed Datasource not shown till table/form closes and opens


    I have a linked table that has it's SourceTableName updated (via deleting the link and recreating it) on a form that also displays the table's contents in a subform. However the subform's contents do not update until you actually close the form and open it again. The same is true for the table if open it manually. I've tried refreshing, requerying, and even repainting the table afterwards but nothing seems to be working.

    Can anyone suggest a way to fix this? I can confirm the links has in fact changed, but nothing happens till I reopen the form or table.

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    On my main form, I have a text box that displays the BE location. I have code on the double click event of that text box to select and relink to a different BE.
    To complete the re-link, I have to close and re-open the main form to connect to the newly linked BE. (BTW, the double click event re-link is only available to me)
    Code:
    .
    .
    DoCmd.Close acForm, Me.Name ' (form name is "startup")
    DoCmd.OpenForm "startup",acNormal,,,, acWindowNormal
    
    'error handler code
    .
    .
    End Sub

  3. #3
    DarkWolff is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    29
    Thanks for the post. I tried that as well but the subform doesn't load properly when I do so (it just shows up blank).

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So do my sub-forms and list boxes. I have to re-select a record in the main form, which then refreshes the sub-form/list boxes.
    What do you do when the dB is first opened?

    Maybe
    save a bookmark to the current record,
    close the form,
    open the form,
    move to the bookmarked record,
    refresh

  5. #5
    DarkWolff is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    29
    My main form doesn't have any records. The main form only has a combobox, a subform in a datasheet view, and a close button.

    I may just move the combobox to the previous (menu) form as a workaround, but would prefer having it on this form if possible.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    So after you select something with the combo box, the sub-form displays records?

  7. #7
    DarkWolff is offline Novice
    Windows XP Access 2007
    Join Date
    Feb 2012
    Posts
    29
    Right now it defaults to showing the last data source when you open it from the menu. When you choose a different table in the the combobox it changes the source of the link table by deleting the link and recreating it with a new source.

    If I add code to close and reopen it, the subform won't display no matter what I do. I assume this is because Access believes the subform is still in use.
    Last edited by DarkWolff; 07-24-2013 at 12:22 PM.

  8. #8
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think I misunderstood what you were doing.

    You have an unbound combo box where the row source is the tables of the database.
    You select a table from the combo box, which changes the record source of a sub-form.

    Would you post the code you are using?

  9. #9
    DarkWolff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    29
    Quote Originally Posted by ssanfu View Post
    I think I misunderstood what you were doing.

    You have an unbound combo box where the row source is the tables of the database.
    You select a table from the combo box, which changes the record source of a sub-form.

    Would you post the code you are using?
    Yes that's correct. I get the list of tables from another function that populations the combobox onform Load.

    Here is the code for the combobox AfterUpdate event and the function it calls. Not I'll be making the function general later; I'm just trying to get the functionality I want first, then I'll refactor it.

    Code:
    Private Sub cboArchivedTableList_AfterUpdate()
        ChangeArchiveTable (cboArchivedTableList.Value)
        DoCmd.Close acForm, Me.Name ' (form name is "startup")
        DoCmd.OpenForm "frmArchiveViewer", acNormal, , , , acWindowNormal
    End Sub
    Code:
    Sub ChangeArchiveTable(newTable As String)
        Dim db As Database
        Dim t As TableDef
        Dim originalConnect As String
        
        Set db = CurrentDb
        
        originalConnect = CurrentDb.TableDefs("tblArchive").Connect
        
        db.TableDefs.Delete "tblArchive"
            
        Set t = db.CreateTableDef("tblArchive")
          
        With t
            .Connect = originalConnect
            .SourceTableName = newTable
        End With
        
        db.TableDefs.Append t
    
    End Sub

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I get the list of tables from another function that populations the combobox onform Load.
    Do all of the tables in the combo box have the same structure?

    I ask because you would need a lot more code to create the controls and bind them to fields in the record source if each table had different field names and number of fields.

    If all of the tables have the same structure - field names and number of fields - it might be easier to change the query def.

  11. #11
    DarkWolff is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2012
    Posts
    29
    Quote Originally Posted by ssanfu View Post
    Do all of the tables in the combo box have the same structure?

    I ask because you would need a lot more code to create the controls and bind them to fields in the record source if each table had different field names and number of fields.

    If all of the tables have the same structure - field names and number of fields - it might be easier to change the query def.
    Yes they all have the same structure. The tables in the combobox are archived versions of a single table. Every month we are updating the data in this table and we need an archive of the previous version. The archived tables sit on a backend which is why I'm updating the link whenever the user wants to see a different table; I don't want to have to release a new frontend every month with a new linked table.

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

Similar Threads

  1. Data entered into form is not shown in Table
    By engr_saud1 in forum Forms
    Replies: 5
    Last Post: 04-07-2013, 06:34 AM
  2. Replies: 1
    Last Post: 01-26-2012, 05:25 PM
  3. Form opens table in datasheet view
    By franklbl in forum Forms
    Replies: 9
    Last Post: 03-23-2011, 09:43 PM
  4. Form closes after mssage box
    By liam01752 in forum Programming
    Replies: 1
    Last Post: 12-06-2010, 03:30 PM
  5. Change to Form Closes Access
    By jbhjm in forum Forms
    Replies: 5
    Last Post: 04-22-2009, 01:04 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