Combobox queries making a form uneditable

[paul.gunther]

I have a form which is used to insert/edit into a table - sundry
fields on this form are populated via comboboxes (with underlying
queries which return an id field and a name field).

The underlying table has its own AutoNumber id and foreign key id's to
the fields populated by comboboxes.

Now, the issue I have discovered is that if the query to populate one
of these comboboxes as more complex than "normal" when I order by that
field (which gets internally/automatically converted into "order by
lookup_thingoId.name"), I lose the ability to edit or append records.

The two examples are these:

1/. The query to populate one combo box does a union - the id could be
a Vendor or a VendorLocation. I.e.: Some vendors don't have multiple
locations so the foreign key id could point to either table.

2/. The other combo box has a query which internally checks to see how
many records exist for a given name and appends a bit of extra text to
the returned name field if the count > 1. In other words, that query
joins to an aggregate query on the (foreign) table's id.

I would naively expect that because the queries in question are
internal to populating combo box contents, as opposed to the top-level
table, they should not make any difference to the editability of this
top-level?

Ideas, anyone?

Thanks in advance,
Paul.

 

[Steve Schapel]

Paul,

Shooting in the dark here a bit Mate, without the details of the queries
to look at. But there are a couple of things that come to mind, for you
to check.

First of all, does this can run the queries that you are using for the
comboboxes' Row Sources, and see that they are returning the data you
expect?

Second, the fields that the comboboxes are bound to in the form's
underlying Record Source table are apparently Number data type. Yes?
Is it possible that the data manipulation that you are doing in the
comboboxes' Row Source queries results in the comboboxes' Bound Column
now being treated a text value rather than a number?

Finally, you mentioned l"ookup_thingoId.name". Note that "name" is a
Reserved Word (i.e. has a special meaning) in Access, and as such should
not be used as the name of a field or control or database object. In
the context you have used in your example, there is definitely the
potential for confusion.

 

[Pat Hartman]

I believe the problem is caused because when you click in the combo field
and sort by it, Access modifies your form's RecordSource query to append the
order by criteria. That means that it needs to join to the RowSource query.
As we have talked about many times and many places, a query that contains
aggregate functions is not updatable.

If you don't need the concatenated data for sorting and you are willing to
have the form always sort by this field, you can add the lookup table to
your RecordSource query and select the text value. That will allow you to
add an order by of the text value in the form's recordset.

I don't believe there is any way to change Access' behavior regarding your
problem since it needs to rebuild the RecordSource in order to comply with
your sort request.

Many MVP's, myself included, have petitioned the Access team to make
modifications to Jet now that it is under their control to allow it to
create an updatable query even though parts of the query are not updatable
as is your issue.