Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create dynamic openarg (2k)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

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

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create dynamic openarg (2k)

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

    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.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create dynamic openarg (2k)

    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...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

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

    Yes.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create dynamic openarg (2k)

    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)

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

    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.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

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

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create dynamic openarg (2k)

    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

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

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

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create dynamic openarg (2k)

    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.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

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

    See screenshot from the Relationships window.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create dynamic openarg (2k)

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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Create dynamic openarg (2k)

    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.

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •