Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2010

    Question Unanswered: Form not requerying backend data after file copy


    I have an odd problem and so far no luck finding a solution on google or here with the search function.

    I have an application split into a frontend and several backend data files. One of these linked backend data files is a copy of a master personnel file that resides on the network and is updated by administrative types. The users of this application can hit a Copy button from the personnel form to update their local copy of the personnel data before they go on travel to ensure they have the latest data.

    The code there is simple enough. Here's a stripped down version...

    'Command Button on Form
    Private Sub cmdUpdateData_Click()
        ' Copy personnel data over the network
        updatePersonnelData Me.Name
    End Sub
    ' Code in a module
    Public Function updatePersonnelData(formName As String)
        Dim fs as Object
        ' Close the form
        DoCmd.Close acForm, formName, acSaveNo
        Set fs = CreateObject("Scripting.FileSystemObject")
        ' Check for file existence (and network connection) and some error handling
        If fs.FileExists(NETWORK_FILE_PATH) Then
            If fs.FolderExists(LOCAL_FILE_DIR) = False Then
                ' Create the local directory structure
            End If
            ' Make a local copy of the master personnel data file
            fs.CopyFile NETWORK_FILE_PATH, LOCAL_FILE_PATH, True
        End If
        ' Open the form again
        DoCmd.OpenForm formName
        Set fs = Nothing
    End Function
    Now here's the fun part. When the form opens again, the data is not updated. If I manually close the form and manually open the form again, the new data is there. If I comment out the DoCmd.OpenForm at the end, click the update button and manually open the form, the new data is there.

    Is there something that Access does with the external data files which does not occur while code is executing? Is there a command to force it to happen so I can open the form again programmatically rather than being forced to just show a MsgBox kindly asking the user to open the form again themselves?

    I have tried refreshing and requerying the form after it is opened and that does not help. I have tried Currentdb.TableDefs.Refresh before opening the form again and that does not work. I tried a little loop through all TableDefs in CurrentDb doing .RefreshLink on each one (with a connect string) before opening the form again and no luck.

    For the moment I'm going with the MsgBox approach because I've already spent more time than it's worth for this project, but I'd still really love to figure out what is going on here for my own knowledge in the future. If you have any ideas, please let me know and I'll give them a shot.

    Thanks for reading.

  2. #2
    Join Date
    Feb 2004
    Chicago, IL
    My guess is it is a timing issue. The copy is probably run asynchronously and the form open occurs milliseconds are the copy has started.

    I think the messing with the form's RecordSource will force it to Refresh the data. Another option is changing the form's RecordType. I think that will also force the form to get new data. And finally you might be able to put in a pause after to the copy to wait for the copy to complete.

    You can use the below procedure to check if it is a timing thing. I would pause for 30 seconds and then open the form to see if the data is updated. Then you can mess with it to get a resonable time.

    Public Sub Pause(sinSeconds As Long)
        Dim sinStartTime As Single
        Dim sinFinishTime As Single
        Dim lngCount As Long
        'Get the current number of seconds since the beginning of the day
        sinStartTime = Timer
        sinFinishTime = sinStartTime + sinSeconds
        'Adjust for midnight
        If sinFinishTime > 86400 Then sinFinishTime = sinFinishTime - 86400
        Do Until Timer > sinFinishTime
            lngCount = 0
    End Sub

  3. #3
    Join Date
    Oct 2010
    Thanks for having a read. Sorry I did not get back here for a while but I've been on other projects.

    I played around for a few minutes earlier and found the problem to be with Snapshot recordset types. I did not dig further into why but as soon as I set the form to Dynaset, which I don't need, it works fine.

    So again, as a Snapshot form, closing the form via code, copying new data via filesystem copy code, and re-opening the form via code shows old data. Closing the form via code, copying new data via filesystem copy code, and making the user re-open the form manually shows them new data.

    As a Dynaset form, I'm not running into this issue.

    That still makes no sense to me but I can chalk it up to just another quirk I don't understand and not use Snapshots anymore.

    Sorry for the unspectacular conclusion.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts