Hiding a button on a form before copying database

R

rhese

I've got a database system that is composed of 3 levels of databases:
National, State, and Local. Each mdb is an exact copy of the other,
except for the amount of data contained. The National contains all
records, the State - only records for that state, and the Local - only
records for that office. The table structure and all the forms & code
are the same.

The National and State level databases have a button that allow the
user to export out the next lower level. i.e., National wants to
create new State-level databases and State-level wants to create new
Local-level databases, and I have code that will do this splendidly.
However, the same "Export Sub Databases" button shows up in the Local-
level database, and my client is concerned this will cause confusion.

I have tried to code in a routine at the beginning of the Export Sub
Databases process that hides the button in the current (State-level)
database before using an iterative .Copyfile loop to create the Local-
level databases, and then restore the button at the end of the Sub.
But, even though the button does disappear in the current database
before the copying begins, it is still visible in each Local-level
database.

Can anyone tell me why this doesn't work? I'm working in Access 2003
but saving these databases as Access 2000. Below is the code, with
the portions dealing with hiding the button bracketed by "***". Thank
you for any help you can provide.

--Rhese

Private Sub bExportLocal_Click()
On Error GoTo bExport_LocalErr
Dim strSQL As String
Dim strDelSQL As String
Dim strLocal As String
Dim strState As String
Dim strSourceDb As String
Dim strDestinationDb As String
Dim strPath As String
Dim intRev As Long
Dim fs As Object
Dim qdfLocals As QueryDef
Dim rstLocals As DAO.Recordset
Dim rsState As DAO.Recordset
Dim dbLocal As Database

DoCmd.Hourglass True
'****************************************************
'Temporarily hide the Export Sub Databases button on fExport_Database
in the current database
'so it won't show up in the local databases.
Forms!fExport_Database!bExit.SetFocus
Forms!fExport_Database!bExportSubs.Visible = False
Forms!fExport_Database!lblExportSubs.Visible = False
Forms!fExport_Database.Repaint
DoCmd.Save acForm, "fExport_Database"
'****************************************************
'Get the name and path of current database
strSourceDb = CurrentDb().Name
intRev = InStrRev(strSourceDb, "\", , 1)
strPath = Left(strSourceDb, intRev)

'Get unique list of Offices for this State that have data
strSQL = "SELECT tSpecies_In_FDOffice.OfficeCode FROM
tSpecies_In_FDOffice " & _
"GROUP BY tSpecies_In_FDOffice.OfficeCode ORDER BY
tSpecies_In_FDOffice.OfficeCode;"

Set qdfLocals = CurrentDb().CreateQueryDef("", strSQL)
Set rstLocals = qdfLocals.OpenRecordset(dbOpenForwardOnly)

'Figure out which State this is
strLocal = rstLocals![OfficeCode]

Set rsState = CurrentDb().OpenRecordset("tFD_Offices", dbOpenDynaset)

With rsState
.FindFirst "[OfficeCode] = " & "'" & strLocal & "'"
strState = ![StateCode]
.Close
End With

'Loop though Local Offices and make copies of the database with the
State and Office as part of the name
With rstLocals
While Not .EOF
strDestinationDb = strPath & strState & "_" & strLocal & "_" &
Str(Year(Now())) & ".mdb"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile strSourceDb, strDestinationDb
If Err.Number = 0 Then 'Copy was successful
'Now open new db, and delete out anything that doesn't
relate to this Office
Set dbLocal = DBEngine.OpenDatabase(strDestinationDb,
True, False)
strDelSQL = "DELETE tSpecies_In_State.* FROM
tSpecies_In_State WHERE (((tSpecies_In_State.StateCode)<>'" & strState
& "'));"
dbLocal.Execute strDelSQL, dbFailOnError
strDelSQL = "DELETE tSpecies_In_FDOffice.* FROM
tSpecies_In_FDOffice WHERE (((tSpecies_In_FDOffice.OfficeCode)<>'" &
strLocal & "'));"
dbLocal.Execute strDelSQL, dbFailOnError
strDelSQL = "DELETE tGlobal.* FROM tGlobal WHERE
(((tGlobal.OfficeCode)<>'" & strLocal & "'));"
dbLocal.Execute strDelSQL, dbFailOnError
strDelSQL = "DELETE tFD_Offices.* FROM tFD_Offices WHERE
(((tFD_Offices.OfficeCode)<>'" & strLocal & "'));"
dbLocal.Execute strDelSQL, dbFailOnError
'Change any Record Status flags from "New" or "Updated" to
"Accepted"
strSQL = "UPDATE tPopCodeSpecies SET
tPopCodeSpecies.Sp_Record_Status = 'Accepted " & Str(Now()) & "'" & _
" WHERE (((tPopCodeSpecies.Sp_Record_Status) Like
'New*')) OR (((tPopCodeSpecies.Sp_Record_Status) Like 'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
strSQL = "UPDATE tSpecies_In_State SET
tSpecies_In_State.St_Record_Status = 'Accepted " & Str(Now()) & "'" &
_
" WHERE (((tSpecies_In_State.St_Record_Status) Like
'New*')) OR (((tSpecies_In_State.St_Record_Status) Like 'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
strSQL = "UPDATE tSpecies_In_FDOffice SET
tSpecies_In_FDOffice.Record_Status = 'Accepted " & Str(Now()) & "'" &
_
" WHERE (((tSpecies_In_FDOffice.Record_Status) Like
'New*')) OR (((tSpecies_In_FDOffice.Record_Status) Like 'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
strSQL = "UPDATE tSpecies_FDOffice_Year SET
tSpecies_FDOffice_Year.FY_RecordStatus = 'Accepted " & Str(Now()) &
"'" & _
" WHERE (((tSpecies_FDOffice_Year.FY_RecordStatus)
Like 'New*')) OR (((tSpecies_FDOffice_Year.FY_RecordStatus) Like
'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
dbLocal.Close
'Compact the new database
DBEngine.CompactDatabase strDestinationDb, strPath &
"tempDb"
fs.DeleteFile strDestinationDb
fs.CopyFile strPath & "tempDb.mdb", strDestinationDb
fs.DeleteFile strPath & "tempDb.mdb"
End If
.MoveNext
If Not .EOF Then
strLocal = ![OfficeCode]
End If
Wend
.Close
End With
'****************************************************
'Get the Export Sub Databases button back on fExport_Database in the
current database
Forms!fExport_Database!bExportSubs.Visible = True
Forms!fExport_Database!lblExportSubs.Visible = True
Forms!fExport_Database.Repaint
DoCmd.Save acForm, "fExport_Database"
'****************************************************
bExport_LocalExit:
DoCmd.Hourglass False
DoCmd.Close
Exit Sub

bExport_LocalErr:
MsgBox Err.Number & ", " & Err.Description
Resume bExport_LocalExit

End Sub
 
J

Jeff Boyce

Rhese

If you export a copy of some of the data from your top level, and then the
top level data is changed, how do the lower levels get re-synchronized?

If your exported copies (i.e., lower levels) are updated due to State and/or
Local conditions, how does the National level get re-synchronized?

When you say you are exporting subsets of data, are you sending those State
and Local databases off to State/Local users, or are you using this
mechanism to narrow the scope of what you are looking at? (if the latter,
you can much more easily do this with queries)

More info, please...


Regards

Jeff Boyce
Microsoft Office/Access MVP

I've got a database system that is composed of 3 levels of databases:
National, State, and Local. Each mdb is an exact copy of the other,
except for the amount of data contained. The National contains all
records, the State - only records for that state, and the Local - only
records for that office. The table structure and all the forms & code
are the same.

The National and State level databases have a button that allow the
user to export out the next lower level. i.e., National wants to
create new State-level databases and State-level wants to create new
Local-level databases, and I have code that will do this splendidly.
However, the same "Export Sub Databases" button shows up in the Local-
level database, and my client is concerned this will cause confusion.

I have tried to code in a routine at the beginning of the Export Sub
Databases process that hides the button in the current (State-level)
database before using an iterative .Copyfile loop to create the Local-
level databases, and then restore the button at the end of the Sub.
But, even though the button does disappear in the current database
before the copying begins, it is still visible in each Local-level
database.

Can anyone tell me why this doesn't work? I'm working in Access 2003
but saving these databases as Access 2000. Below is the code, with
the portions dealing with hiding the button bracketed by "***". Thank
you for any help you can provide.

--Rhese

Private Sub bExportLocal_Click()
On Error GoTo bExport_LocalErr
Dim strSQL As String
Dim strDelSQL As String
Dim strLocal As String
Dim strState As String
Dim strSourceDb As String
Dim strDestinationDb As String
Dim strPath As String
Dim intRev As Long
Dim fs As Object
Dim qdfLocals As QueryDef
Dim rstLocals As DAO.Recordset
Dim rsState As DAO.Recordset
Dim dbLocal As Database

DoCmd.Hourglass True
'****************************************************
'Temporarily hide the Export Sub Databases button on fExport_Database
in the current database
'so it won't show up in the local databases.
Forms!fExport_Database!bExit.SetFocus
Forms!fExport_Database!bExportSubs.Visible = False
Forms!fExport_Database!lblExportSubs.Visible = False
Forms!fExport_Database.Repaint
DoCmd.Save acForm, "fExport_Database"
'****************************************************
'Get the name and path of current database
strSourceDb = CurrentDb().Name
intRev = InStrRev(strSourceDb, "\", , 1)
strPath = Left(strSourceDb, intRev)

'Get unique list of Offices for this State that have data
strSQL = "SELECT tSpecies_In_FDOffice.OfficeCode FROM
tSpecies_In_FDOffice " & _
"GROUP BY tSpecies_In_FDOffice.OfficeCode ORDER BY
tSpecies_In_FDOffice.OfficeCode;"

Set qdfLocals = CurrentDb().CreateQueryDef("", strSQL)
Set rstLocals = qdfLocals.OpenRecordset(dbOpenForwardOnly)

'Figure out which State this is
strLocal = rstLocals![OfficeCode]

Set rsState = CurrentDb().OpenRecordset("tFD_Offices", dbOpenDynaset)

With rsState
.FindFirst "[OfficeCode] = " & "'" & strLocal & "'"
strState = ![StateCode]
.Close
End With

'Loop though Local Offices and make copies of the database with the
State and Office as part of the name
With rstLocals
While Not .EOF
strDestinationDb = strPath & strState & "_" & strLocal & "_" &
Str(Year(Now())) & ".mdb"
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile strSourceDb, strDestinationDb
If Err.Number = 0 Then 'Copy was successful
'Now open new db, and delete out anything that doesn't
relate to this Office
Set dbLocal = DBEngine.OpenDatabase(strDestinationDb,
True, False)
strDelSQL = "DELETE tSpecies_In_State.* FROM
tSpecies_In_State WHERE (((tSpecies_In_State.StateCode)<>'" & strState
& "'));"
dbLocal.Execute strDelSQL, dbFailOnError
strDelSQL = "DELETE tSpecies_In_FDOffice.* FROM
tSpecies_In_FDOffice WHERE (((tSpecies_In_FDOffice.OfficeCode)<>'" &
strLocal & "'));"
dbLocal.Execute strDelSQL, dbFailOnError
strDelSQL = "DELETE tGlobal.* FROM tGlobal WHERE
(((tGlobal.OfficeCode)<>'" & strLocal & "'));"
dbLocal.Execute strDelSQL, dbFailOnError
strDelSQL = "DELETE tFD_Offices.* FROM tFD_Offices WHERE
(((tFD_Offices.OfficeCode)<>'" & strLocal & "'));"
dbLocal.Execute strDelSQL, dbFailOnError
'Change any Record Status flags from "New" or "Updated" to
"Accepted"
strSQL = "UPDATE tPopCodeSpecies SET
tPopCodeSpecies.Sp_Record_Status = 'Accepted " & Str(Now()) & "'" & _
" WHERE (((tPopCodeSpecies.Sp_Record_Status) Like
'New*')) OR (((tPopCodeSpecies.Sp_Record_Status) Like 'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
strSQL = "UPDATE tSpecies_In_State SET
tSpecies_In_State.St_Record_Status = 'Accepted " & Str(Now()) & "'" &
_
" WHERE (((tSpecies_In_State.St_Record_Status) Like
'New*')) OR (((tSpecies_In_State.St_Record_Status) Like 'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
strSQL = "UPDATE tSpecies_In_FDOffice SET
tSpecies_In_FDOffice.Record_Status = 'Accepted " & Str(Now()) & "'" &
_
" WHERE (((tSpecies_In_FDOffice.Record_Status) Like
'New*')) OR (((tSpecies_In_FDOffice.Record_Status) Like 'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
strSQL = "UPDATE tSpecies_FDOffice_Year SET
tSpecies_FDOffice_Year.FY_RecordStatus = 'Accepted " & Str(Now()) &
"'" & _
" WHERE (((tSpecies_FDOffice_Year.FY_RecordStatus)
Like 'New*')) OR (((tSpecies_FDOffice_Year.FY_RecordStatus) Like
'Updated*'));"
dbLocal.Execute strSQL, dbFailOnError
dbLocal.Close
'Compact the new database
DBEngine.CompactDatabase strDestinationDb, strPath &
"tempDb"
fs.DeleteFile strDestinationDb
fs.CopyFile strPath & "tempDb.mdb", strDestinationDb
fs.DeleteFile strPath & "tempDb.mdb"
End If
.MoveNext
If Not .EOF Then
strLocal = ![OfficeCode]
End If
Wend
.Close
End With
'****************************************************
'Get the Export Sub Databases button back on fExport_Database in the
current database
Forms!fExport_Database!bExportSubs.Visible = True
Forms!fExport_Database!lblExportSubs.Visible = True
Forms!fExport_Database.Repaint
DoCmd.Save acForm, "fExport_Database"
'****************************************************
bExport_LocalExit:
DoCmd.Hourglass False
DoCmd.Close
Exit Sub

bExport_LocalErr:
MsgBox Err.Number & ", " & Err.Description
Resume bExport_LocalExit

End Sub
Click to expand...
 
R

rhese

Hello Jeff,

Re-synchronization will only happen once a year. First, the Local-
level databases are distributed to each local office, where that
year's data gets entered. Each office then sends their database to
the State-level office. There also is code that imports the changed
records from each Local-level database to the single State-level
database. The State Office then reviews the data and makes any
necessary corrections. The National Office then gets all of the
reviewed State-level databases, the records of which they can then
import into the single National-level database. Then when the next
fiscal year comes around, the National exports out the State
databases, passes those on to the State Offices, who then export out
the Local-level databases and passes those on to the Local Offices.

Believe me, I realize this is not the most efficient way do to this,
and a single networked database or possibly partial replications would
be better, but this is what the client wants to do and what I have to
work with. So, the screwy process aside, any clues as to the specific
code issue?

Thanks for your time.

--Rhese
 
J

Jeff Boyce

OK, sounds like you've covered the synch issues... (I had to ask, it's what
I do...<g>)

One approach, off the top of my head, would be to hide the button for
everyone, but offer a way to enable it. The trick to enabling it would be
something you'd only share with the folks who had 'need to know'. One way
would be to create a pseudo-login popup and collect a UserID/Password and
compare them against a valid combination in your code.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP


Hello Jeff,

Re-synchronization will only happen once a year. First, the Local-
level databases are distributed to each local office, where that
year's data gets entered. Each office then sends their database to
the State-level office. There also is code that imports the changed
records from each Local-level database to the single State-level
database. The State Office then reviews the data and makes any
necessary corrections. The National Office then gets all of the
reviewed State-level databases, the records of which they can then
import into the single National-level database. Then when the next
fiscal year comes around, the National exports out the State
databases, passes those on to the State Offices, who then export out
the Local-level databases and passes those on to the Local Offices.

Believe me, I realize this is not the most efficient way do to this,
and a single networked database or possibly partial replications would
be better, but this is what the client wants to do and what I have to
work with. So, the screwy process aside, any clues as to the specific
code issue?

Thanks for your time.

--Rhese
 
R

rhese

Hmm, well, that's a concept. There is already a user tracking log in
screen. I'll run it by the bigwigs and see what they think. Still
wish I knew why my code doesn't do what I expect it to, though.

Thanks again Jeff.

--Rhese
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

RecordSet Type mismatch using Access 2007 VB script in Access 2002 2
Problem with Search Button and SQL Statement 4
coding a report from a form. 2
How to display the value of a variable on a form or report 1
Copying an Object within a database 1
Form locking up 1
Duplicating records in a form and subform (Allen Browne) 2
Put a form from another database as a subform 3

Top