 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / November 2006Calculated Controls on Reports
I have several calculated controls on a report. I have a command button on my form. Here is the VBA line as everyone is well aware of: DoCmd.OpenReport reportname:"report_name", view:=acPreview When I view my report the calculated fields have the #Error message. When I change the VBA line to the following (the view:=acNormal prints the report) the calculated controls work. DoCmd.OpenReport reportname:"report_name", view:=acNormal Why do the calculated controls work when I print the report and not when they are previewed. Please help and thank you for your time! Could you share anything about your calculations?  SignatureDuane Hookom Microsoft Access MVP > I have several calculated controls on a report. > [quoted text clipped - 11 lines] > Why do the calculated controls work when I print the report and not when they > are previewed. Please help and thank you for your time! Hi Duane, Here are my calculated controls =DSum("[FieldName]","TableName","[FieldCriteria]=" & chr$(34) & [Forms]! [frm_FrmName]![FieldCriteria]& chr$(34)) and =iif([FieldName]=0,Null,[FieldName]) Again when I use the argument acNormal in the DoCmd.OpenReport VBA line the figures are shown with no #Errors but when I use the acPreview the calculated controls are shown with errors. Thank you! Are you closing the form with code? What happens if your modify your control sources like: =DSum("[FieldName]","TableName") SignatureDuane Hookom Microsoft Access MVP > Hi Duane, > [quoted text clipped - 12 lines] > > Thank you! Hi Duane, I close the report using VBA obviously after I open it. It's a command button on my form. The DSum function worked without the criteria but the =iif conditional still had an #Error when I previewed it. I am stepping through my code line by line and I also have a break on the DoCmd.Close statement. Do I need to close my form before I view the report. That wouldn't make sense to me since my criteria in the DSum function needs a field input from the form to link to the table I have. What am I doing incorrectly? "I close the report using VBA obviously after I open it." Since I asked if you closed the form, I assume you meant "form" not "report". Remove all code that would close the form. SignatureDuane Hookom Microsoft Access MVP > Hi Duane, > [quoted text clipped - 8 lines] > > What am I doing incorrectly? Hi Duane, Actually I am stepping through my code line by line and I stop at the preview before the report and form are closed. The code is as follows: DoCmd.GotoRecord ObjectType:=acForm, ObjectName:="ObjectName", record: =acNewRec DoCmd.GotoRecord ObjectType:=acForm, ObjectName:="ObjectName", record:=acPrev DoCmd.OpenReport ObjectType:="ReportName", View:=acPreview *DoCmd.Close ObjectType:=acReport, ObjectName:="ReportName" DoCmd.Close ObjectType:=acForm, ObjectName:="FormName" *break I break here to view the report and I notice the #Error on the calculated controls but if I change the View argument of the DoCmd.OpenReport VBA line to acNormal then the calculated controls show the correct numerical values. I take all the close commands for both Reports and Forms out of the code and end up with the same result as before. Viewing it with #Errors and printing with the correct values. The second VBA line at the top is to go back to the record after it was appended by the user. As you know when a record is added it moves to the next record waiting for input. I hope this all makes sense and thank you for your help. It just frustrating. Why do you move to a new record and then back? Is this to save the record? If so, just use Me.Dirty = False SignatureDuane Hookom Microsoft Access MVP > Hi Duane, > [quoted text clipped - 23 lines] > > I hope this all makes sense and thank you for your help. It just frustrating. Hi Duane, My form's DataEntry property is set to True .... After the DoCmd.GotoRecord line I use the DoCmd.GotoRecord ,,acprev because after I add the new record the report is blank as it moves to the next record (blank in this case). Do i just insert the Me.Dirty = false after the appending and will it solve my calculated control problems. I am afraid this really doesn't solev that error. With the statement I had it brings up the current inputted record into the report but again with #Errors in the calculated controls. Try this code: DoCmd.RunCommand acCmdSaveRecord DoCmd.OpenReport ObjectType:="ReportName", View:=acPreview SignatureDuane Hookom Microsoft Access MVP > Hi Duane, > [quoted text clipped - 5 lines] > With the statement I had it brings up the current inputted record into the > report but again with #Errors in the calculated controls. Hi Duane, I have tried what you offered me and unfortunately it doesn't work. Again when I use acpreview in the DoCmd.OpenReport then I have the #Error message. My calculated control on the report is =iif([Myfieldname]=0,1,2). I use this for simplicity. [Myfieldname] is a single datatype. When I change the acNormal then it prints out the correct result. My textbox's name doesn't conflict with the actual field name. I also have a DSum function that works by the way. I am out of gas on this. >Try this code: >DoCmd.RunCommand acCmdSaveRecord [quoted text clipped - 5 lines] >> With the statement I had it brings up the current inputted record into the >> report but again with #Errors in the calculated controls. I would try open the report in preview and then open the debug window (press Ctrl+G) and enter: ?DSum("[FieldName]","TableName","[FieldCriteria]=" & chr$(34) & [Forms]! [frm_FrmName]![FieldCriteria]& chr$(34)) See if this produces an error. SignatureDuane Hookom Microsoft Access MVP > Hi Duane, > [quoted text clipped - 15 lines] > >> With the statement I had it brings up the current inputted record into the > >> report but again with #Errors in the calculated controls. Hi Duane, I tried on both the DSUM as well as the iif([Fieldname]=0,Null,[fieldname]) in the debug window and they worked fine by displaying the correct values. Any suggestions? Weird isn't. I appreciate your helps so far and continued help! I can't think of other tests. I would probably start re-building the report adding one calc'd control at a time and see when/if the new report breaks. SignatureDuane Hookom Microsoft Access MVP > Hi Duane, > I tried on both the DSUM as well as the iif([Fieldname]=0,Null,[fieldname]) > in the debug window and they worked fine by displaying the correct values. > Any suggestions? Weird isn't. I appreciate your helps so far and continued > help! Hi Duane, Here is what is happening just to make things clear. I have a form that a user inputs data that is linked to a table. I have a button on the form that saves the form and then proceeds to open a report through the acViewPreview mode. My calculated control's "=iif(...." displays an #Error however if I change the view to acViewNormal and then run the code then the calculated control works when it is printed from the command button: here is the code: DoCmd.Save ObjectType:=acForm, ObjectType:=Me.Name DoCmd.OpenReport ReportName:=strRptName, view:=acViewPreview (acViewNornal) The form is open with the user inputs while the code runs through the VBA code. When I just open the report through the Reports section then the calculated controls work. So now I know I have the correct syntax for my calculated controls. Do you know what is happening while I try to solve the sitaution as well. Thank you for all your help. There is no reason to save the form. The following would only have any relevance if the form was in design view which it isn't: DoCmd.Save ObjectType:=acForm, ObjectType:=Me.Name That is why I suggested the code to save the current record: DoCmd.RunCommand acCmdSaveRecord Do you try this code? SignatureDuane Hookom Microsoft Access MVP > Hi Duane, > [quoted text clipped - 15 lines] > controls. Do you know what is happening while I try to solve the sitaution as > well. Thank you for all your help. Hi Duane, I took out the DoCmd.Save and instead placed the DoCmd.RunCommand acCmdSave Record and recieved the following: Run-time error '2046' The command or action "SaveRecord' isn't available now. The cycle property of the form is set to Current Page so when I tab through the fields the information is not updated to the table. I have multiple pages on my form as an fyi... What seems to be the problem? Your not closing the form straight after opening the report in print preview are you? If you are then the form's control will not be available for the report's calculated control. I would suggest closing the form in the reports close event something like this: On Error Resume Next DoCmd.Close acForm, "FormName" Hope this helps Regards John > Hi Duane, > I took out the DoCmd.Save and instead placed the DoCmd.RunCommand [quoted text clipped - 10 lines] > > What seems to be the problem? Hi John, Thank you for your response. I am not closing the form after the report preview. I need to have the inputted data on the form appeneded to my table with the form still OPEN. How do I do that? Thank you for your help. Hi Chris Presuming the form is bound to the table in question, the Allow Additions=Yes and the RecordSet Type is not Snapshot, to save the current record I always use: DoCmd.RunCommand acCmdSaveRecord which has already been mentioned in this thread. Incidentally I've found that when stepping through code this line will throw the error you mentioned, I always put a breakpoint just after this line if I want to step through the code. As for the report error I've occasionally had similar problems. To get round it I would suggest you add a control (in the same section as your calculated control) bound to [Myfieldname] and call it txtMyfieldname, change your formula to read: IIf([txtMyfieldname]=0,1,2). If this works then change the new txtMyfieldname control's Visible property to false. If it doesn't work does the new txtMyfieldname control display the correct value? Hope this helps Regards John > Hi John, > Thank you for your response. I am not closing the form after the report > preview. I need to have the inputted data on the form appeneded to my > table > with the form still OPEN. How do I do that? > Thank you for your help. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.