Checkbox will not check

[milwhcky via AccessMonster.com]

I have a continuous form based on a multi-table query.

Tables (joined fields) (other fields used by the query)
TrackingNumbers (OrderID) (TrackingNumber, FreightAmt, Paid)
Orders (OrderID, CustomerID, FreightID) (OrderDate)
Customer (CustomerID) (CustomerName, City)
Freight (FreightID) (FreightName)
Sorted by FreightName, OrderDate
Paid = 0

The form is to be used when I receive bills from various freight companies...
allowing me to verify charges and check them off when I pay them. The form
lists all orders with unpaid freight bills with this information:
FreightName-OrderID-OrderDate-CustomerName-City-TrackingNumber-FreightAmt-
Paid

Paid is a yes/no field in the TrackingNumbers table and is the control source
of a checkbox for each record on the form.

The query and the form list all of the unpaid orders with all of the
information. However, the checkbox cannot be checked. When clicked, it gets
focus, but does not update to a 'yes'.

I tried changing the query's joins to every possible combination with no luck,
and I double-checked that the data type matched for each linked field.

I can only get the checkboxes to cooperate if I simplify the query to one
table (TrackingNumbers), but I lose much of the information (FreightName,
OrderDate, CustomerName, City) from the form.

Any ideas of what I could have done wrong?

 

[J_Goddard via AccessMonster.com]

Hi -

You haven't really done anything wrong; the problem is that the query itself
is not updateable. Try using SELECT DISTINCTROW in the query SQL, end ensure
that all your joins are FK to PK, or to a field with a unique index. That
might make the query updateable.

John

 

[M Skabialka]

Open the query in query design, not SQL. Right click in the area which has
the tables and select properties. Under Recordset Type, select Dynaset
(Inconsistent Updates).

Try this with caution... test to see what it does in your tables. This
field should be in the 'one' side of your 'one-to-many' query, otherwise it
will update multiple records on the 'many' side.

Mich

 

[Linq Adams via AccessMonster.com]

Using DISTINCT is one of the things that causes the problem, according to
Allen Browne, who is almost always right!

Also, setting the Recordset Type to Dynaset (Inconsistent Updates) won't make
a difference if the underlying Record Source is read-only.

Allen gives a very good explanation of the various things that can cause this
problem as well as, I believe, some workarounds.

http://allenbrowne.com/ser-61.html

 

[M Skabialka]

Also, setting the Recordset Type to Dynaset (Inconsistent Updates) won't

make
a difference if the underlying Record Source is read-only.

This usually changes it out of the Read Only status. I use it frequently in
JOIN queries on forms and subforms.

There was no mention of DISTINCT in the original post, nor did I refer to
it.
I just offered a solution that has worked for me, withour criticising other
posts.
I have referenced Allen Browne's website frequently for my own programming
questions.

 

[milwhcky via AccessMonster.com]

Sorry I couldn't reply sooner, but I've been away from my desk...

My select query did not use DISTINCT or DISTINCTROW. Neither changing the
query to SELECT DISTINCTROW, nor changing the query's Recordset type to
Dynaset (Inconsistent Updates) led to a positive result.

Something did come to mind which may help someone steer me in the right
direction...

On my TrackingNumbers table, OrderID is one of two fields which make up the
primary key. The second field is LineNumber (autonumber), which is used
because many invoices have more than one tracking number. Therefore, the
same OrderID is listed more than once on the TrackingNumbers table. On the
Orders table, OrderID is the lone primary key field.

This was my first project using a table with a PK of more than one field, so
I wonder if this is the source of my hiccup.

Thanks to everyone who has attempted to help!

Hi -

You haven't really done anything wrong; the problem is that the query itself
is not updateable. Try using SELECT DISTINCTROW in the query SQL, end ensure
that all your joins are FK to PK, or to a field with a unique index. That
might make the query updateable.

John

I have a continuous form based on a multi-table query.

[quoted text clipped - 27 lines]

Any ideas of what I could have done wrong?