Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Posts
    100

    Unanswered: store creator user name for each record

    Hey,

    given the following:

    - a textbox named "cre_user_id" which shall display the name of the person who has created the record. The name of the person is stored in the person table.
    - the attribute cre_user_id is of datatype integer and has required property set to true

    so far the cre_user_id textbox displays the person_id of the person who has created the record. But what I want it to display is the name of the person.
    The information is stored in the person table having person_id as datatype autonumber, firstname as text and lastname as text. Every table has the cre_user_id attribute and textbox, and everywhere I just know the person_id, however the full name is stored in the person table. How can I accomplish that based on the person_id for each record the full name of the person is displayed?
    Any help will be greatly appreciated. Thanks in advance !
    regards Proximus

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi Proximus,

    I was going to try to explain it, however I created a small sample that I think does what you are asking. If not then come back. NOTE it is always best to make a copy of your database, remove real data and replace with dummy data, Zip and Post here so you can be better helped. Just food for thought.

    have a nice one,
    Bud
    Attached Files Attached Files

  3. #3
    Join Date
    Apr 2004
    Posts
    100
    hey thanks for the sample. however it was not what i am looking for.

    the textbox cre_user_id has the control source = cre_user_id which is an attribute of datatype integer. So for now when a new record is being created, the user id (which is the PK of the person table) is stored in the cre_user_id.
    So the cre_user_id displays the PK of the person table.
    However what I want it to display is the persons name. So is there a possibility to run a query that based on the user id retrievs the persons name and displays it in the textbox?
    I hope its clear now... Otherwise please ask. Its really urgent !!

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    That would be basic "SQL Joins 101"

    SELECT t1.*, t2.username
    FROM t1 INNER JOIN t2 ON t1.cre_user_id=t2.user_id

    Alternately you could use a DLookup if you don't want to deal with the SQL.

    like:

    DLookup("username", "Users", "user_id = " & me.cre_user_id)
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Apr 2004
    Posts
    100
    yes the problem was that one can't bind a query to a textbox, so I choose a listbox.

    However the listbox highlights the retrieved value? Does anyone know how I can stop the highlighting?

    Alternatively I tried a combobox. But the Problem here is the small button on the right hand side of a checkbox. Is there any way to hide that button?

    What is it with the DLookup? I am not familiar with that. How can I use it?

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Proximus
    yes the problem was that one can't bind a query to a textbox, so I choose a listbox.
    Uhhh... what are you talking about? Since when can you not have a bound text box?

    Quote Originally Posted by Proximus
    What is it with the DLookup? I am not familiar with that. How can I use it?
    DLookup is a VBA function. You can read about it in the help text under "DLookup function"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I do somewhat of the same thing ... Except I use labels so that noone is tempted to screw with the name... Ex:

    LastModLbl.Caption = "Last Modified: " & Format(TmpDate & "", "mm/dd/yy") & " By: " & RetrieveUserName(MyConnect, TmpID)
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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