Why does Combo Box Wizard give only 2 choices?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In Access 2000, the combo box wizard always provides 3 choices, the last
being to find a record on my form based on the value selected in my combo
box. But, in Access 2002/2003, the wizard sometimes only provides the first
2 choices and sometimes all 3. What causes the wizard to not offer the 3rd
choice?
 
artgilbert said:
In Access 2000, the combo box wizard always provides 3 choices, the last
being to find a record on my form based on the value selected in my combo
box. But, in Access 2002/2003, the wizard sometimes only provides the first
2 choices and sometimes all 3. What causes the wizard to not offer the 3rd
choice?
Click to expand...

If your form is not bound then the third choice is not available (no
RecordsetClone to use).
 
Rick Brandt said:
If your form is not bound then the third choice is not available (no
RecordsetClone to use).
Click to expand...

I should add that this is not version dependent. All versions act the same way.
 
Thanks, Rick, but I am afraid that it IS different in Access 2000 and Access
2003. I have a Access 2000 DB with a form that has a combo box already on it
that finds a record with changed. In Access 2000, when I add another combo
box, the wizard gives me 2 choices. When I do the exact same in Access 2003,
I only get 2 choices on the first wizard screen.

Art
 
artgilbert said:
Thanks, Rick, but I am afraid that it IS different in Access 2000 and Access
2003. I have a Access 2000 DB with a form that has a combo box already on it
that finds a record with changed. In Access 2000, when I add another combo
box, the wizard gives me 2 choices. When I do the exact same in Access 2003,
I only get 2 choices on the first wizard screen.
Click to expand...

Is the form bound? The behavior I see is that if the form is bound I get
all three options in the wizard and if it is unbound I get only the first
two. I just tested this in all four versions from A97 to A2K3 and they all
behaved identically.
 
If the form is bound directly to a table OR a saved query object you will be presented with the
three choices in 97, 2000, 2002, and 2003. If however, your form is based on a SQL statement in the
RecordSource then you will be presented with only two options. Here is a past post by MVP Albert
Kallal on the subject:

<Begin quote>
"Sorry about this. Here is how you duplicate this problem:

You have a form based on a table..and then hit the [...] button, and then
base the form on embedded sql..not query. If you do this..then you only get
two choices. And, I just checked...and this still occurs in a2003....

So, it is likely that the original poster invoked the query builder on the
table..and saved the sql in the form's data source. If you put in a query
name, or a table name..you can then use the wizard and you get 3
choices....then put back in the sql you had!"
<End quote>
 
You are correct in that the problem in Access 2002/2003 is caused by the
record source of the form being based on an SQL statement, not on a Query or
Table. However, in Access 2000, the behavior of the wizard is not the same
using the exact same form.

Regardless, the mystery/problem I was having is now solved. Thanks for your
help!
 
Jeff Conrad said:
If the form is bound directly to a table OR a saved query object you will be presented with the
three choices in 97, 2000, 2002, and 2003. If however, your form is based on a SQL statement in the
RecordSource then you will be presented with only two options. Here is a
Click to expand...
past post by MVP Albert

Ok, I didn't test with a SQL statement, but haven't we always been told
that those are compiled and stored as hidden queries just like the ones
shown in the db window? Seems strange that just because there is not a
"Named" object in that property that the wizard would act differently.
 
Hi Rick,
Ok, I didn't test with a SQL statement, but haven't we always been told
that those are compiled and stored as hidden queries just like the ones
shown in the db window? Seems strange that just because there is not a
"Named" object in that property that the wizard would act differently.
Click to expand...

The experts at MS would probably be the best ones to answer that question, but I'll take a wild
guess. I believe that the SQL statements for forms are saved as some sort of hidden query, but have
a name starting with "~". The combo box wizard code is probably only set up to search the existing
database container for saved table and query objects AND ignore names with starting with the "~"
symbol. Or it may only search for objects in the MSysObjects system table with Types that correspond
with saved tables and queries. Again, these are just wild guesses.
 
Back
Top