Results 1 to 11 of 11
  1. #1
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72

    select a value in a comb box and then a abbreviation is shown in a text box from the value selected

    Hi everybody,



    How do you select something in a comb box and automatically appear a text which is related to it? In my case, I have one field which is called "Factory_name" and it is filled with a list of values I wrote previously.

    We have to send reports to our client with some type of code in a label. But, usually the factory´s name is too long to be fit in that label so we decided to write some abbreviation which identifies each factory.
    For example:

    - Madrid -> MAD
    - Barcelona -> BAR
    - Thüringen -> THU
    - South Carolina - > SHC
    - Burgos -> BG


    The idea is when a Factory is selected from a comb box located in a form, a text box is autofilled. And then by using "CodigoRFQ" shows the following format (date-fabric(abbreviation)-ID). I asked this before in this post (https://www.accessforums.net/showthread.php?t=76096) (Tomorrow I will try and tell you about haha)

    How do I make this relations?

    Thank you very much for your help,

    Regards.

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Your combo box needs both columns BAR and Barcelona. Make sure the short code column is the bound column.
    You can either display both columns or just one.
    Then add code to the combo after update event similar to Me.TextboxName=Me.ComboName.Column(1).
    As column numbering is zero based this will be the 2nd column Barcelona

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    VBA code is not needed to display the column value in textbox. Set ControlSource with expression: =Me.ComboName.Column(1)
    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.

  4. #4
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Hello,

    It works! but when I go to a new record both Comb box and Text box remain the values I selected before.

    How do I set that the field becames like null before selecting anything?

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The combobox is UNBOUND?
    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.

  6. #6
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Yes, i think.
    I create a comb box that has two columns with a structure similar to:

    Madrid MAD
    Barcelona BAR
    Burgos BG

    And I followed your instructions. It seems like it does not recognize the values when I scroll through my form. For example when a field is not introduced, this two text boxes keep mainting the values of the comb box.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, UNBOUND controls will display the same value for every record.

    What is purpose of combobox? To select search criteria? Or to enter value into 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.

  8. #8
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Thank you June for your help.

    In my form I have two ways to enter the information. or by hand or by exporting a excel file. Both ways work fine.

    My purpose is to enter values in a record. I can do this by hand selecting each comb box but it is time consuming because I need to make this relationship between [factory_name] and [factory_code].

    Also, each record have to show me the values (something is no value) when I navigate throught the form.
    Last edited by mar7632; 03-18-2019 at 05:18 AM. Reason: word

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Sorry, don't understand. If combobox is BOUND to a field then its value will change when moving to another record. If it is not BOUND then it won't change unless you manually change or use VBA to set it to Null.
    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.

  10. #10
    Minty is online now VIP
    Windows 10 Access 2016
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Also as stated in another thread, if you store the factory code, you never need to store the factory name as well.
    It can always be looked up.

    This is a fundamental part of relational database design. Only store what you need to, and don't duplicate data.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  11. #11
    mar7632 is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Feb 2019
    Location
    Spain - Madrid
    Posts
    72
    Sorry, I have solved the problem. (I think)

    The code is:

    Code:
    Private Sub cboFactoryandcode_AfterUpdate()
    Me.txtCode_Factory = Me.cboFactoryandcode.Column(1)
    Me.txtFactory = Me.cboFactoryYcode.Column(0)
    End Sub
    And in control Source I have written the field related to.

    And now when I scroll through the records it updates every time.

    Thank you for your patience.

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

Similar Threads

  1. Full name in table to abbreviation in query
    By pldm123 in forum Queries
    Replies: 1
    Last Post: 06-13-2016, 05:18 PM
  2. Configure a Comb Box to be read-only in a form
    By ZodiacPilot in forum Forms
    Replies: 21
    Last Post: 10-03-2015, 09:26 PM
  3. Replies: 2
    Last Post: 02-14-2015, 04:22 AM
  4. Replies: 11
    Last Post: 06-11-2014, 04:50 PM
  5. Replies: 8
    Last Post: 06-06-2012, 12:28 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