Report design question

[Robert Robinson]

I am creating a detailed Sales Journal. I have a "Net Sale" field from a
query. I also include payment terms (cash, check, credit in check boxes) in
the same query. On my report, I would like to like each line to display the
"Net Sale" in the respective "Cash", "Check", or "Credit" columns based on
which box is checked. For example, if an individual sale line was a cash sale
(denoted by a check on the cash check box, I want the net sale amount for
that line to appear in the cash column. I think the VBA code that I would
want to use is something like:

If Me.Cash (the checkbox field for cash) = True Then
Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale
field)
Else
Me.Cashbal = " "

The first question is if this is the proper use of code. The second question
is where should such code be placed. Since the report fields themselves do
not accept event code (greyed out), it seems that the code should be placed
at the form level. But where? On Open? On Activate? Please advise.

 

[Duane Hookom]

I am not sure you need any code if the payment terms don't change frequently.
You could create your text boxes for each column and set the control sources
to:
=IIf([PmtTerms]="cash", [Net Sale], Null)
Create additional similar text boxes for other terms.

I would probably add a field to the payment terms lookup table that
identifies the horizontal position of the column in the report. You could
then add the table to your report's record source and use code in the on
format to set the Left property of you [Net Sale] text box.

 

[Robert Robinson]

Wow. That was fast.
I did not even think about creating an expression in the control source. My
check boxes are individual (yep, I shoulda bound them together in 1
control...rookie mistake...will do that next time) so each will have a
True/False value. Thank you for your help.
--
Robert Robinson

I am not sure you need any code if the payment terms don't change frequently.
You could create your text boxes for each column and set the control sources
to:
=IIf([PmtTerms]="cash", [Net Sale], Null)
Create additional similar text boxes for other terms.

I would probably add a field to the payment terms lookup table that
identifies the horizontal position of the column in the report. You could
then add the table to your report's record source and use code in the on
format to set the Left property of you [Net Sale] text box.
--
Duane
Microsoft Access MVP

I am creating a detailed Sales Journal. I have a "Net Sale" field from a
query. I also include payment terms (cash, check, credit in check boxes) in
the same query. On my report, I would like to like each line to display the
"Net Sale" in the respective "Cash", "Check", or "Credit" columns based on
which box is checked. For example, if an individual sale line was a cash sale
(denoted by a check on the cash check box, I want the net sale amount for
that line to appear in the cash column. I think the VBA code that I would
want to use is something like:

If Me.Cash (the checkbox field for cash) = True Then
Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale
field)
Else
Me.Cashbal = " "

The first question is if this is the proper use of code. The second question
is where should such code be placed. Since the report fields themselves do
not accept event code (greyed out), it seems that the code should be placed
at the form level. But where? On Open? On Activate? Please advise.

 

[Robert Robinson]

OK.
To compensate for the setup of my fields, I created the following formula
for the control box of my "Cashbal" field:
=IIf([Cash]="Yes",[Net Sale],0)
I am getting the 0 just fine, but I should see the net sale amount in the
cashbal field for a couple of lines. The first part of my statement is wrong,
but I do not know how.

--
Robert Robinson

I am not sure you need any code if the payment terms don't change frequently.
You could create your text boxes for each column and set the control sources
to:
=IIf([PmtTerms]="cash", [Net Sale], Null)
Create additional similar text boxes for other terms.

I would probably add a field to the payment terms lookup table that
identifies the horizontal position of the column in the report. You could
then add the table to your report's record source and use code in the on
format to set the Left property of you [Net Sale] text box.
--
Duane
Microsoft Access MVP

I am creating a detailed Sales Journal. I have a "Net Sale" field from a
query. I also include payment terms (cash, check, credit in check boxes) in
the same query. On my report, I would like to like each line to display the
"Net Sale" in the respective "Cash", "Check", or "Credit" columns based on
which box is checked. For example, if an individual sale line was a cash sale
(denoted by a check on the cash check box, I want the net sale amount for
that line to appear in the cash column. I think the VBA code that I would
want to use is something like:

If Me.Cash (the checkbox field for cash) = True Then
Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale
field)
Else
Me.Cashbal = " "

The first question is if this is the proper use of code. The second question
is where should such code be placed. Since the report fields themselves do
not accept event code (greyed out), it seems that the code should be placed
at the form level. But where? On Open? On Activate? Please advise.

 

[Robert Robinson]

Nevermind.
I found my mistake. My condition had quotes around it. Revised it thus:
=IIf([Cash]=-1,[Net Sale],0)
Works great now.
Thanks again.
--
Robert Robinson

I am not sure you need any code if the payment terms don't change frequently.
You could create your text boxes for each column and set the control sources
to:
=IIf([PmtTerms]="cash", [Net Sale], Null)
Create additional similar text boxes for other terms.

I would probably add a field to the payment terms lookup table that
identifies the horizontal position of the column in the report. You could
then add the table to your report's record source and use code in the on
format to set the Left property of you [Net Sale] text box.
--
Duane
Microsoft Access MVP

I am creating a detailed Sales Journal. I have a "Net Sale" field from a
query. I also include payment terms (cash, check, credit in check boxes) in
the same query. On my report, I would like to like each line to display the
"Net Sale" in the respective "Cash", "Check", or "Credit" columns based on
which box is checked. For example, if an individual sale line was a cash sale
(denoted by a check on the cash check box, I want the net sale amount for
that line to appear in the cash column. I think the VBA code that I would
want to use is something like:

If Me.Cash (the checkbox field for cash) = True Then
Me.Cashbal (the unbound cash report field) = Me.Netsale (the bound sale
field)
Else
Me.Cashbal = " "

The first question is if this is the proper use of code. The second question
is where should such code be placed. Since the report fields themselves do
not accept event code (greyed out), it seems that the code should be placed
at the form level. But where? On Open? On Activate? Please advise.