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

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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...
 
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.
 
Back
Top