Results 1 to 14 of 14
  1. #1
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8

    populate boxes based on combo box choice

    Newbie here (to Access). Pretty good in Excel macros and VBA. I get the theory but the syntax eludes me.



    On a form; if I have a combobox, and I select a record from it, what code and settings do I need to populate the other boxes with data from the same record in the same table? Do I use VBA code inthe Afterupdate section of what control?

    (I tried-- me.text1 = me.combobox1.column(1) but it does not "have the focus")

    Thanks in advance.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Yes, the after update event would be the appropriate event (of the combo). If you're getting an error about focus, you're likely using the .Text property of one of the controls. Drop that or change it to .Value, which is the default if unspecified.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8
    Paul,

    Great, Thanks. I am starting to get output.

    I can get the same value in the combobox (Company) into the dependent field with;

    contactfirstname.value = me.companyname

    But I do not know how to access other columns of the same row that the combobox has selected in the table.

    The table is set up;

    Companyname Address City Contactfirstname Contactlastname

    Thanks!

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You had it above:

    me.combobox1.column(1)

    as long as the row source of the combo includes the fields.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8
    Paul,

    Sorry, but I don't know what to type or where to set the conditions so that, as you said; "the row source of the combo includes the fields".

    I see the rowsource property, but what should it say? And if I include other columns in the row source, will it only show the bound one to the user?

    Thanks for your help.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The row source is either a table, saved query or SQL (SELECT...). I assume it's SQL, so you'd adjust it to include the desired fields. If you click on the ellipsis (...) it will open the QBE which should let you add them easily. You control which are shown with the column widths property of the combo (the first visible column will the one displayed when the combo is not dropped down). If you add fields to the row source SQL, make sure to change the column count property of the combo.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8

    Thumbs up

    Paul,

    Hi. OK here's where I am;

    I added the other two column names (contactfirstname & contactlastname) to the SQL query statement in the rowsource of the combobox. I changed the columns to 3. The three columns now show when the combobox is dropped down which is confusing.

    When I run it I get error 2448 "Can't assign a value to that object"

    It stops on the statement;
    contactfirstname.value = me.companyname.column(2)

    Thanks for your help.

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What's the control source of contactfirstname?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8
    The controlsource of ContactFirstName is blank.

    Thanks for sticking with me here.

  10. #10
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8
    Paul,

    OK, now I see that when controlsource of the target field on the form is blank, then you can load it from the VB code. I thought it was like Excel where you lock on a row and can pull up other columns from that row.

    Is there a different version of the command that loads the target listbox that does not require that several fields all be visible in the combobox that is used for selection? In other words; having selected a value in the combobox, can I use that row number or a .Find to extract other fields from the same record by accessing the table directly? It seems if you have 20 fields or more they would not all need to be visible or loaded into the combobox.

    It would be nice to see just one field in the combobox and yet have the dependent fields loaded by the code.

    Thanks for getting me this far.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, you could open a recordset using the key value from the combo, but I'd use the combo method. Then you're making one trip to get data instead of two. The other fields don't have to be visible; you can hide them using the column widths property of the combo (using a width of zero).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8
    Paul,

    Getting back to this project. I get one of two effects when I try to see just the company name in the combobox;

    if column count =3 and column width =0; then no company name, my selection field, appears in the combobox. Only contact firstname and contact lastname appear in the combobox and my listboxes dependent on this combo don't work.

    if column count =3 and column width =blank; then company name does appear in the combobox along with contact firstname and contact lastname. (I'd rather it only be company name) But on the good side, my dependent listboxes work.

    Rowsource references companyname, contactF-name and ContactL-Name.
    bound column = 1

    What settings do I need to; 1) get just company name to show in the combobox, and 2) when a company name is selected, the corresponding contact name shows up in the dependent listboxes?

    Thanks so much.

  13. #13
    Mattm1958 is offline Novice
    Windows XP Access 2002
    Join Date
    Aug 2010
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    8
    OK, I see from a simple example posted by Ryan1313 that columnwidth can be 1;0;0 if you have three columns. So, now only the company name shows in the combobox. Paul, I didn't know there was a setting for each of the three columns.

    I think I'm good for now since I'm in scenario 2 above where the dependent listboxes work and now only companyname shows in the combo.

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to hear it. Post back if you get stuck.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Populate a field based on combobox selection
    By rscott7706 in forum Access
    Replies: 5
    Last Post: 06-02-2011, 03:18 PM
  2. Replies: 3
    Last Post: 08-25-2010, 09:03 AM
  3. Replies: 3
    Last Post: 05-25-2010, 02:16 PM
  4. Replies: 1
    Last Post: 03-27-2010, 06:13 AM
  5. Populate one field based upon another fields data
    By BigBrownBear in forum Queries
    Replies: 1
    Last Post: 03-23-2010, 04:27 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