Results 1 to 9 of 9

Is it possible to return only certain values in a ComboBox in a form based on the user logged in

  1. #1
    Join Date
    May 2018
    Posts
    5

    Is it possible to return only certain values in a ComboBox in a form based on the user logged in


    Good day, I have a database with a table (tblEmployees) containing a column for Employees (Employee) and a column for the Business Unit (Bus_Unit) they belong to.

    I also have a table for users who login to the database (tblUsers) and a Form with a login screen (frmLogin) which authenticates the user per username and password. The user is linked to a Business Unit which correlates with the Business Unit list in the Employee Table. I have declared the TempVars of the user (TempVars("EmployeeType") = rs!EmployeeType_ID.Value).

    At the moment I have a Combox in a Form which looks up the the list of Employees from the tblEmployees table. The users who login must all have access to this form but should only see the employees belonging to their Business Unit when using the ComboBox drop-down menu. Is it possible to filter the list of Employees returned in the form's combo box, linked to a specific Business Unit in the tblEmployees table based on the user logged in (belonging to the same Business Unit)?

  2. #2
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Why not limit the query that feeds your combo-box by using a dlookup on your employee table to get the business unit the logged in person belongs to? Something like dlookup("[BusinessUnit]","[tblEmployee]","[EmployeeID]=" & [Tempvars]![EmployeeID]) (or if you keep your frmLogin open simply reference the control that holds the user.

    Cheers,
    Vlad

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    6,675
    i have a combo box on the form of users, it is invisible so users cannot see it.
    the combo is from tUsers, and has 2 columns: userID, BusUnit

    when user opens the db, and the form, the form grabs the user id, and assigns it to the combo, this pulls the BusUnit.
    the queries for all items us this combo to pull data:

    Code:
    sub form_LOAD()
      cboBusUnit = Environ("Username")
          'the combo bound col = BUSUNIT
    end sub
    then all queries use it:
    select * from table where [busUnit]=forms!myForm!cboBusUnit

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,617
    Are these Users also Employees?


    Could establish a TempVars for the business unit.

    I've never used TempVars.

    I set a textbox on a form that never closes (MainMenu) with info from the login.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    Join Date
    May 2018
    Posts
    5
    Vlad, Ranman and June

    Thank you so very much for your replies, it is very much appreciated! I am not very clued up with VBA to be honest but am in the deep end as my company asked me to develop this tool for them. I have uploaded the database for your reference, the Combo Box I am referring to is in the Form frmConstruction_Main, named Employee, so when a user belonging to the Construction Business Unit logs in, the Employee Combo Box in frmConstruction_Main must only list Employees belonging to the Construction Business Unit. I have tried all your suggestions but I really am at a loss.

    Your possible assistance in this matter will really be very much appreciated and also definitely credited

    Many thanks

    Anita
    Attached Files Attached Files

  6. #6
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Hi Anita,

    Have a look now. I have modified the row source of the combo box to add the business unit and limit it to the saved tempvar (and saved it as a query).

    Cheers,
    Vlad
    Attached Files Attached Files

  7. #7
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Anita, have you had a chance to look at the updated sample?

    Cheers,
    Vlad

  8. #8
    Join Date
    May 2018
    Posts
    5
    Hello Vlad

    Thank you so very much, it is working 100%! I really appreciate your assistance in this, and apologies for the late reply, I m based in South Africa so there is a possible time delay. You have been a massive help to me and I have now learned something new as well :-) Again, thank you!!!

  9. #9
    Gicu is offline Expert
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    508
    Hi Anita,

    No worries, I was just curious if I got your intention right. Glad to hear you have it working now!

    Cheers,
    Vlad

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

Similar Threads

  1. Replies: 4
    Last Post: 10-28-2016, 11:20 PM
  2. Replies: 1
    Last Post: 10-27-2016, 09:51 PM
  3. Replies: 1
    Last Post: 01-04-2014, 10:04 PM
  4. Generate form based on combobox values
    By OatmealJones in forum Forms
    Replies: 3
    Last Post: 02-13-2013, 03:08 PM
  5. logged user name on main form
    By foxtet in forum Forms
    Replies: 1
    Last Post: 07-30-2011, 02:00 PM

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