Replies

Hi RetiredGeek,

That’s an interesting way of handling menus which would probably have meant that the problem couldn’t have reared itself in the first place.

The application I was referring to already uses code to create some popup menus from scratch, so it wouldn’t be a big step to doing the lot. Hmm. I will ponder.

Thanks very much for the suggestions. I would still love to know the root cause of the problem I had, but switching to function calls from the menu was a good workaround.

Thanks RetiredGeek, I’d considered that as a possibility but this is intended to solve a problem on one particular PC and I was hoping not to make any changes to the application.

The reason I’m trying to do this is that just one computer, of a number that run this database, seems to have developed a problem whereby it has an issue with the existing menu which, on that computer alone, keeps disappearing. I’ve tried recreating the menu by hand and from scratch and that seems to work, but so far I’ve only created a much simpler version of the menu with fewer options. So I was just experimenting with a way of recreating the full menu to see whether, if created anew on that machine, that would also stay put.

If I knew that it would, then it would be worth the effort re-creating it manually. But as I don’t it would be very helpful to have a way of easily re-running the creation while I experiment and see if perhaps the problem can be pinned down to any particular part of the menu.

EDIT: I guess I could get away with having a single generic function, along the lines of

Code:

Public Function MenuOpenForm(ByVal FormName As String)
    DoCmd.OpenForm FormName, acNormal
End Function

and making the OnAction property equal to

Code:

=MenuOpenForm(“dlgFormName”)

By the way, I’ve noticed that the same problem affects any reports run straight from the menu, but the same kind of solution would work there too.

Thanks, Hans.

That would work brilliantly well for a report. If I wanted to do the same thing in a form, there wouldn’t be any way of doing it while at the same time allowing the formatted text box to be inputable, I’d have to have a separate input box?

Regards,

Simon

Edited by HansV to display [red] instead of red text.

I’ve got a problem that’s similar enough that it didn’t seem worth starting a new thread.

I have a field which can contain positive or negative numbers. The positive numbers may have a decimal part but may be whole numbers. I would like to use the General Number convention to display the positive values i.e. for decimal values, a decimal point and one or more digits after it, but for integers I would just like to display the integer part with no decimal point. However for negative numbers, I would like to use a specific format (“0.00[red]”). (sorry, that format should have the word ‘red’ in square brackets as part of it, but I don’t know how to make that appear correctly, it just colours the subsequent text red instead)

If there any way I can combine the two using the Format property, or would I need to do something different.

Thanks,

Simon

The problem relates to missing data – or an incorrect join. The queries underlying the crosstabs (QueryFermiM1com and QueryFermiM2com) link a table (either COM1 or COM2) to table FERMI1 using the field CODF.

In the case of table COM1 no records dated after April contain any value in the CODF field. In the case of table COM2, no records dated after March contain a value in this field. Because the two queries use inner joins, the only records being returned will be those with values in the CODF field which are also in the FERMI1 table, so no data will be generated for May at all, and only some (from COM1 only) for April.

The problem is unrelated to the fact that you’re using a union query – any statement along the lines of SELECT A, B, C AS D FROM XYZ WHERE D = “” will result in the user being prompted for a value for “D”. The reason is that D doesn’t exist as a field in source tables to the query itself, it’s merely a label applied to a field or expression on execution. In the same way, you can’t use GROUP BY D either, you have to GROUP BY whatever it was you’d renamed as “D”.

As any WHERE, GROUP BY etc clause operates on the source data before the query begins its output, these labels don’t really exist at that point.

What you can do, of course, is something like SELECT “A” AS C, “B” AS D, C & D AS E FROM XYZ, because the expression is then working on the output values, not trying to apply it to the input values.

I think it might be a file permissions problem.

When opening an Access database, if no other user currently has that database open, Access creates a .ldb file in the same folder as the mdb/mde file. If permission to create files in this folder resides with Administrators but not PowerUsers, it might explain why the latter can’t be the first to open the database.

Once this .ldb file has been created, the need to file creation rights disappears as all subsequent database users merely need rights to modify this file.

Of course, when the last person using the database closes it, the .ldb file will be deleted and thus the database becomes readonly again to all but the Administrators.

I think it might be a file permissions problem.

When opening an Access database, if no other user currently has that database open, Access creates a .ldb file in the same folder as the mdb/mde file. If permission to create files in this folder resides with Administrators but not PowerUsers, it might explain why the latter can’t be the first to open the database.

Once this .ldb file has been created, the need to file creation rights disappears as all subsequent database users merely need rights to modify this file.

Of course, when the last person using the database closes it, the .ldb file will be deleted and thus the database becomes readonly again to all but the Administrators.

There is indeed a further problem, though, if you’re intending to include this code (or the call to this code) in every report. If this is being used only in a “master” report then Charlotte’s code does the business.

As I suggested yesterday (but at that point hadn’t tried it), report A errors trying to close report A, so the added “If” statement will avoid that eventuality. But when report A closes report B, report B’s own OnClose event will kick in and the code that calls will hit the error condition not only when it tries to close itself, but also when it tries to close report A. If this error isn’t fatal then when B closes C, C’s OnClose event starts. Assuming there are no further reports, C will try to close A (fails), B (fails) and will skip trying to close itself. It then closes for real, and control returns to B’s OnClose. This has finished its loop (C being the last report) so it too terminates and the report B closes. Report A has only just started its loop, so it will still try to close B and C, but this will fail with a different error as the only report open will be A – so by this time, Reports(i) is invalid for any value of i > 0.

The easiest solution might be to add error handling to CloseAllReports and in it, check first whether the error is either 2585 (This action can’t be carried out while processing a form or report event.) or 2457 (The number you used to refer to the report is invalid). When it is, just resume processing, otherwise, report the error.

There is indeed a further problem, though, if you’re intending to include this code (or the call to this code) in every report. If this is being used only in a “master” report then Charlotte’s code does the business.

As I suggested yesterday (but at that point hadn’t tried it), report A errors trying to close report A, so the added “If” statement will avoid that eventuality. But when report A closes report B, report B’s own OnClose event will kick in and the code that calls will hit the error condition not only when it tries to close itself, but also when it tries to close report A. If this error isn’t fatal then when B closes C, C’s OnClose event starts. Assuming there are no further reports, C will try to close A (fails), B (fails) and will skip trying to close itself. It then closes for real, and control returns to B’s OnClose. This has finished its loop (C being the last report) so it too terminates and the report B closes. Report A has only just started its loop, so it will still try to close B and C, but this will fail with a different error as the only report open will be A – so by this time, Reports(i) is invalid for any value of i > 0.

The easiest solution might be to add error handling to CloseAllReports and in it, check first whether the error is either 2585 (This action can’t be carried out while processing a form or report event.) or 2457 (The number you used to refer to the report is invalid). When it is, just resume processing, otherwise, report the error.

I think your problem might be that Access doesn’t like you trying again to close the current report when it’s already proccessing the OnClose event for it.

You could try something like If Reports(i).Name Me.Name Then DoCmd.Close acReport, Reports(i).Name

On a similar basis, you might want to watch out for problems if you include the code in each report’s OnClose event. When you close report A and it tries to close report B, report B’s OnClose event will fire and that will start trying to work its way through the open reports too.

I think your problem might be that Access doesn’t like you trying again to close the current report when it’s already proccessing the OnClose event for it.

You could try something like If Reports(i).Name Me.Name Then DoCmd.Close acReport, Reports(i).Name

On a similar basis, you might want to watch out for problems if you include the code in each report’s OnClose event. When you close report A and it tries to close report B, report B’s OnClose event will fire and that will start trying to work its way through the open reports too.

Hans, I’ve stripped the whole DB down, and now, of course, that part of the program works perfectly!

I shall try again…

Hans, I’ve stripped the whole DB down, and now, of course, that part of the program works perfectly!

I shall try again…

Thanks Hans, I’ll have a go at stripping it down and posting it here. May take me an evening or two to get it done.

Simon.