Two divisions but one set of database objects, How? 1

[JOEYB99]

I'm using Access 2007. I have designed a database for one of a two division set up. All of the raw data in the tables is similar yet there is one unique identifier for the two divisions. Let's call it DivID, four characters in length.

To keep the maintenance simple I want to have one set of forms, queries and reports. I do not want to duplicate all of these. To the user they will only see one set of the menus, which will show all of the necessary sub-menus.

The first or main menu will have two buttons, Division A and Division B. The user simply clicks on the appropriate division button, and off they go. I'm thinking that it would be easiest for me if the DivID is stored after they click on their division button, and this value is used for any form or report that they run afterwards. Also, this DivID value would generate custom headers at each menu and submenu to confirm which division they chose.

I'm having trouble following through on how to do this.

Any assistance would be greatly appreciated. Even if someone has a different approach it would be welcome too!

 

[MajP]

In a standard module you can set a project scope "global" variable

public glblDivID as string

Public function getGlblDivID() as string
getGlblDivID = DivID
end function

Then you set the value of this in the login form
Then on any form or report you set properties based off the value of the global divid

Private sub Form_Open()
select case glblDivID
case "ABCD"
with me
.Caption = "Division ABCD Settings"
.someotherProperty =
.someOtherproperty2 = ...
end with
case "EFGH"
with me
.Caption = "Division EFGH Settings"
.someotherProperty =
.someOtherproperty2 = ...
end with
case Else
'Some error checking here because it is not one of your choices
end select
end sub

 

[MajP]

Try that with formatting

Private sub Form_Open()
   select case glblDivID
      case "ABCD"
         with me
           .Caption = "Division ABCD Settings"
           .someotherProperty = 
           .someOtherproperty2 = ...
        end with
     case "EFGH"
        with me
           .Caption = "Division EFGH Settings"
           .someotherProperty = 
           .someOtherproperty2 = ...
       end with
    case Else
       'Some error checking here because it is not one of your choices
    end select
end sub

Oh. The function allows you to use the value of the glblDivID in a query. So for example you have combo boxes but you want to only show those sections within that division

select sectionID, SectionName from tblSections where assignedDivision = getGlblDivID()