Running a combo box on a continuous form

[Peter Hallett]

I have a continuous form bound to a table containing names, addresses and
post codes. A combo box, in each record, lists service agents covering the
area identified by the associated post code. On GotFocus, the combo box runs
a selection query taking the associated post code as the criterion. A
requery is necessary to ensure that the list of service agents is refreshed
on changing records but that all works fine. Clicking the combo box
drop-down arrow for any record confirms that the correct list of service
agents is displayed in each case. However, no selection can then be effected
from the drop-down list, nor can any manual input be made, despite the fact
that Limit to List is set false. My initial thought was that I had failed to
set Allow Additions in the form properties, which can cause this sort of
behaviour, but that is not so.

If the service agent combo box is unbound, then selection is possible but,
because the form is continuous, all the service agent fields are
simultaneously updated to show the same selected name. Before I start
looking for complex answers, I would like to check that I have not missed
something obvious – it wouldn’t be the first time.

With up to two or three hundred records being displayed at any one time, and
with a need to be able to scroll these, Single Form view does not offer a
realistic alternative.

 

[ruralguy via AccessMonster.com]

Is the query that your continuous form has as a RecordSource updateable?

 

[Peter Hallett]

That is a very good question. While awaiting a response, I did a bit more
work and discovered that the problem does not just affect the combo box.
Other controls on the form will also not accept input. Since so much effort
was put into designing the combo box, which is rather involved, it was
natural to suppose that it was the source of the resulting problems but I may
have been adding two and two together and getting five. The source query is
associated with a query that counts records and I have found that when these
are included in, or referred to by, a subsequent query an 'update' error can
result even though that query is not itself updated. One way out is to
create an intermediate table from the record counting query and then to use
that as the source in any subsequent forms. It is early days to be certain,
but I may have a similar problem here, as you suspect. The lesson perhaps is
not to jump to conclusions. I'll do a bit more work and report back.

 

[ruralguy via AccessMonster.com]

Excellent! If you do not have it, here is a link for your Bookmarks:
Why is my query read-only?
http://allenbrowne.com/ser-61.html

That is a very good question. While awaiting a response, I did a bit more
work and discovered that the problem does not just affect the combo box.
Other controls on the form will also not accept input. Since so much effort
was put into designing the combo box, which is rather involved, it was
natural to suppose that it was the source of the resulting problems but I may
have been adding two and two together and getting five. The source query is
associated with a query that counts records and I have found that when these
are included in, or referred to by, a subsequent query an 'update' error can
result even though that query is not itself updated. One way out is to
create an intermediate table from the record counting query and then to use
that as the source in any subsequent forms. It is early days to be certain,
but I may have a similar problem here, as you suspect. The lesson perhaps is
not to jump to conclusions. I'll do a bit more work and report back.

Is the query that your continuous form has as a RecordSource updateable?

[quoted text clipped - 20 lines]

 

[Peter Hallett]

I have now got to the bottom of the matter but I don’t really understand it.
I must study the reference to which you direct me.

The form that would not allow data input had as it source two joined tables,
say A and B. B was derived from A by means of a record totalling query.
Together with the other required fields, these totals are displayed in B. As
I mentioned earlier, this is not the first time that I have encountered
problems when such queries have been included with tables, as form sources,
or have been used to create such tables. In this case, however, only tables
A and B appeared as the sources for the form and both seemed to be updatable.
Nevertheless, the resulting form refused to accept data input, not just in
the combo box, whilst table A was included as a source. By changing the
record totalling query to include all the wanted fields from A in table B and
eliminating table A as a source for the form featuring the combo box, normal
operation of the latter was restored. A somewhat peculiar result, I feel,
because it is table B that is associated with the record totalling query and
not table A. No doubt all will be revealed when I read Allen Browne's piece.

As for lessons learned, mine are :-
1. Don’t assume that malfunction is due to the last, or even the most
complicated thing you did.
2. Beware of using record totalling queries as form sources. It can be
safer to use them to create intermediate tables and then to use those as form
sources, as I suggested earlier.

Thanks for your help. It is all just a bit clearer now.

--
Peter Hallett

Excellent! If you do not have it, here is a link for your Bookmarks:
Why is my query read-only?
http://allenbrowne.com/ser-61.html

That is a very good question. While awaiting a response, I did a bit more
work and discovered that the problem does not just affect the combo box.
Other controls on the form will also not accept input. Since so much effort
was put into designing the combo box, which is rather involved, it was
natural to suppose that it was the source of the resulting problems but I may
have been adding two and two together and getting five. The source query is
associated with a query that counts records and I have found that when these
are included in, or referred to by, a subsequent query an 'update' error can
result even though that query is not itself updated. One way out is to
create an intermediate table from the record counting query and then to use
that as the source in any subsequent forms. It is early days to be certain,
but I may have a similar problem here, as you suspect. The lesson perhaps is
not to jump to conclusions. I'll do a bit more work and report back.

Is the query that your continuous form has as a RecordSource updateable?

[quoted text clipped - 20 lines]

with a need to be able to scroll these, Single Form view does not offer a
realistic alternative.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

*Anything* that creates an ambiguous recordset will make it non-updateable as
Allen's link describes. A join by itself does *not* do it but joining a table
to a query that is ambiguous does.

I have now got to the bottom of the matter but I don’t really understand it.
I must study the reference to which you direct me.

The form that would not allow data input had as it source two joined tables,
say A and B. B was derived from A by means of a record totalling query.
Together with the other required fields, these totals are displayed in B. As
I mentioned earlier, this is not the first time that I have encountered
problems when such queries have been included with tables, as form sources,
or have been used to create such tables. In this case, however, only tables
A and B appeared as the sources for the form and both seemed to be updatable.
Nevertheless, the resulting form refused to accept data input, not just in
the combo box, whilst table A was included as a source. By changing the
record totalling query to include all the wanted fields from A in table B and
eliminating table A as a source for the form featuring the combo box, normal
operation of the latter was restored. A somewhat peculiar result, I feel,
because it is table B that is associated with the record totalling query and
not table A. No doubt all will be revealed when I read Allen Browne's piece.

As for lessons learned, mine are :-
1. Don’t assume that malfunction is due to the last, or even the most
complicated thing you did.
2. Beware of using record totalling queries as form sources. It can be
safer to use them to create intermediate tables and then to use those as form
sources, as I suggested earlier.

Thanks for your help. It is all just a bit clearer now.

Excellent! If you do not have it, here is a link for your Bookmarks:
Why is my query read-only?

[quoted text clipped - 18 lines]

 

[Peter Hallett]

I have now read Allen Browne's article, to which you kindly directed me.
That certainly adds the necessary weight to the suspicions and clears up
another Access mystery. Oh that it were the last!
--
Peter Hallett

*Anything* that creates an ambiguous recordset will make it non-updateable as
Allen's link describes. A join by itself does *not* do it but joining a table
to a query that is ambiguous does.

I have now got to the bottom of the matter but I don’t really understand it.
I must study the reference to which you direct me.

The form that would not allow data input had as it source two joined tables,
say A and B. B was derived from A by means of a record totalling query.
Together with the other required fields, these totals are displayed in B. As
I mentioned earlier, this is not the first time that I have encountered
problems when such queries have been included with tables, as form sources,
or have been used to create such tables. In this case, however, only tables
A and B appeared as the sources for the form and both seemed to be updatable.
Nevertheless, the resulting form refused to accept data input, not just in
the combo box, whilst table A was included as a source. By changing the
record totalling query to include all the wanted fields from A in table B and
eliminating table A as a source for the form featuring the combo box, normal
operation of the latter was restored. A somewhat peculiar result, I feel,
because it is table B that is associated with the record totalling query and
not table A. No doubt all will be revealed when I read Allen Browne's piece.

As for lessons learned, mine are :-
1. Don’t assume that malfunction is due to the last, or even the most
complicated thing you did.
2. Beware of using record totalling queries as form sources. It can be
safer to use them to create intermediate tables and then to use those as form
sources, as I suggested earlier.

Thanks for your help. It is all just a bit clearer now.

Excellent! If you do not have it, here is a link for your Bookmarks:
Why is my query read-only?

[quoted text clipped - 18 lines]

with a need to be able to scroll these, Single Form view does not offer a
realistic alternative.

--
RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Learning Access is a forever project. There is always something else in
Access to learn. Have fun.

I have now read Allen Browne's article, to which you kindly directed me.
That certainly adds the necessary weight to the suspicions and clears up
another Access mystery. Oh that it were the last!

*Anything* that creates an ambiguous recordset will make it non-updateable as
Allen's link describes. A join by itself does *not* do it but joining a table

[quoted text clipped - 32 lines]