Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    kiwichick is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    31

    Fill textbox based on Combo Box selection

    Hi everyone,

    I have a table called tblFamily which includes:
    a field called FName (short text)
    a field called FPronunciation (short text)



    On my form I have:
    a combo box called Family (with FName as control course)
    a textbox called FamilyPronunciation

    When I make a selection from the Family combo box, I want the corresponding FPronunciation text to automatically fill the FamilyPronunciation textbox but I'm unsure of how to make that happen. I hope that makes sense. Any help greatly appreciated. Thanks in advance :-)

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Make your combo record source to include 2 fields, FName and FPronounciation. Set the width of the second column to be zero if you don't want to show it. On combo AfterUpdate event, make textbox control source equal combo column(1) (column count is zero based).

    You appear to be using real data (the value in FName) as a primary key field, which is not advisable. If the textbox you want to populate is bound to some field, that makes little sense if that field is in a different table. It would mean that you're keeping the same data in 2 places. If it's not bound to a different table, then perhaps your form controls ought to be bound to tblFamily and you filter the form records according to the combo value chosen. In that case, you usually would not bind the combo to any field, otherwise haphazard editing of the bound field can occur. You'd just use the combo as a filtering device to only show data that's related to the chosen combo value.

    If none of that helps a lot, you probably need to expound on what it is you have, are doing, and are trying to do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the combo afterupdate event:
    the combo has 2 or 3 columns, pull values from that

    txt
    FamilyPronunciation = cboFam.column(1)

  4. #4
    kiwichick is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    31
    Quote Originally Posted by ranman256 View Post
    in the combo afterupdate event:
    the combo has 2 or 3 columns, pull values from that

    txt
    FamilyPronunciation = cboFam.column(1)
    Thanks heaps. Would you mind answering a few questions, please?

    1. Is "txt" something I'm supposed to include? If so, is it supposed to be on a separate line?
    2. I'm not sure what cboFam is. It doesn't relate to any of the names I've provided, so have you just used it as an example?
    3. Is the number "1" in column(1) referring to the number of the column that has the pronunciation? In my case that would be column 3 (table has 3 columns ID, FName, FPronunciation). Unless the count starts from 0 in which case it would be column 2.
    4. Do I just put that line of code into the Combo Box/Property Sheet/Event/After Update, or do I need something else?

    Thank you so much for your patience with a not very experienced databaser

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    They were examples. Should use whatever your object names are. Use Me prefix.

    Me.FamilyPronunciation = Me.Family.Column(1)

    Instead of VBA, have expression in textbox ControlSource: =Me.Family.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.

  6. #6
    kiwichick is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    31
    Quote Originally Posted by Micron View Post
    Make your combo record source to include 2 fields, FName and FPronounciation. Set the width of the second column to be zero if you don't want to show it. On combo AfterUpdate event, make textbox control source equal combo column(1) (column count is zero based).

    You appear to be using real data (the value in FName) as a primary key field, which is not advisable. If the textbox you want to populate is bound to some field, that makes little sense if that field is in a different table. It would mean that you're keeping the same data in 2 places. If it's not bound to a different table, then perhaps your form controls ought to be bound to tblFamily and you filter the form records according to the combo value chosen. In that case, you usually would not bind the combo to any field, otherwise haphazard editing of the bound field can occur. You'd just use the combo as a filtering device to only show data that's related to the chosen combo value.

    If none of that helps a lot, you probably need to expound on what it is you have, are doing, and are trying to do.
    Thank you so much. Yes sorry, I can see I should have included more information. Hopefully the following will clairfy things.

    My main table is called tblPlants. This is where the record for each plant is stored.
    The combo box selection will be stored in the field Family. In the Property Sheet I have the combo box control source as Family, and Row Source as: SELECT tblFamily.FName FROM tblFamily;
    The corresponding pronunciation will be stored in the field FamilyPronunciation. In the Property Sheet I have the textbox with control source as FamilyPronunciation.

    The table tblFamily is a table with fields ID, FName (options for combo box), FPronunciation (pronunciation for each option in FName). I have entered the information in the table for each name and pronunciation (eg: FName=Cactaceae, FPronunciation=cak-TAY-see-eye).

    So what I would like is to make a selection from the combo box, then my selection and its corresponding pronunciation are stored in tblPlants.

    Where you wrote "On combo AfterUpdate event, make textbox control source equal combo column(1) (column count is zero based)." I totally get what you're saying and I thought that was what I was supposed do but I don't know exactly what I'm supposed to put in there for that (in regards to which names I use - the tblFamily names or the tblPlants names). I tried FamilyPronunciation = FName.column(2) bt it didn't work. Am I even close with that?

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Should save ID from tblFamily into record. Combobox properties settings:

    Name: cbxFamily
    RowSource: SELECT ID, FName, FamilyPronunciation FROM tblFamily ORDER BY FName;
    ColumnCount: 3
    ColumnWidths: 0";1";1"
    BoundColumn: 1
    ControlSource: whatever field you want to save ID into

    Now expression in textbox ControlSource: =cbxFamily.Column(2)

    No VBA needed.
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My main table is called tblPlants. This is where the record for each plant is stored.
    The combo box selection will be stored in the field Family. In the Property Sheet I have the combo box control source as Family, and Row Source as: SELECT tblFamily.FName FROM tblFamily;
    This isn't making sense to me. To put it another way, you are saying that the combo row source comes from tblFamily.Fname, and the combo is bound to a field in the same table, thus the form has to be bound to that same table, and you are trying to store list items that come from that table into the same table but in a different field?? So the data you select will come from a table where the combo is also bound to?? If you want to make a textbox show the pronunciation value, it obviously has to be there already, plus you are saying that it is in the same table? I can't make heads or tails around that.

    Maybe June7 understands it better than me, but I seem to have a totally weird mental picture of the thing, in which case I couldn't be showing you what to do.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I read that Family is field in tblPlants and combobox is bound to that field with a RowSource from tblFamily.
    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
    kiwichick is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    31
    Quote Originally Posted by Micron View Post
    This isn't making sense to me. To put it another way, you are saying that the combo row source comes from tblFamily.Fname, and the combo is bound to a field in the same table, thus the form has to be bound to that same table, and you are trying to store list items that come from that table into the same table but in a different field?? So the data you select will come from a table where the combo is also bound to?? If you want to make a textbox show the pronunciation value, it obviously has to be there already, plus you are saying that it is in the same table? I can't make heads or tails around that.

    Maybe June7 understands it better than me, but I seem to have a totally weird mental picture of the thing, in which case I couldn't be showing you what to do.
    Oh my god, I'm so sorry. I'm obviously not explaining well. Does this help?

    Yes, I am saying:
    you are saying that the combo row source comes from tblFamily.Fname
    No, It wasn't my intention to say:
    the combo is bound to a field in the same table
    If by 'bound' you mean the 'control source', my previous reply said:
    I have the combo box control source as Family
    Family is a field in tblPlants (not tblFamily) where the combo box selection will be stored.

    I can make a selection from the combo box and it is stored in tblPlants. That part works fine. It's getting the pronunciation part to work.

  11. #11
    kiwichick is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    31
    Quote Originally Posted by June7 View Post
    I read that Family is field in tblPlants and combobox is bound to that field with a RowSource from tblFamily.
    Yes that is correct.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Did you try suggested settings in post #7?
    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
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    OK, ignore me. It's probably because of these descriptions of your combo:

    This means it's bound to tblPlants.Family?
    The combo box selection will be stored in the field Family
    This means it's bound to FName, which you said is in tblFamily because I just assumed "control course" was a typo for "control source".
    a combo box called Family (with FName as control course)
    Maybe you meant rowsource, but they are not the same thing.

    Personally, I would not use the same name for a control and a field related to the same table, much less a different table. Nor would I use the name of a table for a field name in some other table (granted, the tbl prefix makes them not exactly the same, but too close). Or maybe I'm talking out of my hat and June7 has it figured out. Regardless, I think you've been given the right advice about 3 times now, and it's just a matter of applying what you've been given. I realize you are struggling with interpreting how to do that, but I'm not being of much help so I will bow out. Don't worry, you are in good hands.
    Last edited by Micron; 07-02-2020 at 09:10 PM. Reason: clarification

  14. #14
    kiwichick is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    31
    Quote Originally Posted by June7 View Post
    Did you try suggested settings in post #7?
    Thanks, yes I did. No it didn't work. Sorry but I guess I'm just not explaining things very well. I seem to be creating more and more confusion. I really appreciate everything you've done but I think I'll just go with what you've given me and see if I can figure it out myself.

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It should work. 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.

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

Similar Threads

  1. Replies: 4
    Last Post: 10-13-2014, 09:20 AM
  2. Textbox based on a Combo Box Selection
    By accessbuddy in forum Forms
    Replies: 2
    Last Post: 07-15-2014, 06:03 AM
  3. Replies: 3
    Last Post: 02-21-2014, 11:36 AM
  4. Replies: 2
    Last Post: 01-26-2013, 07:53 PM
  5. Replies: 2
    Last Post: 09-17-2010, 09:53 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