Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    15

    Unanswered: Setting a Default for a Combo Box

    Hello,

    I have a combo box in a form that I want to set a default for based on who the current user that is logged to Access. I have a table with a list of users and their corresponding values on the combo box. I have a query that returns the number of the item on the combo box list I want to display as the default:

    SELECT Locations.L_CB_INDEX
    FROM Locations INNER JOIN UserLocations ON Locations.L_LOC_CODE = UserLocations.UL_LOC_CODE
    WHERE (((UserLocations.UL_USER)=[CurrentUser]));

    I tryed putting the result of this query in the Default Value for the combo box:

    =[Combo115].[ItemData]( [DefaultUser]![L_CB_INDEX] )

    which did not work.

    How can I tell the combo box that based on the currently logged in user I want the default to be the corresponding value in the table Locations?

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you have the user login name in a table, you can utilize the getuser() routine here: http://www.dbforums.com/showpost.php...0&postcount=20 to set the combobox default value to =getuser(). Make sure the user login name field is the bound column for the combobox and the next column is your corresponding value in the table for that User Login Name (ie. Combobox name is: MyLoginField). Name another TEXT Field something to represent the 2nd value of the combobox (ie. UserLocation) and set the sourceobject of that field to =Forms!MyFormName!MyLoginField.column(1) of the combobox. Then set your query criteria to =Forms!MyFormName!UserLocation.

    Or

    SELECT Locations.L_CB_INDEX
    FROM Locations INNER JOIN UserLocations ON Locations.L_LOC_CODE = UserLocations.UL_LOC_CODE
    WHERE (((UserLocations.UL_USER)=getuser()));
    Last edited by pkstormy; 09-23-07 at 01:19.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Aug 2007
    Posts
    15

    Addition Explination

    Thanks for responding to my post.

    I am not quite sure how your post will help my problem.

    Currently the Row Source... for the combobox is set to:

    SELECT Locations.L_LOC_CODE, Locations.L_TEXT FROM Locations;

    which is a table that is a list of location codes and there associated descriptive text. Example:

    CCSD San Diego
    CCCN Shenzhen

    I have another table that is the user id and the location that corrsponds to the user id. Example

    smith CCSD
    xchen CCCN

    The first table is working ok for a list of locations for the user to select from. What I what is the possiable values for the combobox to be listed in the first table and based on what user is logged in to display the location that corresponds to that user in the combobox as a default.

    So your statement set the sourceobject of that field to =Forms!MyFormName!MyLoginField.column(1) of the combobox does not make since to me. By sourceobject do you mean 'Control Source' or 'Row Source' or somthing else in the Properties List for the control. Also I am not sure what you mean by query criteria.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm guessing you have a table which has a field which stores the user's login name (something like ISPAK or ISDev, etc....). Correct? If you don't, you should create a field in the table which holds the user's login name since your request is dependent upon who logs in. The example in the link I supplied shows you how to retrieve the current user's login name (from windows) which you can then match against the table to find the user's location (if your table which holds the user's login name is joined to another table, you can create a query to link the 2 tables and find the corresponding value in the 2nd table...again, where the user's login name field matches the current user (ie. getuser() from the example.). The getuser() routine in the example shows you how to retrieve the window's current login name. If you store the user login name in a table, then finding any value for the record where the "LoginName" field in the table matches the getuser() (current windows user login name) is possible. But you need to store the windows login name in a table somewhere in order to match the getuser() to the login name in the table. If you don't store the login name field in a table, the user must then select (on some form or via criteria in a query) who they are in order for your query to work. I'm guessing from your question that you want to avoid making the user select/enter who they are in order to get your query to work. But take a look at the example and see how it matches windows user's login and compares it to the login Name field in the tabled.

    Let me know if that makes more sense.
    Last edited by pkstormy; 09-24-07 at 21:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Aug 2007
    Posts
    15

    I decide to use VBA...

    Sorry, but I'm not sure where you are going with your reply. My last post described the tables I already have which seems to fit almost exactly what you are describing. The problem I am having is that I am not sure what to put in the proprities dialog to tell access which users location to use as the default. I am interested in understanding your approach however, I decide to writh VBA code for the Open event of the form to set the default in the drop down menu and it seems to be work just fine.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    GetAJob,

    I'm sorry if I'm confusing. I want to give you advice on the best approach from what I'm interpreting you are trying to do.

    Am I correct in that you want to somehow determine which user is using the database and find a value in the users location table based on which user opened the db?
    If so (and you don't want to have the user select/enter their name to find the value in the user location table), then I'd use the getuser() routine found in the example link. What the getuser() routine does is tell you the user login name (i.e. windows login name) that is accessing the db. You may want to check out the example in the link supplied to see what I mean. In the example, there is a text box on the main form which shows the windows login name. For example, if you open the main form in the example, it will show your windows login name. If Joe-Somebody opens the main form in the example from the computer that they are logged on to, it will show the windows login name of Joe-Somebody.

    Now using this approach, if you stored the user's windows login name in your user location table (ie. call the field: LoginID), you would easily be able to find the userLocation field value in that table by matching the LoginID value with the getuser() (Note: getuser() is a just a function in a module and it can be utilized anywhere, even in criteria for a query - it will always return the windows user login name of who is accessing the db - it uses the SystemInfo Class module (required module in the mdb) to find this information.) Simply import the "UserName (getuser)" module and the "SystemInfo" class module into your mdb and you're all set to utilize the getuser() function anywhere in your mdb. Read the notes in the SystemInfo class module as there is a wealth of other information which can be returned using this class module besides just the windows user login name.

    So your user location table (ie. MyuserLocationTableName) would have these fields:
    LoginID
    UserLocation

    and your select statement might look like this...
    Select userLocation from MyuserLocationTableName where LoginID = ' " & getuser() & " ' "

    To use this approach though, you need to find out the windows user login names of each person who is using the db and put their login name in the LoginID field of this table (along with the userLocation.) So you might have values like this...
    LoginID UserLocation
    ispak Office
    dKohn Somewhere
    fBailey Home
    JSmith Office 2

    So I log into my computer (using my login name of ispak) and get into your mdb. The select statement above will return Office since the getuser() returns the value: ispak (for my connection to the mdb) and it matches this against ispak in the MyuserLocationTableName to find: Office.

    I hope I've read you correctly on what you're trying to do and I hope that makes more sense. I'm sorry if I'm doing a poor job explaining it or led you astray but the getuser() routine along with the SystemInfo class module are very powerful and I utilize them in every mdb I've designed. Once you understand it, you can see how you can apply this to basically anything where you want to find a value in a table based on the user's login name (I will often use this approach for security and make buttons visible/invisible on the forms depending on who is accessing the mdb.) Another example is I have a field in my main data table called: EnteredBy (along with a DateEntered field setting default =Date()) and set the default value of the EnteredBy field on the form to =getuser(). This way I know who entered the record and when. This a good approach to use on all main data tables in any mdb!! Something you should consider if you don't already do this.

    As a last note, the getuser() routine is the best approach I've found to get the login name of the person accessing the mdb. There are other methods but this approach is pretty fool-proof.
    Last edited by pkstormy; 09-25-07 at 17:00.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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