Wanting to have an editable check box on subform datasheet that doesnt change

[graeme34 via AccessMonster.com]

Hi I have a subform datasheet what has a query based control source.
What I want to do is add a checkbox to each record. I have added thsi in the
subform in design view, but each time I try to edit the check box all check
boxes change. Is there any way I can do this, I was thinking along the line
of adding the checkbox as an expression in the query but not too sure how to
format the expression to become a checkbox?
such as Checkbox: the rest of the expression I'm not to sure of or even if it
is allowed ?
Thank you,

 

[Roger Carlson]

The checkbox needs to be bound to a Yes/No field in your table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Allen Browne]

Add a yes/no field to your table. You can then show it as a check box in
your subform, and check whichever rows you wish.

The problem of all rows getting checked when you click one only occurs if
the box is unbound.

 

[graeme34 via AccessMonster.com]

Thank you Allen and Rodger
A couple of more questions if you dont mind?
Firstly let me explain what I am trying to do....I have a master form that is
used for data entry, frmReceiveInvoice......the underlying table has six
fields...ReceiptNumber (primary key(auto)), PurchaseOrderNumber,
SupplierInvoiceRef, DateInvoiceReceived, TotalNettAmount, CarriageAmount....
then I have a subform with a control source query the master and child are
linked through txtPurchaseOrderNumber and PurchaseOrderNumber (from the
select query), the select query has information of past receipted
orders/deliveries....why I required the check box was to check the records
that are on the suppliers Invoice as some suppliers deliveries/invoices have
a one to one relationship, .i.e if there have been multiple deliveries of a
certain order we may get an invoice for each delivery.
The first problem I'm having is as the SupplierInvoiceRef is a required field
when I click on one of the check boxes this is causing Access to try and save
the record in the Master form, is there any way of delaying this??
And next on the subform I have a LineTotal field is it possible to Sum only
the fields the have the checked box ticked, then palce this total onto a
textbox on the main form to allow a further calculation/comparison with
fields TotalNettAmount and CarriageAmount ??
I think thats about all (for now)
Graeme

Add a yes/no field to your table. You can then show it as a check box in
your subform, and check whichever rows you wish.

The problem of all rows getting checked when you click one only occurs if
the box is unbound.

Hi I have a subform datasheet what has a query based control source.
What I want to do is add a checkbox to each record. I have added thsi in

[quoted text clipped - 10 lines]

is allowed ?
Thank you,

 

[Allen Browne]

Answers in-line. (Question re-ordered.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

The first problem I'm having is as the SupplierInvoiceRef is a required
field
when I click on one of the check boxes this is causing Access to try and
save
the record in the Master form, is there any way of delaying this??

No. Access saves the record as soon as you leave it. You cannot leave the
main form record unsaved while you enter the subform or vice versa.

And next on the subform I have a LineTotal field is it possible to Sum
only
the fields the have the checked box ticked, then palce this total onto a
textbox on the main form to allow a further calculation/comparison with
fields TotalNettAmount and CarriageAmount ??

You can sum a field conditionally. For example, you could put this into the
Control Source of a text box in the subform's Form Footer section:
=Sum(IIf([MyYesNoField], [Amount],0)

You do *not* save the calculated field into the main form's table. You can
display the calculated total on the main form if you wish. For an example,
open the Northwind sample database that installs with Access, and open the
Orders form. The subform totals the order amounts, and the main form
displays the total, but does not store it in its table.

Firstly let me explain what I am trying to do....I have a master form that
is
used for data entry, frmReceiveInvoice......the underlying table has six
fields...ReceiptNumber (primary key(auto)), PurchaseOrderNumber,
SupplierInvoiceRef, DateInvoiceReceived, TotalNettAmount,
CarriageAmount....
then I have a subform with a control source query the master and child are
linked through txtPurchaseOrderNumber and PurchaseOrderNumber (from the
select query), the select query has information of past receipted
orders/deliveries....why I required the check box was to check the records
that are on the suppliers Invoice as some suppliers deliveries/invoices
have
a one to one relationship, .i.e if there have been multiple deliveries of
a
certain order we may get an invoice for each delivery.

As in Northwind, one order can have many line items, so you have an Order
table and an OrderDetail table. Similarly, one delivery can have many items,
so you have Delivery and DeliveryDetail tables. If you ordered 100 widgets
and the supplier only has 10 in stock, they might send 10 and backorder 90
for you, so there is a one-to-many relation between OrderDetail and
DeliveryDetail. With this approach, an order is completely delivered once
the sum of quantity in DeliveryDetail matches the quantity in OrderDetail
for all the OrderDetail rows in the order.

You may find that supplier invoices are different again. For example, a
supplier might invoice you monthly, so each invoice covers multiple
deliveries. At least, it is worth considering those scenarios when you try
to build the data structure that will handle all reasonable possibilities.

HTH.

 

[graeme34 via AccessMonster.com]

Hi Allen thanks for your help,
I have now set the subform visible property to false until the master record
is complete this stops the user clicking into the subform any getting the
error message.
I also had to alter the calculation that appeared in the query of the subform
and place it to an unbound text box as it was causing the query to become
unupdatable to grouping in the query and this prevented me from checking the
check box.
The final problem I have now is summing the line totals using your advice I
tried...
=Sum(IIf([LineTotalCorrect], [LineTotal],0)) in a text box on the subforms
footer, then placing a textbox on the main form and setting the control value
to
=subfrmReceiveInvoice.Form!txtsubfrmTotal where txtsubfrmTotal is the name of
the text box control in the subform footer
You are correct in all your assumptions concerning the relationship
1Order/MOrderDetails....1Despatch/MDespatchDetails etc.....although there is
no direct relationship between despatch details and order details this
relationship is made up from transitive dependencies between the parent
tables.
I think the problem lies in the immediate if statement because when I open
the subform in a new design window, the text box in the footer is showing
#Error..
can you see the problem,
Thanks for you help once more
Graeme.

Answers in-line. (Question re-ordered.)

The first problem I'm having is as the SupplierInvoiceRef is a required
field
when I click on one of the check boxes this is causing Access to try and
save
the record in the Master form, is there any way of delaying this??

No. Access saves the record as soon as you leave it. You cannot leave the
main form record unsaved while you enter the subform or vice versa.

And next on the subform I have a LineTotal field is it possible to Sum
only
the fields the have the checked box ticked, then palce this total onto a
textbox on the main form to allow a further calculation/comparison with
fields TotalNettAmount and CarriageAmount ??

You can sum a field conditionally. For example, you could put this into the
Control Source of a text box in the subform's Form Footer section:
=Sum(IIf([MyYesNoField], [Amount],0)

You do *not* save the calculated field into the main form's table. You can
display the calculated total on the main form if you wish. For an example,
open the Northwind sample database that installs with Access, and open the
Orders form. The subform totals the order amounts, and the main form
displays the total, but does not store it in its table.

Firstly let me explain what I am trying to do....I have a master form that
is

[quoted text clipped - 11 lines]

a
certain order we may get an invoice for each delivery.

As in Northwind, one order can have many line items, so you have an Order
table and an OrderDetail table. Similarly, one delivery can have many items,
so you have Delivery and DeliveryDetail tables. If you ordered 100 widgets
and the supplier only has 10 in stock, they might send 10 and backorder 90
for you, so there is a one-to-many relation between OrderDetail and
DeliveryDetail. With this approach, an order is completely delivered once
the sum of quantity in DeliveryDetail matches the quantity in OrderDetail
for all the OrderDetail rows in the order.

You may find that supplier invoices are different again. For example, a
supplier might invoice you monthly, so each invoice covers multiple
deliveries. At least, it is worth considering those scenarios when you try
to build the data structure that will handle all reasonable possibilities.

HTH.

 

[graeme34 via AccessMonster.com]

I'm almost there (I think), I have placed a hidden unbound textbox control on
the subform
set the control to
=IIf([LineTotalCorrect]=-1,[LineTotal],0)
this is working ..
now I'm trying to sum this txtConditionalAdd (name of hidden textbox) in the
subform footer
using
=Sum([txtConditionalAdd])
but this is displaying #error
once I have this working I'm hoping to display this total in another textbox
in the main form
=subfrmReceiveInvoice.Form!txtsubfrmTotal where txtsubfrmTotal is the name of
the text box control in the subform footer, not too sure yet if this will
work as subform footer not yet working...
I have also tried entering the formula =Sum(IIf([LineTotalCorrect]=-1,
[LineTotal],0))
but this also never worked

Hi Allen thanks for your help,
I have now set the subform visible property to false until the master record
is complete this stops the user clicking into the subform any getting the
error message.
I also had to alter the calculation that appeared in the query of the subform
and place it to an unbound text box as it was causing the query to become
unupdatable to grouping in the query and this prevented me from checking the
check box.
The final problem I have now is summing the line totals using your advice I
tried...
=Sum(IIf([LineTotalCorrect], [LineTotal],0)) in a text box on the subforms
footer, then placing a textbox on the main form and setting the control value
to
=subfrmReceiveInvoice.Form!txtsubfrmTotal where txtsubfrmTotal is the name of
the text box control in the subform footer
You are correct in all your assumptions concerning the relationship
1Order/MOrderDetails....1Despatch/MDespatchDetails etc.....although there is
no direct relationship between despatch details and order details this
relationship is made up from transitive dependencies between the parent
tables.
I think the problem lies in the immediate if statement because when I open
the subform in a new design window, the text box in the footer is showing
#Error..
can you see the problem,
Thanks for you help once more
Graeme.

Answers in-line. (Question re-ordered.)

[quoted text clipped - 44 lines]

HTH.

 

[Allen Browne]

Summing the calculated control won't work.

Summing the IIf() expression should work. If that is still the stumbling
block, an alternative is to create a query as the source for the subform,
and put the calculated field in the query by typing an expression like this
into a fresh column in the Field row in query design:
LT: IIf([LineTotalCorrect], [LineTotal],0)
Since LT is now a field in the subform's RecordSource, on the form you can
use:
=Sum([LT])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I'm almost there (I think), I have placed a hidden unbound textbox control
on
the subform
set the control to
=IIf([LineTotalCorrect]=-1,[LineTotal],0)
this is working ..
now I'm trying to sum this txtConditionalAdd (name of hidden textbox) in
the
subform footer
using
=Sum([txtConditionalAdd])
but this is displaying #error
once I have this working I'm hoping to display this total in another
textbox
in the main form
=subfrmReceiveInvoice.Form!txtsubfrmTotal where txtsubfrmTotal is the name
of
the text box control in the subform footer, not too sure yet if this will
work as subform footer not yet working...
I have also tried entering the formula =Sum(IIf([LineTotalCorrect]=-1,
[LineTotal],0))
but this also never worked

Hi Allen thanks for your help,
I have now set the subform visible property to false until the master
record
is complete this stops the user clicking into the subform any getting the
error message.
I also had to alter the calculation that appeared in the query of the
subform
and place it to an unbound text box as it was causing the query to become
unupdatable to grouping in the query and this prevented me from checking
the
check box.
The final problem I have now is summing the line totals using your advice
I
tried...
=Sum(IIf([LineTotalCorrect], [LineTotal],0)) in a text box on the
subforms
footer, then placing a textbox on the main form and setting the control
value
to
=subfrmReceiveInvoice.Form!txtsubfrmTotal where txtsubfrmTotal is the name
of
the text box control in the subform footer
You are correct in all your assumptions concerning the relationship
1Order/MOrderDetails....1Despatch/MDespatchDetails etc.....although there
is
no direct relationship between despatch details and order details this
relationship is made up from transitive dependencies between the parent
tables.
I think the problem lies in the immediate if statement because when I open
the subform in a new design window, the text box in the footer is showing
#Error..
can you see the problem,
Thanks for you help once more
Graeme.

Answers in-line. (Question re-ordered.)

 

[graeme34 via AccessMonster.com]

Thank you Allen!!!!
Placing the expression in the query worked just fine.....success (thanks)
So access doesnt allow summing of calcualted controls I'll have to remember
that,
but you say summing the IIF expression should have worked ??
Must have been an error on my part, still coming to terms with access
Thank you any way Allen for your time and help, much appreciated.....

Summing the calculated control won't work.

Summing the IIf() expression should work. If that is still the stumbling
block, an alternative is to create a query as the source for the subform,
and put the calculated field in the query by typing an expression like this
into a fresh column in the Field row in query design:
LT: IIf([LineTotalCorrect], [LineTotal],0)
Since LT is now a field in the subform's RecordSource, on the form you can
use:
=Sum([LT])

I'm almost there (I think), I have placed a hidden unbound textbox control
on

[quoted text clipped - 54 lines]