Results 1 to 11 of 11
  1. #1
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52

    Set value of field based on another field

    I have a need where an end user is filling out a form with a work id (unique person ID) and a free format Text box. I need to take the work id and use that to set the value of another field with data from another table. I am having a hard time trying to figure out the right phrase to google lol. like this:

    Form -
    Work Id - U1W1
    Text Box - Blah Blah Blah
    *Hidden Field* Emp ID - ? (set by joining on the work id above)

    Table To Pull Data From -
    Work ID - U1W1
    Emp ID - 123456

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Make the work ID a combobox with a hidden column of the Emp ID

  3. #3
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    I should have been a little more clear, the table the form is submitting to is a different table from where I need to look up the data

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If the work ID is a combobox then it has its own SQL query string, different and separate from the form's.

    Create a query that gives you workd ID's and Emp ID's. Then either use that query or copy the SQL for the Row Source of the Work ID combobox.

  5. #5
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    Also can that be submitted to separate fields on the destination table

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    cdscivic,

    Further to aytee's comments, if you have a list of EmpID or WorkId or any other identifier, offer the user a combobox to select a value. Don't opt for a free form text field where the user may enter any garbage they please, or where you have to validate/verify against known values anyway.

    Good luck.

  7. #7
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    Yea on the way home I decided I would do that. Good call. I can't wrap my brain around how to populate the second field based on the value the end user chooses.....

  8. #8
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    Update on what I ended up doing. I used a combo box that referenced my linked table with a query. I included the additional values I needed in that query as well. Then I used the change event for the combo box to populate the other fields!

    Shout out to Aytee and Orange for getting me googling in the right direction.


    After creating the combo box, here is the code that I Used to fill the additional fields. cboChoice is obvs the combo box. It is 0 based, so...Column1 is actually the 2nd column.

    Code:
    Me.txtBox1.Value = Me.cboChoice.Column(1)
    Me.txtBox2.Value = Me.cboChoice.Column(2)
    Me.txtBox3.Value = Me.cboChoice.Column(3)

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Well done!

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Ditto! Well done and thanks for posting your approach.

  11. #11
    cdscivic is offline Resident Troll
    Windows XP Access 2010 32bit
    Join Date
    Aug 2016
    Posts
    52
    Anytime! Thanks again (Pet peeve where threads have no end result lol)

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

Similar Threads

  1. Replies: 1
    Last Post: 11-14-2016, 10:36 AM
  2. Replies: 14
    Last Post: 06-22-2016, 04:08 PM
  3. Replies: 0
    Last Post: 06-17-2016, 08:56 AM
  4. Replies: 3
    Last Post: 01-09-2015, 05:48 PM
  5. Replies: 3
    Last Post: 10-03-2011, 02:33 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
  •  
Other Forums: Microsoft Office Forums