Limit Lookup Values based on field on another form

[EllieFant]

I am creating a database to help SMART (our employee based Safety Committee) track their employee suggestions/concerns. In this database I have a field (SMARTPosition) that has it’s values looked up from a table (luTeamPosition). There are 4 positions in this table (Member, Chairperson, Recorder, Database Administrator). Each team position is allowed to perform certain duties.

One of the duties allowed by the Chairperson, Recorder and Database Administrator is to change the SMARTPositions of all members. Since the Database Administrator has access to the database window (via a button on a form that shows only for the database administrator) I am concern that the Chairperson and/or Recorder will give the Database Administrator position to someone who has limited knowledge of Access and may mess up the database unknowingly.

When the user signs in, a form (FilterForm) is loaded in the background (made invisible when the main menu form loads using the following code: Forms!FilterForm.Visible = False). The FilterForm contains the users SMARTName and the users SMARTPosition. I use this form to limit what fields on my other forms the user can edit and/or see, what buttons that have access to on the menu forms and etc.

I am not sure how to limit what is shown in the SMARTPositions field based on what SMARTPosition the user has. Basically I only want the database administrator to have access to give another person the SMARTPosition of a Database Administrator. I will have the information on how to get into the database in a sealed envelope that will be available should I win the lottery and leave the company without assigning a new database administrator so that I make sure that WHEN I do leave (cause of lottery winnings haha) the database doesn’t become useless cause I am not there to administer it.

Is this possible? If so how can I go about doing this?

Thanks so much in advance for your help,

Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net

 

[Lightning]

Hi Ellie

From your Post I gather that what you want is to Set various fields visible or not when each form is opened from the Menu form, based on WHO has opened the database. The who is established on your form FilterForm.

Probably the simplest way to do this is when the Main Menu opens, set a variable to the value of the SmartPositions field. This variable will then hold its value as long as the Main Menu form is open.

Then use a Select Case statement when each form is opened to decide which fields should be visible and which should be not visible.

So, if the Database Administrator opens the database, the flow would be something like this:

1. DA opens the database
2. FilterForms form opens - sets value of SmartPositions field to Database Administrator
3. Main Menu opens - variable sets to Smartpositions value
4. DA opens another form from Main Menu - Select Case routine sets various fields on this form Visible/Not Visible based on value of variable.
5. DA closes this form and opens another from Main Menu - Select Case routine sets values for this form's fields based on value of variable.

etc, etc.

The only reason I suggest using a variable on the Main Menu is because it is easier/shorter to type a variable name than it is to type Forms!FilterForm.SmartPosition each time. By placing the variable in the Main Menu's module, you don't need to refer to the other form every time you need to get the Smartposition value.

HTH
Lightning

 

[EllieFant]

Thanks for the input.

What I ended up doing is opening the form where the SMART position is set for each member, and editing the query that the combo box gets the information through. I just said don't show Database Administrator and made the limit to list set to yes. If a new Administrator needs to be set up I will have to go to the table itself to do it. Long way around, but it will work.

I do appreciate your assistance and will re-read what you have written from work and see if I can make my database run smoother thanks to your ideas.


Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

lena.wood@starband.net