Need to understand combo-box behavior: Access 2010

Topic

New Reply

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!

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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!

Reply | Quote

Thank you! That makes perfect sense. Can’t find records that aren’t yet “there”.

The fog has lifted. I appreciate the response!

Reply | Quote

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 …

Reply | Quote