Rename forms error

[gg]

I am tying to change the names of two forms I am using the onUpdate of a
comboBox named RaceNoBox on the form named 0MainMenu to start the code.

If DLookup("pursuitFlag", "00RaceSchedule", "idRaceNo=form.racenoBox") = -1
Then
call Module5

Module5 contains:
DoCmd.Close acForm, "0MainMenu"
DoCmd.Rename "0MainMenu_Reg", acForm, "0MainMenu" (This works)

DoCmd.Rename "0MainMenu", acForm, "0MainMenu-PUR" (Get error 2007 msg)
DoCmd.OpenForm "0 Main Menu"

The first action works. 00mainMenu becomes 00MainMenu-Reg Then, I get a
run-time error message "run-time error 2007-You already have an open data
base object named 0MainMenu" and procedure halts. i

I'm using Access 2000 with W2000. Any help much appreciated.

 

[Clifford Bass]

Hi,

It may be due to that fact that you are running code in the original
form and while the first rename seems to be working, it may be getting
postponed until you completely exit out of the code. So when the second
rename is executed, 0MainMenu still exists. Is Module5 a subroutine inside a
separate regular module? I am presuming so. So what you have is this type
of scenario:

Inside code inside form 0MainMenu, while it is open
Inside Module5
Rename 0MainMenu to 0MainMenu_Reg
Rename 0MainMenu-PUR to 0MainMenu
Exit from Module5
Exit from code inside 0MainMenu
0MainMenu closed

Only at this point is the code inside 0MainMenu completed and the form
completely freed up/closed.

Check to see if this indeed the cause of the problem by running the
code, but not from within the form: Open up 0MainMenu and then open up the
VBA editor to display Module5. Place your cursor inside Module5 and press
<F5>. Does it work without any errors?

All that aside, renaming the main menu form name is probably not the
best of methods. It would probably be better to use a setting somewhere,
such as in a table, that is then used to determine which menu to use. So in
the place where you would just open the main menu:

DoCmd.OpenForm "0MainMenu"

you would instead have something like:

If DLookup("MenuToUse", "tblSettings") = "Reg" Then
DoCmd.OpenForm "0MainMenu_Reg"
Else
DoCmd.OpenForm "0MainMenu-PUR"
End If

And within the On Update event of the control, you simply would close
the current form and open the other form. Oh, and there also, you could set
the menu to use inside the settings table.

Hope that helps,

Clifford Bass

 

[gg]

Hi and thanks for quick reply. I ran the code in the module using F5 and all
runs well without any errors, so problem is with the 0MainMenu not being
freed up.

I am not a pro, so didn't completely understand your first two paragraphs.
Is there a way of making the 0MainMenu formt ruly freed/closed or were you
just describing the situation in general terms.?

I didn't use the proposed the table indicator as you described it because
there are places in the program that call for opening and closing 0MainMenu
and using or inserting values into 0MainMenu (not a good idea, but I was very
new at this when created). I was trying to avoid a lot of "IF" statements.

Is there a code statement that will truly free up 0MainMenu?

Thanks again George

 

[Clifford Bass]

Hi George,

The gist of the first two paragraphs, which you verified to be
accurate, is that when you are running code in a form, even if it closes the
form itself, the code runs through completion. And only on completion is
the form totally closed/freed up.

So, what to do. I had an idea about using an invisible form to close
out the main form, do the renames and the open the "new" main form again. It
ran into the same problem. It almose looks like the rename is somehow sort
of "opening" the form. But that does not seem likely.

It looks to me like you are probably going to have to abandon the
renaming tactic. Unless someone else has a solution. There is nothing wrong
with having with a bunch of If statements.

Good Luck!

Clifford Bass

 

[gg]

Sometimes one is the wndshield and one is the bug.

Thanks for all the time you put into this-I much appreciate it. I will try
this again when I get Ac2007.

George

 

[Clifford Bass]

Hi George,

You are welcome. I was doing my testing with Access 2007, so do not
expect it to change. Good luck with figuring out something!

Clifford Bass