Results 1 to 8 of 8

Users accessing the same reports, but able to see information for only their location

  1. #1
    millerprm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6

    Users accessing the same reports, but able to see information for only their location

    I have created a database in which the reports are going to be used by 3 different location i.e. Los Angeles, Wichita and Toronto. There is a field in a table [Location] the designates the office branch. Management has requested all branches be able to view the same reports, but should see ONLY their branch information, i.e. when Wichita views their reports it should only contain data for Wichita.

    Currently I have created a form for each branch containing reports in an option group and text boxes to pick a data range (code courtesy of Allen Browne's post). I know that I could then create 3 different queries and have the criteria default be each location, then base each branches reports on the appropriate query, but to me this doesn't seem like the most efficient solution. A prompt isn't an option, as a user in Toronto could easily enter Wichita or vice versa. Also, I would like the user to have the option of using a date range for each report.



    I am somewhat familiar with VBA and have used it in databases before (customizing examples posted on forums) , but I am not a programmer. Any suggestions on the best way to tackle this issue would be greatly appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,590
    When user opens database, set a global variable or a TempVars or a textbox on a form that never closes with the appropriate Location value. Reference this as parameter in the criteria that filters report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    millerprm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6
    Thanks for the quick reply. Would it be possible for you to go into more detail, as i'm not sure how to implement your suggestion.

    thank you

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,590
    How should the database 'know' the location it is opened by? Is there a user login in process? Do you want to hard-code the location into 3 versions of the same db?

    Is this a split db or all-in-one? Is each location running the db completely independent of the others (not sharing data?)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    millerprm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6
    Hi,
    I hadn't though about how the database would 'know' which location it's opened by, but can certainly see the value now that you have brought it to my attention. it's one database that will be split after completion as there will be a total of about 10 users (with permissions to the db). the main form has 3 command buttons, one for each branch. the user clicks on a command button that requests a password (not super secure, but should do for the user environment, mostly managers and supervisors, who don't know anything about access except how to enter data or press a button).

    the command button opens another form that contains reports in a option box and text boxes for a date range. the report forms (and reports) are identical, the only difference would be the information on the report would be for only their branch.

    ideally I guess the branch would be hard coded so all the user had to do was choose an option button and enter a date, but I'm not married to what I've done so far, when it means implementing something more efficiently.

    thank you!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,590
    Hard coding means maintaining 3 versions of database - a real headache.

    In my db I have a Users table and code determines who user is from their network login. The table has user permission category and I use this to determine what forms and controls a user will access. The 'login' procedure opens MainMenu form which is bound to Users table and filtered to the user ID. This form never closes and I can reference that user record from anywhere at any time.

    An alternative to the form is to set global variables or TempVars variables with the user info during login procedure. Issue with global variables is they will lose value if code execution is interrupted by run-time error. That's why I use the form. I didn't know about TempVars at the time and have yet to use anywhere.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    millerprm is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    6
    then I would say no to hard coding.

    when you are talking a login do you mean like the code below? this was in a db I did not create, but was asked to figure when someone couldn't enter records. the correct login and user name wasn't in the lookup table.

    Public Function LookUpName() As String
    Dim myRS As Recordset
    Set myRS = CurrentDb.OpenRecordset("Select * from dbo_NameLookup where LoginName = '" + Environ("USERNAME") + "'")
    LookUpName = myRS!UserName
    End Function

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,590
    Yes, something like that. I also use the Environ() function.

    I have more code that pulls the USERNAME from network login, looks for user in Users table and if not found, means it's their first time using the database and code opens a "Register as User" form that creates a new record for them in Users table. User just has to provide their initials for input to the record (initials are used elsewhere in db). The code automatically classes them as "staff". User never sees the "Register" form again and future login is automatic because I take for granted if they can login to the network they are authorized user.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Two different users accessing Access database
    By ultimateguy in forum Access
    Replies: 3
    Last Post: 06-25-2014, 03:43 PM
  2. Novice Users and Reports Best Practices
    By james28 in forum Reports
    Replies: 4
    Last Post: 03-02-2014, 03:35 PM
  3. Replies: 2
    Last Post: 04-16-2012, 10:24 AM
  4. Replies: 7
    Last Post: 03-16-2011, 05:09 PM
  5. Multiple users accessing same database
    By 4ACE in forum Access
    Replies: 0
    Last Post: 02-28-2008, 03:10 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums