Results 1 to 6 of 6
  1. #1
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23

    Populate a Field Based Off Another Field

    I have vender ID that is attached to a branch. For instance, branch AMA has a vender # of 58697 and branch FGS has a vender # 34256.



    I want to be able to choose branch AMA from a drop down box and have another field auto populate with the corresponding vender #.

    I have 45 of them. Branch acronyms with corresponding vender ids. This is too many for a IIF or Switch function.

    Does anyone know how to do this?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    the you set a combo box with the query of Branches, and it will have 2 columns
    BranchName, Vendor#

    the combo properties:
    bound col = 1 (this holds the name)
    columns = 2 (so both columns can be used)
    columnwidths = 1;0 (zero will hide the Vendor#, or you can make it visible with any #>0)

    in the AFTERUPDATE event of the combo, set the textbox to the vendor#

    Code:
    sub cboBox_aftgerupdate()
      txtBoxVendor = cboBox.column(1)   'NOTE in vb code columns start with zero.  So here we are setting the vendor to col 2 in the query.
    end sub

  3. #3
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23
    yes, but this drop down is within a query, not on a form. so how can i embed the AFTERUPDATE into the combo box within a query?

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Queries don't have comboboxes, tables are joined to get the required data. Is it attached to the table? Maybe you can show us what you are referring to.

  5. #5
    gameemaster is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Sep 2016
    Posts
    23
    Yes, I have a column in a table that is in a query im using, is what i meant. That column is a look-up column where the values to look up are in another table. but im working out of a query. either way. what im looking for is this: the column name is "branch" each branch has a vender id that does not change. so when I choose a branch from the drop down, I would like another column named "vender id" to populate with the associated number. however, there are almost 50 and that won't fit in a IIF or switch statement. Since im working out of a query, I think i just need a simple join, i dont know. any ideas? or should i try to explain further?

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Remove all lookups from your tables, they create problems and issues in many ways. It is far better to be in control of your data and your joins. A simple query of bringing in both tables and joining on the required field will give you the data that you need and then you can populate fields as desired.

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

Similar Threads

  1. Replies: 2
    Last Post: 12-20-2016, 08:55 AM
  2. Replies: 1
    Last Post: 08-10-2014, 04:06 PM
  3. Replies: 2
    Last Post: 12-04-2013, 05:38 PM
  4. Replies: 3
    Last Post: 06-12-2013, 08:53 PM
  5. Replies: 3
    Last Post: 10-03-2011, 02:33 PM

Tags for this Thread

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