Modify control properties in forms in A.MDB w/ data in B.mdb

G

Guest

I have an MS Access application that is in use by approximately 100 clients -
all similar but not exactly the same.
The differences are slight, I'd like to be able to maintain a "master" front
end that contains the super-set of the forms and controls, and then based
upon the client's profile disable those controls that are inapplicable to the
specific client's needs.
My estimate is that about 3 dozen adjustments need to be made per client.

To do this I'd like to maintain the differences in a separate database on my
development machine, and then be able to run a proc that applies the tweaks
as needed. Here is the code behind the "apply" button.

Option Compare Database
Option Explicit

Private Sub btn_Apply_Click()

Dim the_FormsDB As Database ' a "front end" .mdb in different file than
here
' it is associated with a specific client
Dim the_Form As Form
Dim str_CurrentFormName As String
Dim the_Control As Control
Dim SQL As String
Dim CRLF As String
Dim RS As DAO.Recordset
Dim fld_ClientName As DAO.Field
Dim fld_FormName As DAO.Field
Dim fld_ControlName As DAO.Field
Dim fld_Visible As DAO.Field
Dim fld_Enabled As DAO.Field
Dim fld_Locked As DAO.Field

CRLF = Chr$(13) & Chr$(10)
str_CurrentFormName = ""

SQL = "SELECT [ClientName], " & CRLF & _
" [FormName], " & CRLF & _
" [ControlName], " & CRLF & _
" [Visible], " & CRLF & _
" [Enabled], " & CRLF & _
" [Locked] " & CRLF & _
"FROM [MasterSpecs] " & CRLF & _
"WHERE [ClientName] = """ & Me.cbo_SelectedClient.Column(0) & """ "
& CRLF & _
"ORDER BY [FormName], [ControlName]"

Set RS = CurrentDb.OpenRecordset(SQL)

' Recordset looks like this...
' ClientName FormName ControlName Visible Enabled Locked
' Client A TestForm TestDate No No No
' Client A TestForm TestText Yes No No
' Client B TestForm TestDate Yes Yes No
' Client B TestForm TestText Yes Yes Yes

If Not RS.EOF Then
RS.MoveFirst
Set fld_ClientName = RS("ClientName")
Set fld_FormName = RS("FormName")
Set fld_ControlName = RS("ControlName")
Set fld_Visible = RS("Visible")
Set fld_Enabled = RS("Enabled")
Set fld_Locked = RS("Locked")
End If

Set the_FormsDB = OpenDatabase(Me.cbo_SelectedClient.Column(1)) '
contains full path and filename

While Not RS.EOF
If fld_FormName <> str_CurrentFormName Then
If Len(str_CurrentFormName) > 0 Then
' Is this next line necessary?
DoCmd.Close acForm, str_CurrentFormName, acSaveYes
the_Form.Close
End If
str_CurrentFormName = fld_FormName

' What I'd like to do is something like this....
' But how do I "reach into" the other database?
DoCmd.OpenForm str_CurrentFormName, acDesign, , , , acHidden
Set the_Form = Forms(str_CurrentFormName).Form
End If
Set the_Control = fld_ControlName
the_Control.Visible = fld_Visible
the_Control.Enabled = fld_Enabled
the_Control.Locked = fld_Locked
RS.MoveNext
Wend
RS.Close
Set RS = Nothing
the_FormsDB.Close
Set the_FormsDB = Nothing
End Sub


Alternatively I guess I could put the above code in each of the distribution
databases and pull the settings from the master table via linked tbles, but I
would much prefer being able to push the changes from one master form with
list of clients.

Thanks for looking.
 
D

Douglas J. Steele

Instantiate an instance of the Access Application and use the
OpenCurrentDatabase method to load the appropriate database. Use the DoCmd
method of the object you just created:

Dim appAccess As Access.Application

Set appAccess = New Access.Application
With appAccess
.OpenCurrentDatabase "C:\Folder\File.MDB"
.DoCmd.OpenForm "MyForm"

' do your stuff...

.DocCmd.Close acForm, "MyForm, acSaveYes
.CloseCurrentDatabase
End With
Set appAccess = Nothing

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


NKTower said:
I have an MS Access application that is in use by approximately 100
clients -
all similar but not exactly the same.
The differences are slight, I'd like to be able to maintain a "master"
front
end that contains the super-set of the forms and controls, and then based
upon the client's profile disable those controls that are inapplicable to
the
specific client's needs.
My estimate is that about 3 dozen adjustments need to be made per client.

To do this I'd like to maintain the differences in a separate database on
my
development machine, and then be able to run a proc that applies the
tweaks
as needed. Here is the code behind the "apply" button.

Option Compare Database
Option Explicit

Private Sub btn_Apply_Click()

Dim the_FormsDB As Database ' a "front end" .mdb in different file than
here
' it is associated with a specific client
Dim the_Form As Form
Dim str_CurrentFormName As String
Dim the_Control As Control
Dim SQL As String
Dim CRLF As String
Dim RS As DAO.Recordset
Dim fld_ClientName As DAO.Field
Dim fld_FormName As DAO.Field
Dim fld_ControlName As DAO.Field
Dim fld_Visible As DAO.Field
Dim fld_Enabled As DAO.Field
Dim fld_Locked As DAO.Field

CRLF = Chr$(13) & Chr$(10)
str_CurrentFormName = ""

SQL = "SELECT [ClientName], " & CRLF & _
" [FormName], " & CRLF & _
" [ControlName], " & CRLF & _
" [Visible], " & CRLF & _
" [Enabled], " & CRLF & _
" [Locked] " & CRLF & _
"FROM [MasterSpecs] " & CRLF & _
"WHERE [ClientName] = """ & Me.cbo_SelectedClient.Column(0) & """
"
& CRLF & _
"ORDER BY [FormName], [ControlName]"

Set RS = CurrentDb.OpenRecordset(SQL)

' Recordset looks like this...
' ClientName FormName ControlName Visible Enabled Locked
' Client A TestForm TestDate No No No
' Client A TestForm TestText Yes No No
' Client B TestForm TestDate Yes Yes No
' Client B TestForm TestText Yes Yes Yes

If Not RS.EOF Then
RS.MoveFirst
Set fld_ClientName = RS("ClientName")
Set fld_FormName = RS("FormName")
Set fld_ControlName = RS("ControlName")
Set fld_Visible = RS("Visible")
Set fld_Enabled = RS("Enabled")
Set fld_Locked = RS("Locked")
End If

Set the_FormsDB = OpenDatabase(Me.cbo_SelectedClient.Column(1)) '
contains full path and filename

While Not RS.EOF
If fld_FormName <> str_CurrentFormName Then
If Len(str_CurrentFormName) > 0 Then
' Is this next line necessary?
DoCmd.Close acForm, str_CurrentFormName, acSaveYes
the_Form.Close
End If
str_CurrentFormName = fld_FormName

' What I'd like to do is something like this....
' But how do I "reach into" the other database?
DoCmd.OpenForm str_CurrentFormName, acDesign, , , , acHidden
Set the_Form = Forms(str_CurrentFormName).Form
End If
Set the_Control = fld_ControlName
the_Control.Visible = fld_Visible
the_Control.Enabled = fld_Enabled
the_Control.Locked = fld_Locked
RS.MoveNext
Wend
RS.Close
Set RS = Nothing
the_FormsDB.Close
Set the_FormsDB = Nothing
End Sub


Alternatively I guess I could put the above code in each of the
distribution
databases and pull the settings from the master table via linked tbles,
but I
would much prefer being able to push the changes from one master form with
list of clients.

Thanks for looking.
Click to expand...
 
G

Guest

The very best way (IMHO) would be to acquire a copy of Visual Source Safe.
It will allow you to maintain different versions of the same core application
with client specific mods. Using VSS, if you make a change to a core
object(used by all), it will apply to all versions. You can also make
changes to a client specific version without affecting the other versions.
 
Top