Checkbox will not check

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?

--
Message posted via http://www.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



milwhcky wrote:
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?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via http://www.accessmonster.com

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

"milwhcky via AccessMonster.com" u54692@uwe wrote in message
news:a4d12e7bfafe1@uwe...
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?

--
Message posted via http://www.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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

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.

"Linq Adams via AccessMonster.com" u28780@uwe wrote in message
news:a4d1e83acdeb0@uwe...
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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1

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!

J_Goddard wrote:
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?


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/201003/1