Error converting form's macros to VB

[Petrol]

My application opens with a Switchboard based on the one created by Access's standard Switchboard manager - that is, a form driven by tbl SwitchboardItems, with an OnOpen macro to initialise its variables and a command button which invokes an OnClick macro to initiate the required action.

This has worked fine, but I now want to convert the macros to code procedures to give me a little more flexibility. When I click "Convert Form's Macros to Visual Basic" on the Design tab, it does so and displays "Conversion Finished", but then when the form is opened it gives the On Open error "Procedure declaration does not match the description of event or procedure having the same name" (screenshot attached).
Even if I delete the entire contents of the resultant Sub, it still gets the same error. If I delete the OnOpen macro before converting the macros to VB, it get the same error at the On Load, OnCurrent, OnClose stages.

I have converted macros to VB code in other forms successfully.

[PhilOfWalton]

Hi Petrol

Open the form in Design View and see if the code compiles.

Phil

[Petrol]

No it doesn't, the message ("Microsoft Access was unable to create the ACCDE ...") implying too many table IDs.
However, note that I said that the same error occurs even when I take all the compiled code out of the sub, leaving just the following two statements:

Private Sub Form_Open(Cancel As Integer)
 
End Sub

As shown in the post on the original screenshot, the error on opening is "Procedure declaration does not match the description of event or procedure having the same name" However the procedure declaration,
Private Sub Form_Open(Cancel As Integer)
looks fine to me.

[PhilOfWalton]

Well obviously the error message has nothing to do with compiling. That error message is related to converting an Accdb to an Accde.

Are you sure you just tried a compile in the VBA--->Debug--->Compile your project name?

Phil

[Petrol]

Well no, Phil, I did it by creating an accde file, which I understood compiles the code. However when I do it by VBA-Debug-Compile I get the same message as before: "Compile Error: Procedure declaration does not match description of the event or procedure having he same name".

[Petrol]

I mean "the same name"

[PhilOfWalton]

Right, two processes are completely different. You can't make an Accde unless the Accdb compiles without errors. So always compile first.

Don't forget you can't modify forms & reports in an Accde, so that should only be created when you want to distribute your DB.

Does the compiler highlight the error line.

I'm off sailing for the rest of the day, so probably won't get back to you.

Phil

[Petrol]

Looks like it should be a nice day for it, too. Enjoy!

Yes I understand that. I've released the DB at the beginning of this year to selected other users in our organisation, though I'm still adding enhancements. All releases to others have been accde, which have always compiles without trouble - and still do, except when I convert the macros to VBA. It's clearly that process which is going wrong.

And no, it doesn't highlight any error line. It doesn't even open the code module. When I click "Convert Form's Macros to Visual Basic" on the Design tab, it does so and displays "Conversion Finished", but then when the form is opened it gives the On Open error "Procedure declaration does not match the description of event or procedure having the same name" (screenshot attached to OP).

Even if I delete the entire contents of the resultant Sub, it still gets the same error. If I delete the OnOpen macro before converting the macros to VB, it get the same error at the On Load, OnCurrent, OnClose stages.

[PhilOfWalton]

Sitting here on the boat, having a G & T so, probably you aren't going to get much sense.

So you've done the conversion. Forget opening any forms. Can you open any forms in design view and can you open any modules?

Phil

[Petrol]

Still on the boat at ... what, 10pm?

Yep. No probs with any other forms, reports, modules etc. Just the switchboard. I'm gradually chopping little bits out of its macros to see what's causing ghe problem ... but as I said, if I delete all the contents of the resultant VBA code after conversion it still claims not to like the procedure name. Must be a bug in Access, surely

[PhilOfWalton]

Hey, aren't I allowed a day off. Come home tomorrow.

It's worth checking the names of all the fields on the form, and see if the corresponding subroutine has the correct parameters.

For example

Private Sub Form_Open (Cancel as Integer) 
 

would probably yield the sort of error you describe.

Good Night

Phil

[PhilOfWalton]

A further thought

Have you got

Option Compare Database
Option Explicit
 

at the top of EVERY module. The Option Explicit ensures you don't write code about anything that hasn't been defined.

If these are not there, add them and recompile

Phil

[Petrol]

I'm pretty sure I have.
I'm just looking into Decompile.

[Petrol]

This is getting ridiculous! To recap, my Switchboard form has both the original MS Switchboard macros plus a couple of VBA subs I've added, OnLoad and OnClose. As stated in the Original Post, when I click "Convert Form's Macros to Visual Basic" on the Design tab, it does so and displays "Conversion Finished", but then when the form is opened it gives the On Open error "Procedure declaration does not match the description of event or procedure having the same name". It didn't open or display any code modules, but when I did so manually the OnOpen sub does have the correct name and procedure declaration: Private Sub Form_Open(Cancel As Integer) .

1. I reloaded Access and the DB and deleted all macros, then clicked "Convert Form's macros to Visual Basic". It now gave the same fault (see image attached to original post) but this time referring to the OnLoad and OnClose procedures - which had never been macros and were still the same VBA code as before.

2. I reloaded the DB and deleted all macros and event procedures entirely, then ran "Convert Form's macros ..." again. I then created a new empty OnLoad event procedure, consisting only of the procedure declaration and the End Sub statement. I saved, exited and reloaded the DB, Compacted it, closed and decompiled and recompiled it. The Compile failed with the same error, "Procedure declaration does not match the description of event or procedure having the same name". Likewise, attempts to open the form fail with the same error.

The MSDN Help site (here) suggests that this may be caused by a mismatch of the number or type of parameters. However there is no mismatch in either name or parameters - one system-generated Integer parameter for the Open sub, none for the Load or Current or Close ones.

I think I'll just have to give up the idea of converting the macros to VBA. :-(

[PhilOfWalton]

No, Don't give up. VBA is so much more powerful.

So Can I suggest 3 options.
1) After you have done your conversion, create a new database and import all your tables and queries into it. Then 1 by 1 import your forms and reports and compile after each import. The problem may not be with your switchboard form.

2) Set up a Skype contact with me and I can look at what you are doing by sharing screens .

3 Send be a copy of your Db and I will look at it.
Obviously for the latter 2 options, you will need to send me a private message.

Phil

[Petrol]

Thanks, Phil. You are very generous.

I'm tied up for the next few days ... I'll have to get back to this next week.

Peter

[Petrol]

I still have a few more things to try in a couple of days, but as a quick check I created a new empty DB - no tables, no nuffin - and then built a basic switchboard in it with Switchboard Manager. Very basic - only the Main Switchboard, and only one option on it: GoTo Switchboard back to itself.

Switchboard Manager installed three macros as usual - two on Form events and one on the option button click.

When I Convert form's macros to VB and open the resultant form it finds an error in its own code: Run-time error 32538, "TempVars can only store data. They cannot store objects". The line this occurs on is the one and only line in the Form_Current sub:
TempVars.Add "CurrentItemNumber", ItemNumber

I think S/b Manager is trying to be too smart for its own good!

[PhilOfWalton]

I tried to repeat what you had done, but couldn't get the original switchboard to work, even with the Embedded Macros.

I then converted the Macros to VBA and got compile errors. Having commented them out (to look at later) I get the same error as you do.

So I suggest, if the Switchboard is working OK you leave it alone. Something about letting sleeping dogs lie.

Certainly try to convert all your other Macros

If you really need a fancy switchboard, this is the one I developed

But I think you have seen this in a previous post on January 7th.

It's advantage other than being prettier (in my opinion, and with no aesthetic taste whatsoever), the menu a person sees is different depending on the user login. So for example, the above may be the menu (switchboard) that I see, but a person with less privileges might not see the Maintenance Menu (and all it's sub menus) or the Club Fixed Information Menu. They are used for setting up parameters to run the database.

Phil

[Petrol]

I finally resolved the problem by rebuilding the switchboard from scratch, copying the form layout but coding all the event actions in VBA. Like the macro-driven original created by Switchboard Manager, it displays the items from the Switchboard Items table as continuous forms, filtered on opening by (SwitchboardID=1 AND ItemNumber>0).

It now works fine, except that it displays one more line than there are items in the filtered switchboard list. In the attached screenshot there are 7 items displayed, but an 8th line with just the option button but no item text. The SwitchboardItems table shows there are just 7 items with SwitchboardID=1 and ItemNumber>0. The filter is applied in the OnOpen event, as follows:

Private Sub Form_Open(Cancel As Integer)
 
' Open main switchboard
 
Me.Filter = "[ItemNumber] >0 And [SwitchboardID] = 1"
Me.FilterOn = True
 
End Sub
 

The corresponding macro-driven version created by Switchboard Manager doesn't display this spurious extra line.
In the screenshot of the main menu, the numbers in boxes at the right hand end of each line are the SwitchboardID and ItemNumber respoectively, put in there for debugging purposes. The last line has SwitchboardID=null and ItemNumber=0, violating both the filter conditions. Any ideas why this line shows?

[PhilOfWalton]

It may be worth trying on your form to set Allow Additions to False and Allow Deletions to False.

As I said, I am unfamiliar with the Switchboard, but your screenshot appears to show a blank line ready for you to add a new record. I presume that is handled by the code

Phil

[Petrol]

Hmm, you got it!
Why didn't I think of that?

Thanks also for the offer of your switchboard. Looks impressive, but I've sort of got used to the appearance of mine (and rather like the look of it - the fact that it now works!)