UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Changing Controlsource Onopen Of Report......error 2427, Any Version    
 
   
jimbofoxman
post Jul 31 2020, 09:34 AM
Post#1



Posts: 435
Joined: 4-April 08



I have a report I am trying to run that when it prints, I want it to change a couple Formulas used in a couple fields depending on the value of the ContractVerNo field.

So in the Form, I basically did similar to below and had no issue. Now when I try to put it on a report it fails with RTE 2427 on the first line: RTE 2427 "You entered an expression that has no value"

CODE
Private Sub Report_Open(Cancel As Integer)

    If Me.ContractVerNo < "900" Then
        Me.RealTax.ControlSource = "=(Nz([43UT],0)+Nz([60],0)+Nz([80]))*0.06 "
        Me.Ctl43UM.ControlSource = "=Nz([Total],0)-Nz([43UT],0)-Nz([43UTE],0)-Nz([50M],0)-Nz([60],0)-Nz([60E],0)-Nz([60I],0)-Nz([70],0)-Nz([80],0)-Nz([90],0)-Nz([90M],0)-Nz([RealTax],0)"
    Else
        Me.Real43STM.ControlSource = "=[Ttl43STM]/1.06"
        Me.RealTax.ControlSource = "=(Nz([43ST],0)+Nz([Real43STM],0)+Nz([60],0)+Nz([80]))*0.06"
        Me.Ttl43STM.ControlSource = "=Nz([Total],0)-Nz([95],0)-Nz([80],0)-Nz([60E],0)-Nz([60],0)-Nz([43ST],0)-Nz([43E],0)-[Tax]"
        Me.Tax.ControlSource = "=(Nz([43ST],0)+Nz([43STM],0)+Nz([60],0)+Nz([80]))*0.06"
    End If
    
End Sub


It errors on the If statement. At first I thought it was because I didn't have the control on the report. So I added it, but still the same error. I can see the value, it's 3.

It didn't like doing it ONLOAD either, but a different error.

Any thoughts? Some stupid Syntax thing again?

Thanks!
Go to the top of the page
 
Bob007
post Jul 31 2020, 11:20 AM
Post#2



Posts: 11
Joined: 16-May 11



Try your code in the On Print event of whichever section (Header,Footer,Detail etc) that holds your text box.
This post has been edited by Bob007: Jul 31 2020, 11:22 AM
Go to the top of the page
 
jimbofoxman
post Jul 31 2020, 12:01 PM
Post#3



Posts: 435
Joined: 4-April 08



So I put it in the ON PRINT event for the detail section. Now I get RTE 2191; You can't set the Control Source property in Print Preview or after printing has started. It gets past the IF statement and fails on the next line.

Print Preview gives you the error. Report View doesn't error out, but it also doesn't apply the changes to the control source.

The other thought I had, was when they hit the submit button on the form it just copies the calculation values into the appropriate field. Which goes against normalization of data, but it's only temporary until the invoice is generated and then the data will get deleted for the billing. So it's never storing it permanently.
Go to the top of the page
 
projecttoday
post Jul 31 2020, 12:04 PM
Post#4


UtterAccess VIP
Posts: 12,493
Joined: 10-February 04
From: South Charleston, WV


You're using the open event. Is each run of the report supposed to apply to only 1 ContractVerNo?

--------------------
Robert Crouser
Go to the top of the page
 
jimbofoxman
post Jul 31 2020, 12:17 PM
Post#5



Posts: 435
Joined: 4-April 08



Essentially only one ContractVerNo will be printed out at a time. When they finish filling out the billing slip form, then they can print it out to attach to the paper copy they file away. Then someone uses the data on the form to create an invoice in Quickbooks. Eventually we hope to make it import into Quickbooks.

The next time someone could be doing one with a ContractVerNo of 900 so the formulas need to be different. Basically a contract with a version number of 900 (or higher) gets taxes figured differently that below 900. Below 900 is installed, 900+ is direct sale, no installation.

We had the billing slip in Excel, but some changes to the way taxes and margin were made and I couldn't trick Excel into doing some calculation. Kept getting a circular reference in Excel. So doing it in Access let me trick the calculations.
This post has been edited by jimbofoxman: Jul 31 2020, 12:26 PM
Go to the top of the page
 
projecttoday
post Jul 31 2020, 12:32 PM
Post#6


UtterAccess VIP
Posts: 12,493
Joined: 10-February 04
From: South Charleston, WV


And ContractVerNo is a text field? Remember, "1000" is lower than "900". 60 and 80 are fields? Some of your Nz's don't have 2nd operands.
What happens if you remove all the statements except this one?
Me.RealTax.ControlSource = "=(Nz([43UT],0)+Nz([60],0)+Nz([80],0))*0.06 "
?
(Note the corrected Nz.)

--------------------
Robert Crouser
Go to the top of the page
 
jimbofoxman
post Jul 31 2020, 12:49 PM
Post#7



Posts: 435
Joined: 4-April 08



ContractVerNo is a Long Integer in the table. The "" around the 900 was just a test that I forgot to remove. But the error is still the same without the "". Yes 60 and 80 are fields, they are codes we use within Quickbooks. I know the form actually uses Ctl60, Ctl80 instead of just 60 or 80.

Good catch on the missing operands. I fixed those and then made the Report_Open code to be only;

CODE
Me.RealTax.ControlSource = "=(Nz([43UT],0)+Nz([60],0)+Nz([80],0))*0.06 "


It runs fine in Report_Open. I also tried just that line in the Detail_Print event and I get the RTE 2191 error.

So it definitely runs in the Report_Open event, it just doesn't like ContractVerNo line.
Go to the top of the page
 
jimbofoxman
post Jul 31 2020, 03:07 PM
Post#8



Posts: 435
Joined: 4-April 08



Ok, I think I've found a solution.

In the control source for the individual calculation fields, I did an Iif statement.

CODE
=IIf([ContractVerNo]<900,Nz([Total],0)-Nz([43UT],0)-Nz([43UTE],0)-Nz([50M],0)-Nz([60],0)-Nz([60E],0)-Nz([60I],0)-Nz([70],0)-Nz([80],0)-Nz([90],0)-Nz([90M],0)-Nz([RealTax],0),0)


It seems to be working fine. I have two different billings, one <900 and one 900 and they both seem to show up correctly.
Go to the top of the page
 
projecttoday
post Jul 31 2020, 05:40 PM
Post#9


UtterAccess VIP
Posts: 12,493
Joined: 10-February 04
From: South Charleston, WV


Good job. (Iif was the next thing I was going to suggest.)

--------------------
Robert Crouser
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    6th August 2020 - 05:22 AM