Create dynamic openarg (2k)

[armitageshanks]

Hi,

I'm having a slight problem getting my head arounf the following...

I have a form called FrmStartUp, with a invisible subform called FrmPasswordSub.

FrmStartUp has three combo boxes on it: CboRole, CboName, & CboArea.

CboRole has its content taken from TblRole, which contains RoleID (PK) and RoleName (Text)
CboName has its contents taken from TblName, which contains NameID(PK) and Surname (Text) & Forename (Text)
CboArea has its contents taken from TblArea, containing AreaID (PK) and AreaName (Text)

FrmPasswordSub has a text box, TxtPassword and a combobox, CboArea. TxtPassword is used for entering a password which gets checked in code. CboArea has a list of areas (Geographical), this data is taken from TblArea which contains AreaID (PK) AreaName (Text).

When the user goes into FrmStartUp they are shown CboRole only & some Command buttons, CmdEnter & CmdExit. They then select a role from the list , RoleID's of 1,2 & 3, text visible only.
If RoleID = 1, FrmPasswordSub is displayed. The user then enters the password in TxtPassword, and selects an area from CboArea. They then click on CmdOpenMain (In frmPasswordSub) which opens the main form.
If RoleID=2, CboName is displayed where the user selects a name. They then click on CmdEnter and FrmMain is opened filtered on NameID in TblMain.
If RoleID=3, Same as role 1, different text,

I have got somewhat confused and I'm not too sure the best way to filter records in FrmMain based on the above criteria. Basically RoleID 2 should have limited access and be able to search by name only. The other roles can search by area.

I will need to filter reports on this as well as other comboboxes once in the main database.

[HansV]

Your description is rather confusing. First, you mention that CboName and CboArea are on FrmStartUp, but cboArea also appears to be on FrmPasswordSub.

[armitageshanks]

I must apologise, having re-read my description, CboArea is only on FrmPasswordSub.

[HansV]

Create a query (or edit an existing one) based on the table(s) you need.
Add the fields you need.
Add a column [Forms]![FrmStartUp]![CboRole] and clear its Show check box.

In the first Criteria line, enter the following criteria:
- For AreaID: [Forms]![FrmStartUp]![FrmPasswordSub]![CboArea]
- For [Forms]![FrmStartUp]![CboRole]: 1 Or 3

In the second Criteria line, enter the following criteria:
- For NameID: [Forms]![FrmStartUp]![CboName]
- For [Forms]![FrmStartUp]![CboRole]: 2

Use this query as basis for forms and reports.

[armitageshanks]

Sorry, I don't quite understand the layout of the query.
Presumably:
[Forms]![FrmStartUp]![CboRole] is the field, with the remaining criteria's below, or am I missing something...

[HansV]

> [Forms]![FrmStartUp]![CboRole] is the field

Yes.

[armitageshanks]

Not too sure whether I have got the premise of this correct as FrmMain has no records in it when I enter it.
FrmMain is currently based on QryMain, with the conditions as previously set out (although I'm still not confident that I have done these correctly)

[HansV]

First, some general comments:
- Turn off Track Name AutoCorrect Information in the General tab of Tools | Options...
- Remove the duplicate indexes from the tables. They take up space needlessly.
- Set the Subdatasheet name property of all tables to [None].
- Indent your VBA code consistently.
You should do this in all your databases.

[HansV]

I don't understand where the name and area come into this. Which fields do they correspond to?

[armitageshanks]

In my haste I attached the incorrect version, duly amended.

The idea is that the user selects their role from CboRole on FrmStartUP.
This then displays either FrmPasswordSub or CboName.

If the user is a lawyer then they select their name from CboName and they can only view their records in FrmMain.

If the user is a CPO or Support then they have access to the whole database, filtered on Area as selected in FrmPassword sub.

Hope this helps.

Nigel

[HansV]

So apparently the name corresponds to StaffID. But I still don't know which field represents the area.

[armitageshanks]

TblMain should have a field called AreaID in, which corresponds to the values in TblArea.

Only Support or CPO roles will be able to add records, Lawyers will only be able to amend data. Hence any new record added will need to take its have the same AreaID value as selected in CboArea in FrmPasswordSub.

[HansV]

No, TblMain does *not* have a field called AreaID. That's why I asked!

See screenshot from the Relationships window.

[armitageshanks]

In a speculative way, what about if it did??? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

[HansV]

You should now be able to use my original suggestion.

QryMain should be based on TblMain only, there is no need to include TblDirections in this query. TblDirections is the record source of the subform, not of the main form.
The subform should be linked to the main form on CaseID, not on DirectionID.
Your code for the On Click event of CmdOpenAreaForm made no sense.

See the attached database and please take a moment to see how I streamlined the code.