Error using 'Row Source Type' table/query with RWOP query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

synthkid - 29 Jun 2006 18:17 GMT

In an Access multi user application I am able to read and update
backend db tables fine using RWOP queries.

One table (TABLE A) in the backend db uses the Combo Box, RowSourceType
= Table/Query settings or some fields so they display drop downs on
input forms.

In the client app form these Combo Box values will only show up if I
copy the 'sub-tables' that are the RowSource tables, into the client
db. The sub-tables won't populate the drop downs if left in the
backend db.

Other than using Value List instead of Table/Query to value drop downs
(which I have not tried yet), is there any way to be able to keep the
RowSource tables in the backend where they belong? I could distribute
the tables with the drop down constants with the client app but it
seems clunky after all other hassles I'm able to avoid.

Thanks for any help or suggestions.

Reply to this Message

Larry Linson - 30 Jun 2006 03:26 GMT

Simple matter: don't allow "lookup fields" in any tables. They are
invariably more trouble than they are worth, as witness the number of posts
about lookup field problems in these newsgroups.

Larry Linson
Microsoft Access MVP

> In an Access multi user application I am able to read and update
> backend db tables fine using RWOP queries.
[quoted text clipped - 15 lines]
>
> Thanks for any help or suggestions.

Reply to this Message

synthkid - 30 Jun 2006 15:27 GMT

> Simple matter: don't allow "lookup fields" in any tables. They are
> invariably more trouble than they are worth, as witness the number of posts
> about lookup field problems in these newsgroups.
>
> Larry Linson
> Microsoft Access MVP

Thank you Larry

As I still need to limit forms input using drop-downs (there is no way
out of this) can you suggest another mechanism for doing this that will
allow me to update the available values without using lookups in the
backend db? The values will need to be updated after deployment. I can
think of ways to do this but if there is a better way that will be a
big help.

Harry S (synthkid)

Reply to this Message

Larry Linson - 01 Jul 2006 02:39 GMT

Using lookup techniques, with Combo or List Boxes in Forms, is just fine. It
is Lookup Fields in Tables that will "getcha" sooner or later, like when you
create a Query and it doesn't return what you see in the Datasheet View of
the table, but instead a number, and it costs you time and work to fix that
issue. That's when you slap yourself on the side of the head and vow to
implement your lookups yourself.

You create tables relating an ID to a value, and use that table, or a Query
referring to it, in a Combo or List Box.

Users shouldn't be working in Datasheet View on developed applications,
anyway, as there are 'way too many chances for error.

Larry Linson
Microsoft Access MVP

>> Simple matter: don't allow "lookup fields" in any tables. They are
>> invariably more trouble than they are worth, as witness the number of
[quoted text clipped - 14 lines]
>
> Harry S (synthkid)

Reply to this Message