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

    Filter issues (2k)

    Hi,
    I have attached a database that I'm having problems with. I'm trying to adapt an existing database into performing a simliar task.
    The purpose of the database is to record dates that papers have to be sent out to parties.
    The database has FrmStartUp. The user selects their role, from Caseworker, Lawyer and Support (This is selected from CboRole which gets its values from TblRole).
    The previous version of the database only had Support and Lawyer. In this previous version, if the user selected support, then FrmPassword.Visible = True, they then enter the password, select the Area (all from FrmPassword) and then they open FrmMain, which if filtered on the area selected. Thus showing records related to that area. The Area is added to each record through and open arg that sets the AreaID to that selected in FrmStartUp (if that makes sense). Support would have full editing rights.
    If the user selected Lawyer, this would then display CboName, the data in this combobox is taken from TblStaff. The user then selects their name (now changed to initials) and then CmdOpenAreaForm is visible, user clicks on it, which opens FrmMain and will filter FrmMain based on the value in CboName which corresponds to the value LawyerID on FrmMain. Basically allowing the Lawyer to view only their cases, and giving them limited editing rights.

    Now Iím trying to add a further role, Caseworker, that will open FrmMain in the same way as the lawyer role, however with full editing rights, and only viewing their cases. So on FrmStartUp the user selects the Caseworker role from CboRole. CboCaseworker is then displayed (CboName is still not visible) as is CmdOpenAreaForm, which is clicked on, opening FrmMain. The selection in CboCaseworker should correlate with CboCaseworker in FrmMain.

    FrmMain is based on QryMain which does some of the filtering based on the selections from FrmStartUp.

    With the original database, HansV assisted me, which may explain why Iím a bit stuck. I also havenít done anything with Access for about a year so Iím slightly rusty. Any help or guidance would be appreciated.
    Attached Files Attached Files

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Filter issues (2k)

    The OpenForm command accepts a "Where" condition, which filters the records displayed to just those that meet the condition.

    This provides a very flexible way to open the same form with different sets of data displayed, without having to have lots of different queries.

    DoCmd.OpenForm "FrmMain", , , strwhere

    If a Caseworker is opening the form you would use
    strwhere = "[CaseworkerID]=" & Me.[CboCaseworker]

    If a Lawyer
    strwhere = "[lawyerid]=" & Me.[CboName]

    You can then remove these references from the query.
    Regards
    John



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

    Re: Filter issues (2k)

    Will this approach allow for various editing rights and locking fields? This is specified in the original coding.

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

    Re: Filter issues (2k)

    You can still refer to CboRole on FrmStartup, as you do in the Form_Load event procedure of FrmMain, to check which role the user has, and to locl or unlock controls based on that role.

Posting Permissions

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