How to refresh data in a form after relink to new tables

  • Thread starter Thread starter DianePDavies
  • Start date Start date
D

DianePDavies

I have a frontend with a form to display data from a backend. The form has
two subforms.

On my form I can indicate the path to the backebd - i.e. I can display data
from different backends (of the same format of course).

If I select a new backend and makes a succesful linking to this database I
can see the new data if I first shut down my form and reopens it.

I would like to display the new data without having to close the form first.
I have tried to requery data , but it does not work.

Any ideas?
 
I link to the new table with the following routine:

Public Function RefreshLinks(strDirName As String, count As Control, AppName
As String, Optional force As Boolean = False) As Boolean
' Refresh links to the supplied database. Return True if successful.
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer
Dim tables As Integer
' Loop through all tables in the database.
i = 0
tables = 0
Set dbs = CurrentDb
DoCmd.Hourglass True
count = "0.0 % complete"
' MsgBox "All tables need to be connected to the data source", , AppName
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.connect) > 0 Then
tables = tables + 1
End If
Next tdf
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.connect) > 0 Then
i = i + 1
tdf.connect = ";DATABASE=" & strDirName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
count = Format(i * 100 / tables, "#0.0") & " % complete"
Call pause(1)
If Err <> 0 Then
MsgBox ("can't link to table : " & tdf.Name)
MsgBox Err.Description
RefreshLinks = False
DoCmd.Hourglass False
Exit Function
End If
End If
Next tdf
DoCmd.Hourglass False
RefreshLinks = True ' Relinking complete.
End Function

When that is done, I requiery the form and the subforms:

Me.Requery
Me.IssueList.Requery
Me.Contributions.Requery

where IssueList and Contributions are my two sub-forms
 
never worked to requery the subforms while the mainform is open. It is like
the data source of a subform is kept in memory still after the underlying
table has in fact been replaced as I have linked to another backend.

Now I solve the problem by simply closing and re.openeing the form... its
not as elegant - but it works.
--
Diane


BruceM via AccessMonster.com said:
Try requerying the Form property of subform controls rather than the controls
themselves (if I understand correctly that IssueList and Contributions are
subform controls:

Me.IssueList.Form.Requery
Me.Contributions.Form.Requery

Count is a reserved word, so I wouldn't use it as an argument for the
function. Maybe you could use ctl or something like that. I assume you
include the name of an unbound text box in the function call, and that is
where you display the percentage that is completed.
I link to the new table with the following routine:

Public Function RefreshLinks(strDirName As String, count As Control, AppName
As String, Optional force As Boolean = False) As Boolean
' Refresh links to the supplied database. Return True if successful.
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer
Dim tables As Integer
' Loop through all tables in the database.
i = 0
tables = 0
Set dbs = CurrentDb
DoCmd.Hourglass True
count = "0.0 % complete"
' MsgBox "All tables need to be connected to the data source", , AppName
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.connect) > 0 Then
tables = tables + 1
End If
Next tdf
For Each tdf In dbs.TableDefs
' If the table has a connect string, it's a linked table.
If Len(tdf.connect) > 0 Then
i = i + 1
tdf.connect = ";DATABASE=" & strDirName
Err = 0
On Error Resume Next
tdf.RefreshLink ' Relink the table.
count = Format(i * 100 / tables, "#0.0") & " % complete"
Call pause(1)
If Err <> 0 Then
MsgBox ("can't link to table : " & tdf.Name)
MsgBox Err.Description
RefreshLinks = False
DoCmd.Hourglass False
Exit Function
End If
End If
Next tdf
DoCmd.Hourglass False
RefreshLinks = True ' Relinking complete.
End Function

When that is done, I requiery the form and the subforms:

Me.Requery
Me.IssueList.Requery
Me.Contributions.Requery

where IssueList and Contributions are my two sub-forms
I have a frontend with a form to display data from a backend. The form has
two subforms.
Click to expand...
[quoted text clipped - 9 lines]
Any ideas?
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top