Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: user names

  1. #1
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316

    Unanswered: user names

    I have a form (frmDataEntry) with a combo box on it called cboUserName and I want the current users name to automatically appear in this box when a new record is created. I'm using a function called fOSUserName() which retreives the LAN ID of the current user using the database. I also have a Table called tblUsers which has 2 fields - 1. User Name, and 2. LAN ID. All the user records are entered into this Table - their names and their corresponding LAN ID.

    I want to use fOSUserName() in the Query I am using as the Row Source to cboUserName to automatically update cboUserName when a new record is created on frmDataEntry, however, I only want the User Name to be displayed in cboUserName, not the LAN ID.

    I've tried a few things, but to no avail. Any help greatly appreciated.

  2. #2
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Perhaps this is a partial answer - when the form opens, figure out who the current user is, and display the user name value in the combo box.
    sub on_open()


    dim db as database
    dim rs as recordset

    set db = currentdb()
    set rs = db.openrecordset("Select * from tblUsers " & _
            &n bsp;  "Where [LAN ID] = ' " & fOSUserName() & " ' "; )

    if rs.recordcount <> 0 then
    &nbsp;&nbsp;&nbsp;&nbsp; me.cboUserName = rs![User Name]
    end if

    set rs = nothing
    set db = nothing

    end sub
    Last edited by jpshay; 07-16-02 at 20:50.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  3. #3
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    Thanks for the reply, I can see what your getting at, but I couldn't get it to work. Also, although I want the default value of cboUserName to be the current users name (fOSUserName()) I want to allow the user(s) to select any name, should they want to.

    Is there a way to get the SQL into the 'Default Value' field for cboUserName? Or must it be done in VB?

  4. #4
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    I don't know about having the sql for the default name.
    The VB should just set the current value to user ID, but the user still could select any name, because the rowsource hs not been altered.

    Call that function I included GetUserName(). Then on the 'onCurrent' property, or the 'onOpen' property of the form, call the function. The property would look like this...

    =GetUserName()

    The function behind the form would look like this

    Function GetUserName()

    dim db as database
    dim rs as recordset

    set db = currentdb()
    set rs = db.openrecordset("Select * from tblUsers " & _
    "Where [LAN ID] = ' " & fOSUserName() & " ' "; )

    if rs.recordcount <> 0 then
    me.cboUserName = rs![User Name]
    end if

    set rs = nothing
    set db = nothing
    end Function

    hope that helps.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  5. #5
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    When I try compiling that function I get: 'Compile error: User-defined type not defined', and the following line is highlighted:

    Dim db As database

    Then I get another error: 'Compile error: syntax error' and the following line is highlighted:

    set rs = db.openrecordset("Select * from tblUsers " & _
    "Where [LAN ID] = ' " & fOSUserName() & " ' "; )

    I just copied and pasted your code from here into my code window, so I haven't mistyped anything.

  6. #6
    Join Date
    Jul 2002
    Posts
    15
    Set your references to Microsoft DAO 3.6 Object Library. Do this by going to the VB code for your form and click Tools, Refrences. Go to the Microsoft DAO 3.6 Object Library reference and check it. Click OK. That should fix it.

  7. #7
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Thanks Jill,
    The other problem - the syntax error - can be corrected by placing the semi colon inside the double quote at the end.
    Not
    set rs = db.openrecordset("Select * from tblUsers " & _
    "Where [LAN ID] = ' " & fOSUserName() & " ' "; )

    but

    set rs = db.openrecordset("Select * from tblUsers " & _
    "Where [LAN ID] = ' " & fOSUserName() & " ';")
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  8. #8
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    OK, I've set the object library, and corrected the syntax error, but now I get the following error when I attempt to compile:

    Compile error: Invalid use of Me keyword

    And the following line in highlighted:

    Me.Raised_By = rs![User Name]

  9. #9
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    Now for some debugging...

    Using a message box to display a value is a helpful way to see how far the code is working. Try this
    msgbox "Users name is " & rs![User Name]
    instead of
    Me.Raised_By = rs![User Name]
    This will tell you whether you are getting the user name correctly.
    If so, go back to the original line of code, but rename your combo box on the form, from Raised_By to cboRaised_By.
    Then you can refer to it in the code as Me.cboRaised_By and not confuse the control with the value of the field.
    Last edited by jpshay; 07-20-02 at 11:07.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  10. #10
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I've changed the Raised_By reference to cboRaisedBy, and it now compiles correctly. I have also change the On Current property to =GetUserName(). However, when I load the form, a new error message appears:

    The expression On Current you entered as the event property setting produced the following error: Type mismatch.

    * The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
    * There may have been an error evaluating the function, event, or macro.

  11. #11
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    One correction. In the initial code I suggested, I placed a blank between the single quote and double quote so you could distinguish between them. Delete the blanks between the quotation marks.
    ("Select * from tblUsers " & _
    "Where [LAN ID] = ' " & fOSUserName() & " ' "; )

    should be
    ("Select * from tblUsers " & _
    "Where [LAN ID] = '" & fOSUserName() & "';")


    Did you try replacing the line of code with the msgbox command?
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  12. #12
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I deleted the blanks, but the same error message is shown. I also tried your MsgBox suggestion, but that had no effect - the original error was just shown.

  13. #13
    Join Date
    Jun 2002
    Location
    Mpls/St.Paul area
    Posts
    303
    It is difficult to debug through a chat media like this, isn't it?

    Do you know how to operate the debugging features?

    Go to design mode of the form.
    Go to the code behind it.
    Find the function and place a break point on the first line of executable code, set db = currentdb()
    The way to set a break point is to click in the gray verticle column to the left of the code. This will color the line dark maroon.
    Then view the form.
    When the 'on current' event occurs, it will call the function. When control passes to the function, each line will be executed in turn. Watch the progress and see exactly where the code stops.

    Tools to use in debug.
    1) Use F8 to step from one line to the next
    2) Use your mouse to hover over variables and the yellow tool tip text will display the current values.
    3) Use the stop button to break out.
    4) Use message box commands do see how far you are getting.
    Stop the code and place a line like msgbox fOSUserName() to see if you are getting the right value.

    Let me know if this helps.
    John
    This is the day the Lord has made, I will rejoice and be glad in it.

  14. #14
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    OK, I debugged it, and it stops at:

    ("Select * from tblUsers " & _
    "Where [LAN ID] = '" & fOSUserName() & "';")

    But, I can confirm that the function fOSUserName() is working, as I got the text box to display the current users LAN ID. Also, the fOSUserName variables were showing the correct LAN ID when I hovered the mouse over it in debug mode.

  15. #15
    Join Date
    Jul 2002
    Location
    Island of Dots
    Posts
    316
    I'm wondering if Access just doesn't like my putting the =GetUserName() function in the On Current event. Is there anyway I could call the function in code instead, e.g. have the On Current event point to code that calls the function?

Posting Permissions

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