Combo box wizard "Find & GoTo Record" missing in Access 2007 beta

[Guest]

The Combo box wizard which allowed you to easily create a control to look up
a text field in the form, select and go to the record containing that value,
appears to be missng from Access 2007 beta 2. Databases imported from Access
2003 which have forms with this type of control do, however, still function
properly.
Anyone know why this wizard has been omitted from Access 2007 beta 2, and
whether it will be reinstated?

 

[Brendan Reynolds]

I just checked, John, and it is not missing. When I add a combo box to a
form with control wizards enabled, I get three options, the third of which
is the one you're looking for: 'Find a record on my form based on the value
I selected in my combo box'.

You get this option only with a bound form, of course. You won't get the
option if the form is unbound. That's not a change, though, that's the same
behaviour as in earlier versions.

 

[RoyVidar]

The Combo box wizard which allowed you to easily create a control to
look up a text field in the form, select and go to the record
containing that value, appears to be missng from Access 2007 beta 2.
Databases imported from Access 2003 which have forms with this type
of control do, however, still function properly.
Anyone know why this wizard has been omitted from Access 2007 beta 2,
and whether it will be reinstated?

I don't know anything about 2007, but I've seen the same behaviour in
the previous versions. I thin one possibility, in previous versions, is
that it might not give this if the form is unbound, or bound to a
dynamic SQL string, while if bound to a stored query or table, it might
give it.

Could this be the case in 2007 too?

If you need any help with the code, please post.

 

[Guest]

I am not exactly sure what a "bound" form is compared with an "unbound" one.
But looking again, I see that the form is based on a query rather than
directly on a table. In fact I simplified the tables eliminating some
redundant fields so that I now no longer need to base the form on a query -
the base table has one straightforward lookup field (an indexed text field
for a name) which should display the name properly in a normal combo box.
I'll rebuild the form directly from the table which should be fairly simple.

 

[Brendan Reynolds]

A bound form would have a blank Record Source property. If you have a table
name, a query name, or a SQL statement in the Record Source property, that's
a bound form.

I tested using the Access 2007 Assets' template. I created a query that
joined the Contacts and Assets tables, then created a form bound to that
query. The 'Find a record' combo box wizard was still available with that
form. So then I tried using the SQL statement from that same query in the
Record Source property, instead of the name of the query. The wizard no
longer included the third option. I tried the same thing, with the same
result, in Access 2003, so this isn't a change in Access 2007.

It appears that the wizard is limited to working with forms that have a
table name or the name of a saved query as the Record Source. It seems it
doesn't work with SQL statements.

If that's the problem, you could save your SQL statement as a saved query,
use the name of that query as the Record Source for your form, run the
wizard, then if you wish you can change the Record Source property back to
the SQL statement again. Or just run the wizard once, on a form that is
bound to a table or saved query, and examine the macro the wizard creates to
learn how to do it without the wizard. It's a fairly simple macro with just
one action. The wizard creates it as an embedded macro, but if you open it
in the macro designer you can save it as a named macro, so that you can
refer to it in the future for reference.

 

[Brendan Reynolds]

Oops! That first sentence should have read "An unbound form would have a
blank Record Source property."

 

[Guest]

Thanks Brendan, expecially for the tests and results you carried out and
described. That has helped me considerably to understand some features of
Access.

John Tempest