What is this Errror Message? 2

[khwaja]

I am trying to create a report based on a query which is a collection of other smaller queries. I am constantly getting the following error message when I create a report. Could someone shed light on this and advise how could I resolve this? Error message is as under:

Quote
The wizard is unable to preview your report possibly because a table needed by your report is exclusively locked.

Your report will be opened in design view.

OK
Unquote

I have looked at all tables and their properties and none of these seem to be in use or linked to any other database.

Regards

AK

 

[RickSpr]

If you have the table open in Design View, that will cause it to be locked exclusively. Could that be it?

Or, if you have your Default Record Locking (Tools|Options, Advanced page for A97) set to "All records", and you have a datasheet view open on that table or a query that uses it, the table will be exclusively locked.

Is this table in the same database as the report, or do you just have a link to it? Rick Sprague

 

[khwaja]

Thanks for your response. No file is not linked to any other database or table. I have the option set to No lock. So it could not be the possiblility you raised. Any other insights?

Kind regards

AK

 

[RickSpr]

Strange. Try the following diagnostic experiments:
1. For each table which is used as a source for the report you're building, open it in design view, make a trivial change (such as adding a space to the end of a field description), and save it. Remember to include tables that are sources for any subreport(s). Any problems?
2. Run the report wizard and get the error. Save the report, then open it from the database window. Any problems?

You might be able to fix the problem by doing a repair. For Access 97, that's Tools|Database Utilities|Repair Database. For Access 2000, it's Tools|(Database Utilities?)|Compact and Repair.

If that doesn't fix it, also try creating a new database and importing all the objects into it. Remember to use the Advanced options in the Import dialog if you have any Import/Export specs or custom commandbars in the database. Rick Sprague

 

[khwaja]

Very thorough. I will definitely follow your advice but in the meanwhile I did some experimentation and thought might as well share with you before I do the diagnostics.

I have noticed that in a formula I am using in the underlying query, which I have reproduced below, if I restrict it to only 6 arguments, I can create a report. If it exceeds six, then I get the error I mentioned earlier on. Is there something wrong with the formula or is there a limitation in nesting like Excel does?

Due: IIf([TTotal]>=[Score1],"FY 04",IIf([TTotal]>=[Score2],"FY 05",IIf([TTotal]>=[Score3],"FY 06",IIf([TTotal]>=[Score4],"FY 07",IIf([TTotal]>=[Score5],"FY 08",IIf([TTotal]>=[Score6],"FY 09",IIf([TTotal]>=[Score7],"FY 10","FY 11&quot))))))

This formula helps assign financial year by testing the total of various weights originating from various queries based on cut off certain cut off denoted as Score*, fields maintained in a separate table for user input.

If I delete the last IIf part (Score7), I can get the report. I have also been getting 'Query is too complex' error message when I managed to run the report which is was created but opened in design mode with the error message.

I will perform the diagnostic once I get your advice. But I have tried creating a copy of database and I was careful to not to import table lest I might have some linkages coming along but this approach did not work either.

Cheers

 

[RickSpr]

I think you're on to something here. I overlooked that that "possibly because" in the error message. It probably comes up as a default message any time a query underlying a report won't run, for a reason that wasn't anticipated by the Access programmers. Most likely, the problem is the same one you're seeing when you open the report from the database window.

Your formula seems to have correct syntax, so I'm sure the problem is in the overall query complexity. The help file (for Access 97) describes this error as follows: "The query is too complex. Reduce the number of fields in the SELECT clause or the number of subqueries or tables in the join."

I'm not very clear on your explanation, but it sounds as if the Score* values are coming from other tables. If they come from separate tables, it makes sense that adding the Score7 term to the formula might bring in one more table, exceeding the limit of 32. That limit includes the tables in any subqueries, if you have them.

If that's not the problem, perhaps it's the number of fields in the SELECT, thought that seems unlikely (the limit is 255).

One way you could possibly simplify the query is to replace the big IIf() function with a custom function you write, that takes TTotal as a parameter and returns an FY string. The function can then determine the Score* values. If that lets you take the additional tables out of the query, it should get you under the limit. Rick Sprague

 

[khwaja]

Absolutely right approach. I wrote the following function but apparently due to my limited background, something is not right and I am receiving error when I run this. Could you kindly see if you can fix it. Looks like there is something wrong with the Due = FY ?? line.

Public Function Due(TTotal, _
Score1, Score2, Score3, Score4, Score5, Score6, Score7, Score8) As Integer

If (TTotal) >= Score1 Then
Due = "FY 04"
ElseIf (TTotal) >= Score2 Then
Due = "FY 05"
ElseIf (TTotal) >= Score3 Then
Due = "FY 06"
ElseIf (TTotal) >= Score4 Then
Due = "FY 07"
ElseIf (TTotal) >= Score5 Then
Due = "FY 08"
ElseIf (TTotal) >= Score6 Then
Due = "FY 09"
ElseIf (TTotal) >= Score7 Then
Due = "FY 10"
ElseIf (TTotal) >= Score8 Then
Due = "FY 11"

End If
End Function

Regards

 

[RickSpr]

Your function is syntactically correct. What kind of error are you getting?

I notice that TTotal and the Score arguments aren't given a type. As long as they're numeric, that shouldn't cause an error, I think. But it would be more efficient if you can type them.

Actually, I was thinking that maybe you could develop the Score* values within the function, if they aren't otherwise needed in the query, and that then you could remove some tables from the query. You'd want to cache the Score* values, though, so you don't have to look them up each time. Then you have the problem of knowing when to refresh the cached values. It's starting to sound more complicated than it's worth, if you can get it to work the way you're trying now. Rick Sprague

 

[khwaja]

Thanks. It is Run time error 13, tpe mismatch.

Cheers

 

[khwaja]

Thanks. I figured out. I should have shown the type as string and not integer. Much much appreciate your help and patience.

Kind regards

AK

 

[inventif]

Just a thought, you may prefer using SELECT CASE ... instead of too many IF statements, I think SELECT CASE ... gives better readability.

Cheers!