Page 1 of 4 1234 LastLast
Results 1 to 15 of 58
  1. #1
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43

    Control to get login but store id to table


    I have a form that I want the control to show the id of the user and store it on a table based of their login. Here is what I have for code right now. It doesn't work and maybe someone can help out on why it doesn't.

    Code:
    CurrentDb.Execute
    "UPDATE [EMPLOYEE LIST NEW] SET [ID] = '" & Me.SCAN_OPERATOR
    & " Where[ID] = '"
    thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand. How can you use the ID as WHERE criteria if it's not already in the table? If it's already in the table, why would you need to update it?

    Maybe you should bind the form to the table and bind combobox to the field.

    When you say something "doesn't work", it's helpful to provide info about what does happen - error message, wrong results, nothing?
    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
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43
    That's why I'm asking what's wrong with my code. Something is wrong with it because it doesn't populate the ID in the control named SCAN OPERATOR. Right now the user puts their ID in said box and it saves to the table EMPLOYEE LIST NEW. What I want is their ID to auto-populate when they open the form. In the table mentioned earlier I have all users "UserNames" and their matching "ID". I get no error message or anything it just doesn't work. I'm kind of a newbie at this so any help would be appreciated. Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Are you creating a new record or editing existing?

    UPDATE is to edit an existing record.

    INSERT is to add a record.
    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
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43
    I am editing a record in a table called BATCH HEADER SHEET.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That's not the table referenced in the UPDATE.
    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
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43
    OK? So should I reference that table after the update? What field should be set then? Sorry, so many questions but I'm really new to vb code.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I am totally confused.

    Is the form bound to table - what table?

    Why do you need an UPDATE action?

    If all you want to do is set the value of a textbox on form:

    Me.textbox = something

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  9. #9
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43
    The form is bound to the table "batch header sheet" the control "scan operator" is the field. I want this control to auto populate the userid in this field. I have another table "employee list new" with a field "id". I want the id which is a number field to be auto populated on the form then be saved to table " batch header sheet".

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Going in circles. If you want to provide db for analysis, follow instructions at bottom of my post.

    What do you mean by 'auto populate' when they open the form? How should Access know who the user is?
    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.

  11. #11
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43
    I can not provide DB for analysis due to security reasons set forth by my IT department. What I mean by auto-populate is that Access gets the username from the user's login and on the form's on open event it puts the users corresponding ID into the control based off the table mentioned earlier.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to pass a value from one form to another, several ways to do that.

    1. use OpenArgs argument
    DoCmd.OpenForm "formname", , , , , , Me!ID

    then code in the second form Open event:
    If Me.NewRecord Then
    Me.textbox = Me.OpenArgs
    End If

    2. code in one form references control on other form

    3. global variable
    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.

  13. #13
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43
    I am not trying to pass a value form form to form and apparently I have not done a good enough job explaining what I need to do.

    I have a form called "SCAN INPUT" with a control named "SCAN OPERATOR" that I want to have filled in on the "on open event". Now, I have a table called "EMPLOYEE LIST NEW" that has a fields "LOGIN" and "ID". I also have a table named "BATCH HEADER SHEET" that the form "SCAN INPUT" is it's control source. So, I want code to have Access look at the users login or fOsUserName compare it to the table "EMPLOYEE LIST NEW" and find that login and matching "ID" and put "ID" into form "SCAN INPUT's" control "SCAN OPERATOR" when the form opens. Then have it stored in the table "BATCH HEADER SHEET" so it is one less step that the user has to do.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want Access to look at the users login where? From a login form or you want this pulled from the network? What is OsUserName - the network username?

    The latter can be done with:

    Environ("USERNAME") - yes, type that exactly as shown.

    Why is the table called "EMPLOYEE LIST NEW" - is there an "EMPLOYEE LIST OLD"?
    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.

  15. #15
    nmart1230 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2013
    Posts
    43
    I want access to get the username for the network. Sorry, OsUserName was a typo and I corrected it to fOsUserName. I know how to get the username from "Environ" but then it needs to look at the table for that username and find the corresponding ID for said username. Does it matter what the table is called? That's what it is called when I inherited this database. So, the question remains how would I tell it to get the username from the network then look at table to find ID for username and have it inserted into the form. Here is what I was trying to use but wasn't returning anything. With it I was getting data type mismatch since the ID field is a number field.

    Me.SCAN_OPERATOR = DLookup("[ID]", "EMPLOYEE LIST NEW", fOSUserName() & "'")

Page 1 of 4 1234 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 04-01-2014, 05:06 PM
  2. Replies: 4
    Last Post: 06-30-2012, 02:01 AM
  3. Replies: 1
    Last Post: 04-17-2012, 12:27 PM
  4. How can i store expressions in table?
    By smahdih in forum Access
    Replies: 9
    Last Post: 10-28-2011, 05:32 AM
  5. How to store data many row in one table
    By dododo in forum Access
    Replies: 4
    Last Post: 06-25-2011, 12:42 AM

Posting Permissions

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