Prev: numerical summation to passed from sub to main form
Next: Subform New Record
From: milwhcky via AccessMonster.com on 10 Mar 2010 16:29
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

From: J_Goddard via AccessMonster.com on 10 Mar 2010 16:52
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

From: M Skabialka on 10 Mar 2010 17:02
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(a)uwe> wrote in message
news:a4d12e7bfafe1(a)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
>


From: Linq Adams via AccessMonster.com on 10 Mar 2010 17:52
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/Forums.aspx/access-forms/201003/1

From: M Skabialka on 11 Mar 2010 08:57
> 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(a)uwe> wrote in message
news:a4d1e83acdeb0(a)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/Forums.aspx/access-forms/201003/1
>


 |  Next  |  Last
Pages: 1 2
Prev: numerical summation to passed from sub to main form
Next: Subform New Record