Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Need to understand combo-box behavior: Access 2010

    Hi, everyone. I'm trying to understand how the combo box wizard works.

    Scenario #1:
    Create a new form, based on a table, using Create > Form Wizard (select the table, add all fields)
    Open the new form in design view, add a combo box. The combo box wizard appears and I have 3 choices: 1) Get the values from another table; 2) Type in my values; 3) Find a record on my form based on combo box choice


    Scenario #2:
    Create a new form by choosing Create > Form Design
    Open the new, blank form in Design view
    Select the form, choose Properties
    Set the record source by clicking the build button (ellipses) and when the SQL builder pops up, add all fields from your desired table to the design grid. Save and close the property sheet. Record source property now shows the SELECT statement (instead of table name, but all fields are included)
    Add a combo box. The combo box wizard now only offers 2 choices: 1) Get values from another table; 2) Type in values

    When a form is created using this second method, why is there no option to create a combo box that searches for records?

    Thanks for any advice!

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Midwest, USA
    Posts
    108
    Thanks
    5
    Thanked 5 Times in 4 Posts
    If the first method is used, your combo box is using a physical table so it can look up values. In the second method the combo box is looking at a form based on a select query not a table. This all depends on what you want to do with the combo box. Combo boxes are meant to look up and store a value for use by something else on your form based on the user's selection in the combo box. Choice one wants to know if you want to use the info in the table the form is based on, or look up something in another table based on a field you choose in the current table, or find a value in the current table based on your choice. Last you can type in a value directly.
    2nd choice doesn't have any records to look at until the select query is run, thus there is no current table to look up values in so you drop the 3rd choice of finding a record from your current form.

  3. The Following User Says Thank You to caveman144 For This Useful Post:

    13ILGal (2013-05-12)

  4. #3
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Thank you! That makes perfect sense. Can't find records that aren't yet "there".

    The fog has lifted. I appreciate the response!

  5. #4
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Quote Originally Posted by caveman144 View Post
    If the first method is used, your combo box is using a physical table so it can look up values. In the second method the combo box is looking at a form based on a select query not a table. This all depends on what you want to do with the combo box. Combo boxes are meant to look up and store a value for use by something else on your form based on the user's selection in the combo box. Choice one wants to know if you want to use the info in the table the form is based on, or look up something in another table based on a field you choose in the current table, or find a value in the current table based on your choice. Last you can type in a value directly.
    2nd choice doesn't have any records to look at until the select query is run, thus there is no current table to look up values in so you drop the 3rd choice of finding a record from your current form.
    That really is not correct. A table can be empty, no records to look up, and it works. If the record source is a query (saved SQL) it works. There is a "bug" in the Access Combo box wizard. The third option is only available if you use a table name or query name as the record source. It is when you use in-line SQL as the record source that the wizard does not give the third option.

    See: ACC: Combo Box Wizard Does Not Display Third Option If a Form Is Based on a SQL Statement
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  6. #5
    Star Lounger
    Join Date
    Jan 2011
    Location
    Illinois
    Posts
    62
    Thanks
    17
    Thanked 2 Times in 2 Posts
    Thanks for the additional info. It sounds like the original answer was correct (due to the bug) but not the rationale.

    I always like to learn what is going on behind the scenes, and I appreciate the comments. Thank you!

  7. #6
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    The original answer was may partially correct. They said "...the second method the combo box is looking at a form based on a select query not a table ..." which is not a problem if the select query is a saved query (object). If you had saved the "select" query it would have worked just fine.


    FYI:
    A record source can be:

    1) a table nane (This should be avoided in most cases)
    2) a query name (saved SQL - has many advantages, like pre-compiled/optimized when saved - my preferred method)
    3) in-line SQL (not a saved query). This is where the record source is a select statement. This has advantages and disadvantages.

    It is only with #3 (in-line SQL) that the Combo box wizard has an issue.

    FWIW: During development I mainly use saved queries with forms/reports for many reasons. It also avoids this issue. In some application before deployment I build a new database where the the form/reports are converted to have the SQL in the record source and the saved queries are removed. This is done before a new front end is complied into a MDE/ACCDE. Most people do not need to lock down their database this much.

    Hope that helps ...
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  8. The Following User Says Thank You to HiTechCoach For This Useful Post:

    13ILGal (2013-05-14)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •