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

[JoeyB_99]

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!

 

[Isskint]

Perhaps the simplest way is to have a hidden textbox on the first form that will be opened after the user selects the division. In your CommandButton OnClick event code, after the DoCmD.OpenForm line include;

Forms!FormName.HiddenDivTextBox = Division

You could duplicate this on any other forms that are opened subsequently.

With that in place, change header labels from Label to TextBox (set AutoTab = No, TabStop = No, SpecialEffect = Flat). Then set the control source as =Me.HiddenDivTextBox & " Menu" to display Div1 Menu. You could set it any way you want. For the subform do the same except, of course, use =Forms!MainFormname.HiddenDivTextBox & " Menu"

 

[NeutronFlux]

Personally, I would create a query for each division. From then on, the data source would be either one of those two queries instead of the actual table. Everything else should be based on data from either of the two queries. I find this a lot easier to deal with than coding in your own filter each time. If you need to find out at any point which division you're on, such as for labels, you can just refer to the data.

If you have a switchboard that will remain open for the duration of the session, Isskint idea of a hidden textbox holding the DivID value works well. Otherwise, you can also consider a global variable. You would declare that in a non-class module such as

Public gVariableName As String

You would probably want to initialize it on the first form with the two buttons. From here on, you can access it in any VBA module. Unfortunately, this doesn't work terribly well if you try to use the variable without VBA.

 

[nanscombe]

If you wanted to use the Global variable you can place it in a function.

Global gstrMyDivision as String

Public Function myDivision()
  myDivision = gstrMyDivision
End Function

Or if you go the form and textbox route

Public Function myDivision()
  myDivision = nz(Forms("myForm")!txtMyDivision,"myDefaultValue")
End Function

The function myDivision() can then be used as a criteria for queries as well.

 

[NeutronFlux]

If you wanted to use the Global variable you can place it in a function.

Global gstrMyDivision as String

Public Function myDivision()
  myDivision = gstrMyDivision
End Function

Or if you go the form and textbox route

Public Function myDivision()
  myDivision = nz(Forms("myForm")!txtMyDivision,"myDefaultValue")
End Function

The function myDivision() can then be used as a criteria for queries as well.

Ah, so that's how you include a global variable into the properties menu. Thanks!