Hide Navigation Pane Part III
Good Afternoon all
I was given a subroutine (below by pteranodon72) to hide the navigation pane when I switch back and forth between Normal and Archive sides of the database. The old code worked but opened the Navigation pane when ever switching to Archive, and it would stay open when switching back. I have recently discovered that calling the sub routine to switch back - drops six links to the archive side that are maintained on the normal side.
The old code that works correctly but leaves the navigation pane open is:
If SetActive Then ' Set Archive mode
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, rs!TableName
Else 'Set Normal mode
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!TblDbName, acTable, rs!TableName, rs!TableName
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, "Arc" & rs!TableName
End If
The new code that calls the subroutine to - hide the Nav Pane is:
LinkOrRelinkAccessTable Forms!Settings!ArcDbName, "Arc" & rs!TableName, rs!TableName
Else
'Set Normal mode
LinkOrRelinkAccessTable Forms!Settings!TblDbName, rs!TableName, rs!TableName
When I add this line after:
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, "Arc" & rs!TableName
It reattaches the necessary tables - but it opens up the nav pane.
Can that new code that hides the pane - be modified to reattach the necessary tables - or do I need to find another solution?
I was given a subroutine (below by pteranodon72) to hide the navigation pane when I switch back and forth between Normal and Archive sides of the database. The old code worked but opened the Navigation pane when ever switching to Archive, and it would stay open when switching back. I have recently discovered that calling the sub routine to switch back - drops six links to the archive side that are maintained on the normal side.
The old code that works correctly but leaves the navigation pane open is:
If SetActive Then ' Set Archive mode
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, rs!TableName
Else 'Set Normal mode
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!TblDbName, acTable, rs!TableName, rs!TableName
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, "Arc" & rs!TableName
End If
The new code that calls the subroutine to - hide the Nav Pane is:
LinkOrRelinkAccessTable Forms!Settings!ArcDbName, "Arc" & rs!TableName, rs!TableName
Else
'Set Normal mode
LinkOrRelinkAccessTable Forms!Settings!TblDbName, rs!TableName, rs!TableName
When I add this line after:
DoCmd.TransferDatabase acLink, "Microsoft Access", Forms!Settings!ArcDbName, acTable, "Arc" & rs!TableName, "Arc" & rs!TableName
It reattaches the necessary tables - but it opens up the nav pane.
Can that new code that hides the pane - be modified to reattach the necessary tables - or do I need to find another solution?
Any time you use the TransferDatabase method to link tables, it will unhide the navigation pane.
Instead, I use something similar to the following:
Public Sub LinkTable(TableName as string, Filename as string)
Dim tdfDest as DAO.Tabledef
Set tdfDest = dbDest.CreateTableDef(Tabl
tdfDest.Connect = ";DATABASE=" & Filename
tdfDest.SourceTableName = Tablename
dbDest.TableDefs.Append tdfDest
End Sub
Instead, I use something similar to the following:
Public Sub LinkTable(TableName as string, Filename as string)
Dim tdfDest as DAO.Tabledef
Set tdfDest = dbDest.CreateTableDef(Tabl
tdfDest.Connect = ";DATABASE=" & Filename
tdfDest.SourceTableName = Tablename
dbDest.TableDefs.Append tdfDest
End Sub
The only way I have found to completely hide the navigation pane is to run Access in runtime mode. The downside is that you have to built in a lot of the UI.
To learn how to force Access into runtime mpde see: What is the Access Runtime Version?
To learn how to force Access into runtime mpde see: What is the Access Runtime Version?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try this:
' reattached tables
LinkOrRelinkAccessTable Forms!Settings!ArcDbName, "Arc" & rs!TableName, rs!TableName
' make sure nav pane is hidden
DisplayNavePane FalseASKER
This is the sub-routine that switches from Normal to Archive and Back again:
Sub LinkOrRelinkAccessTable(st
Dim db As DAO.Database
Dim tdfLocal As DAO.TableDef
Set db = CurrentDb()
If TableExists(strLocalTableN
'point to existing tabledef
Set tdfLocal = db.TableDefs(strLocalTable
'change tabledef properties
tdfLocal.Connect = ";DATABASE=" & strBackend
tdfLocal.SourceTableName = strRemoteTableName
'refresh the link so new properties are reflected
tdfLocal.RefreshLink
Else
'create a new tabledef object
Set tdfLocal = db.CreateTableDef(strLocal
'set tabledef properties
tdfLocal.Connect = ";DATABASE=" & strBackend
tdfLocal.SourceTableName = strRemoteTableName
'append tabledef to collection
db.TableDefs.Append tdfLocal
End If
End Sub
HiTechCoach - when I uses the above routine to switch between Archive and Norm - it hides the Nav Pane fine - however, I lose 6 linked tables to the archive side - that stay linked even on the Normal side for purposes of archiving specific data. Thats where the problem is - those six tables need to be re-linked
Fyed - that sub-routine works - however, when switching between Normal and Archive - the Navigation Pane is displayed while it changes, and hides it after it changes. I appreciate that it works - I am just trying to make the transition a little cleaner
I really appreciate your help people
Sub LinkOrRelinkAccessTable(st
Dim db As DAO.Database
Dim tdfLocal As DAO.TableDef
Set db = CurrentDb()
If TableExists(strLocalTableN
'point to existing tabledef
Set tdfLocal = db.TableDefs(strLocalTable
'change tabledef properties
tdfLocal.Connect = ";DATABASE=" & strBackend
tdfLocal.SourceTableName = strRemoteTableName
'refresh the link so new properties are reflected
tdfLocal.RefreshLink
Else
'create a new tabledef object
Set tdfLocal = db.CreateTableDef(strLocal
'set tabledef properties
tdfLocal.Connect = ";DATABASE=" & strBackend
tdfLocal.SourceTableName = strRemoteTableName
'append tabledef to collection
db.TableDefs.Append tdfLocal
End If
End Sub
HiTechCoach - when I uses the above routine to switch between Archive and Norm - it hides the Nav Pane fine - however, I lose 6 linked tables to the archive side - that stay linked even on the Normal side for purposes of archiving specific data. Thats where the problem is - those six tables need to be re-linked
Fyed - that sub-routine works - however, when switching between Normal and Archive - the Navigation Pane is displayed while it changes, and hides it after it changes. I appreciate that it works - I am just trying to make the transition a little cleaner
I really appreciate your help people
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The original application in the 2000-2003 format, did not display the database object window - just the front-side of the application. When it was converted to a 2007 version, the application would reveal the Navigation pane when opening. If you select to hide the Nav Pane under options, it only hides it when opening the ap. When you switch to the archive side (as so frequently required) - the navigation pane is revealed, and it stays revealed when switching back. It has to do making the navigation pane unavailable to the end users.
If you use the tabledef object (and code similar to what I provided above or the LinkOrRelinkAccessTable subroutine) to modify your links the navigation pane instead of the TransferDatabase method, the navigation pane should not be revealed (at least it isn't on my computer).
I'm with TheHiTechCoach regarding your "losing 6 linked tables". That has to have something to do with the method you are using to do the linking, and has nothing to do with the NavPane.
I'm with TheHiTechCoach regarding your "losing 6 linked tables". That has to have something to do with the method you are using to do the linking, and has nothing to do with the NavPane.
<<The original application in the 2000-2003 format, did not display the database object window - just the front-side of the application. When it was converted to a 2007 version, the application would reveal the Navigation pane when opening.>>
I have experienced the same results with Access 2007. Lots of people have the same issue I have not converted lots of mdb's to the newer format *.accdb) just to avoid this issue when not using Access in Runtime mode.
Have you tried fyed's suggestion?
I have experienced the same results with Access 2007. Lots of people have the same issue I have not converted lots of mdb's to the newer format *.accdb) just to avoid this issue when not using Access in Runtime mode.
Have you tried fyed's suggestion?
ASKER
I did use fyed's suggestion at first - and then someone suggested the sub routine that caused the issue.
Although fyed's suggestion works - the nav pane will appear and then disappear in a series of flashes during the change from normal to archive and back. I am trying to find a way to avoid then flashing of nav pane. I believe it flashes because there are several sub-routines that the application runs through before it arrives at the call to the sub routine DisplayNavPane. It works incredibly well in smaller applications with no flashing. That sub-routine will be used for now. Thank you all for your help
Although fyed's suggestion works - the nav pane will appear and then disappear in a series of flashes during the change from normal to archive and back. I am trying to find a way to avoid then flashing of nav pane. I believe it flashes because there are several sub-routines that the application runs through before it arrives at the call to the sub routine DisplayNavPane. It works incredibly well in smaller applications with no flashing. That sub-routine will be used for now. Thank you all for your help
"the nav pane will appear and then disappear in a series of flashes"
Then you need to look into those other subroutines. If you are using TransferDatasheet or Transferspreadsheet to link or import any external data, that will cause the navigation pane to become visible. I would simply search for those two term throughout your code (and macros if you use them) and replace those with other alternatives for linking or importing.
Then you need to look into those other subroutines. If you are using TransferDatasheet or Transferspreadsheet to link or import any external data, that will cause the navigation pane to become visible. I would simply search for those two term throughout your code (and macros if you use them) and replace those with other alternatives for linking or importing.
Where is the code you use to hide the navigation pane?