Recalculate Calculated Control on Form

[Linda]

Hi,

(Access 97)

On my Menu form I have a button (cmdRefreshLinks) that
refreshes/changes the linked tables in my database, and a calculated
control (txtDataSource) that uses a custom function to display the
location of the back-end database. (This is all done using Dev
Ashish's 'Relink Tables' code from
http://www.mvps.org/access/tables/tbl0009.htm.)

Both of these controls do exactly what they're supposed to, with one
exception. After I've refreshed the table links using
cmdRefreshLinks, txtDataSource doesn't update correctly. I've tried
virtually all combinations of the below lines of code in the Click
event of cmdRefreshLinks after it's carried out its refresh function:

--------
CurrentDb.TableDefs.Refresh
DoEvents
Me.Recalc
or Me.txtDataSource.Requery
or Me.Requery
or Me.Refresh
or Me.txtDataSource.Recalc (doesn't compile)
--------

When I use Me.txtDataSource.Requery, I SEE txtDataSource requery, but
it returns the incorrect (i.e. old) path. This is why I tried
DoEvents - I thought it might have needed a second to catch up with
itself.

I've now resorted to closing and reopening the form at the end of the
cmdRefreshLinks Click event, but surely there must be a better way?!

Thanks,
Linda Patterson

[Don Leverton]

Hi Linda,

This custom function of yours... does it parse the data path from one of the
linked table's .Connect string?
If not then I think that a new function that does just that is what you
need.

In fact, isn't that what the "fParsePath" function inside of Dev's code
does???
Take the name of one of the tables that you've just re-linked, and
debug.print it's .Connect String

eg.
******************* In the Debug window *****************
?CurrentDB.TableDefs("tblPartsInventory").Connect
returns:
;DATABASE=C:\Documents and Settings\Don\My Documents\Mck2004_be.mdb

Then use the fParsePath() function to parse out the data path.
?fParsePath(";DATABASE=C:\Documents and Settings\Don\My
Documents\Mck2004_be.mdb")
returns:
C:\Documents and Settings\Don\My Documents\Mck2004_be.mdb

-- Or even just write it as a combination --
?fParsePath(CurrentDB.TableDefs("tblPartsInventory ").connect)
C:\Documents and Settings\Don\My Documents\Mck2004_be.mdb
************************************

As a control source for "txtDataSource" =
fParsePath(CurrentDB.TableDefs("YourLinkedTableNam eHere").connect)

--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Linda" <li*************@hertshighways.org.uk> wrote in message
news:a6**************************@posting.google.c om...

Hi,

(Access 97)

On my Menu form I have a button (cmdRefreshLinks) that
refreshes/changes the linked tables in my database, and a calculated
control (txtDataSource) that uses a custom function to display the
location of the back-end database. (This is all done using Dev
Ashish's 'Relink Tables' code from
http://www.mvps.org/access/tables/tbl0009.htm.)

Both of these controls do exactly what they're supposed to, with one
exception. After I've refreshed the table links using
cmdRefreshLinks, txtDataSource doesn't update correctly. I've tried
virtually all combinations of the below lines of code in the Click
event of cmdRefreshLinks after it's carried out its refresh function:

--------
CurrentDb.TableDefs.Refresh
DoEvents
Me.Recalc
or Me.txtDataSource.Requery
or Me.Requery
or Me.Refresh
or Me.txtDataSource.Recalc (doesn't compile)
--------

When I use Me.txtDataSource.Requery, I SEE txtDataSource requery, but
it returns the incorrect (i.e. old) path. This is why I tried
DoEvents - I thought it might have needed a second to catch up with
itself.

I've now resorted to closing and reopening the form at the end of the
cmdRefreshLinks Click event, but surely there must be a better way?!

Thanks,
Linda Patterson

[Linda Patterson]

Hi Don,

Well spotted - fParsePath is exactly what I'm using:

txtDataSource: =fParsePath([CurrentDb].[TableDefs]("prj_Projects").[Connect])

And it works fine every time except when I've just refreshed the table
links. I'd understand if it needed the TableDefs refreshed AND a
txtDataSource.Requery, but I've tried them and it still doesn't return
the new path.

I'll investigate further and see what Debug.Print-ing the function
shows me, but surely after a CurrentDb.TableDefs.Refresh it should be
correct?

Yours frustratedly,
Linda
"Don Leverton" <le****************@telusplanet.net> wrote in message news:<YRURc.74935$T_6.71711@edtnps89>...

Hi Linda,

This custom function of yours... does it parse the data path from one of the
linked table's .Connect string?
If not then I think that a new function that does just that is what you
need.

In fact, isn't that what the "fParsePath" function inside of Dev's code
does???
Take the name of one of the tables that you've just re-linked, and
debug.print it's .Connect String

eg.
******************* In the Debug window *****************
?CurrentDB.TableDefs("tblPartsInventory").Connect
returns:
;DATABASE=C:\Documents and Settings\Don\My Documents\Mck2004_be.mdb

Then use the fParsePath() function to parse out the data path.
?fParsePath(";DATABASE=C:\Documents and Settings\Don\My
Documents\Mck2004_be.mdb")
returns:
C:\Documents and Settings\Don\My Documents\Mck2004_be.mdb

-- Or even just write it as a combination --
?fParsePath(CurrentDB.TableDefs("tblPartsInventory ").connect)
C:\Documents and Settings\Don\My Documents\Mck2004_be.mdb
************************************

As a control source for "txtDataSource" =
fParsePath(CurrentDB.TableDefs("YourLinkedTableNam eHere").connect)

--
HTH,
Don
=============================
E-Mail (if you must)
My*****@Telus.net

Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Linda" <li*************@hertshighways.org.uk> wrote in message
news:a6**************************@posting.google.c om...

Hi,

(Access 97)

On my Menu form I have a button (cmdRefreshLinks) that
refreshes/changes the linked tables in my database, and a calculated
control (txtDataSource) that uses a custom function to display the
location of the back-end database. (This is all done using Dev
Ashish's 'Relink Tables' code from
http://www.mvps.org/access/tables/tbl0009.htm.)

Both of these controls do exactly what they're supposed to, with one
exception. After I've refreshed the table links using
cmdRefreshLinks, txtDataSource doesn't update correctly. I've tried
virtually all combinations of the below lines of code in the Click
event of cmdRefreshLinks after it's carried out its refresh function:

--------
CurrentDb.TableDefs.Refresh
DoEvents
Me.Recalc
or Me.txtDataSource.Requery
or Me.Requery
or Me.Refresh
or Me.txtDataSource.Recalc (doesn't compile)
--------

When I use Me.txtDataSource.Requery, I SEE txtDataSource requery, but
it returns the incorrect (i.e. old) path. This is why I tried
DoEvents - I thought it might have needed a second to catch up with
itself.

I've now resorted to closing and reopening the form at the end of the
cmdRefreshLinks Click event, but surely there must be a better way?!

Thanks,
Linda Patterson