"Too Complex to be Evaluated" Error

[sawilliams]

I have a form that has a control button. The button's Event Procedure turns off warnings, saves the record, runs an append query and previews a report. It ran without errors yesterday. Today, I modified a table to add some new fields and then added those controls to the form (they were not added to the Query criteria or Report output -- just to the form). Now, when I run the Event Procedure, I get this error:

The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

The expression in question is below. Don't know how to proceed.

Private Sub cmdAppendRequest_No_Click()
On Error GoTo Err_cmdAppendRequest_No_Click

    Dim stDocName As String
    Dim stDocName2 As String
    
    stDocName = "aqryRequest_ID_Out"
    stDocName2 = "rptRequestOut"
    
    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    DoCmd.OpenReport stDocName2, acPreview
    
Exit_cmdAppendRequest_No_Click:
    Exit Sub

Err_cmdAppendRequest_No_Click:
    MsgBox Err.Description
    Resume Exit_cmdAppendRequest_No_Click
    
End Sub

 

[jrbarnett]

Cause could be the append query wanting to push data into the table with new fields set as mandatory but no values to go into it.

Ways of tracking this down include: can the individual components be run properly without problems -eg

DoCmd.OpenQuery "aqryRequest_ID_Out"

then separately
DoCmd.OpenReport "rptRequestOut", acPreview

John

 

[Remou]

What happens when you open the report or query from the database window?

 

[sawilliams]

All the components run fine from the database window. But I went thru the process of commenting out each of the DoCmd lines and then running the Event Procedure -- ONLY when I commented out the last line...

 DoCmd.OpenReport stDocName2, acPreview

...did I get the error.

 

[sawilliams]

sorry, that last line was supposed to say that the only time I don't get the error is when I comment out the last DoCmd. Sorry, I was a bit rushed.

 

[jrbarnett]

So if you run your append query from the database window, then immedately afterwards open your report in print preview mode (effectively doing the code run manually), does it cause the same error?

John

 

[sawilliams]

Yes, when I run the append query as just the query, it is fine -- then immediately open the report in print preview, and it runs fine.

 

[TheAceMan1]

How are ya sawilliams . . .

Try this:

[blue]On Error GoTo Err_cmdAppendRequest_No_Click
   Dim db As DAO.Database
   
   Set db = CurrentDb
   
   DoCmd.SetWarnings False
   DoCmd.RunCommand acCmdSaveRecord
   db.Execute "aqryRequest_ID_Out", dbFailOnError
   DoEvents
   DoCmd.OpenReport "rptRequestOut", acPreview
    
Exit_cmdAppendRequest_No_Click:
   DoCmd.SetWarnings True
   Set db = Nothing
   Exit Sub

Err_cmdAppendRequest_No_Click:
   MsgBox Err.Description
   Resume Exit_cmdAppendRequest_No_Click[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[sawilliams]

jbarnett, remou and AceMan1: Thanks for your valuable assistance. I tried all that you suggested but the problem persisted. I was in mid-response a week ago when we had a fire emergency in my building and we were evacuated. I am only just now picking up from that point.

Something that I had done since I first started working on this db caused this problem, so I started anew and built a new form and a new report and a new query and the problem went away. I wish I could report on what I did differently but from what I can tell I just replicated those same steps and now, it does what I intended it to do.

I apologize for the long delay in responding and thanking you all. I rely on Tek-Tips for excellent help. Thanks!

 

[vbajock]

It's a bug in Access. Sometimes,when a report is called thru code the underlying query of the report does not process correctly for who knows what reason.The solution is to not use an underlying query for the report, use a static table and populate the table first in code and then run the report.

 

[sawilliams]

vbajock:

Thanks for the tip. After I rebuilt the whole thing the problem disappeared but, alas, it has returned. I will take your suggestion and work with a static table. Many thanks.

 

[vbajock]

When I was doing a lot of Access report coding I dealt with it a lot so I got into this habit: just do your report development as you normally do, but when you are ready to code it in, save the query the report is based on as a make-table query and put it in a macro with Setwarnings = false. Then add a docmd.runmacro right before you call the report that runs the make table query, it will wipe out the existing table. If it is a multi-user application, it gets a lot trickier - you need to create a seperate database on the user's HD that you can use to dump the report tables too so they aren't walking all over each other, for these you have create the tables in the C:drive temp.mdb, link them to your main app frontend if they are all sharing a single one, and then do a empty table DELETE then APPEND thing.

 

[sawilliams]

Actually, I did something similar to what you are suggesting in another db I worked on. I had split the db into two parts with the data on the network and the "front end" on the local drive, with linked tables. I had a create table query that wrote to the front end so each of the several users could be in the same function simultaneously. So, your suggestion makes perfect sense. I got so hung up on the error that I didn't think it through clearly, so your help was very appreciated. I will try your suggestion of using the macro. Again, thanks.

 

[fneily]

vbajock - have you noticed that every time you run the make-table query, you're database bloats? When you delete a table and make a table the size of the database increases. It eventually means a Compact and Repair procedure. The make-table idea may be a bandaid but not the perfect solution.

 

[vbajock]

Just set the MDB to compact on close.

 

[vbajock]

Or better yet, get out your ADO and just code it.