Errors with VBA Code in Class Objects in accde database

[billw1100]

Hi, this is my first post here, so I hope I'm doing it right.

After converting an Access 2007 accdb database to accde, the vba code in my modules runs OK, but the code in my class objects (forms) wont run. In these cases, I always get the error message "The expression On Dbl Click you entered as the event property setting produced the following error: Can't perform operation since the project is protected" (or something similar depending on the particular event).

Please note that I don't get any errors when running the accdb file.

I don't understand why some of the code works, and some doesn't. I have searched Google for several days to find a solution, but I can't find this situation.

I am converting the front end file only to accde, not the back end tables. I do have a custom ribbon xml script in table UsysRibbons, which is located in the front end (because it doesn't work if I put it in the back end).

My files are all in trusted locations, I compile immediately before making the accde file, I've checked my References, I've de-compiled and compiled, I've copied all objects to a new (clean) database, I've even tried it with mdb and mde. Same problem every time.

My references are:
Visual Basic For Applications
Microsoft Access 12.0 Object Library
OLE Automation
Microsoft Office 12.0 Access database engine Object
Microsoft Office 12.0 Object Library
Microsoft Scripting Runtime

Any suggestions would be greatly appreciated.

[Rabbit]

You would need to post the expression that you entered on the double click to offer any help. Hard to tell you why the code won't work if you don't tell us what the code is.

[billw1100]

Sorry about that. Here is the code.

Private Sub UserName_DblClick(Cancel As Integer)

'On Error GoTo Err_Handler
Dim strModName As String
strModName = Me.Module.Name

    If (Form.Dirty) Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    
    'Opening Argument at end of following command is used to show/hide some buttons on details form
    DoCmd.OpenForm "frm_UserDetails", acNormal, "", "[UserID]=" & UserID, , acDialog, "oaEdit"
    
Exit_Handler:
     Exit Sub
Err_Handler:
      Select Case Err.Number
      Case 9999                         
          Resume Next                   
      Case 999                          
          Resume Exit_Handler           
      Case Else 
          Call LogError(Err.Number, Err.Description, strModName, "UserName_DblClick()")
          Resume Exit_Handler
      End Select
      
End Sub

[NeoPa]

I see nothing in that code that might lead to such an error message.

Perhaps you could narrow down the search by telling us which line number the error occurs on (if you have any way of knowing that).

[billw1100]

After a lot of good old "trial and error", I've nailed the problem down to the error code (if I delete the error code lines, it works OK).

I will try to limit it down further, then post another message.

The thing that is really concerning me is: why does it work in accdb format, but not in accde format?

[billw1100]

I've found the offending line of code:

strModName = Me.Module.Name

It seems that Me.Module.Name works fine in accdb, but not in accde format. I have no idea why it should be different (but it would be nice to find out).

I need to know the module name so that I can include it in my error table.

As a work-around, I can manually type in the module name for every sub, but if anyone knows another way to programmaticall y determine it, that would be great.

[Rabbit]

Because once the code is compiled, there is no such concept as modules anymore. Modules are a way for humans to organize code, computer code needs no such paradigm.

There's no need to explicitly define the module in each sub. Define a constant at the module level and use that constant in all your subs.

[billw1100]

Now it makes total sense regarding the modules.

I've defined a constant for each form and it works fine now (less lines of code than before).

Thanks very much for your quick advice.

[Rabbit]

No problem, good luck with the rest of your project.

[NeoPa]

Alternatively, as the default name for an associated class module is "Form_" or "Report_" followed by the actual name of the object, then the following code may work :

strModName = IIf(TypeOf Me Is Form, "Form_", "Report_")+Me.Name

[MikeTheBike]

Hi

I am a little confused here, wouldn't

strModName = Me.Name

work for the purpose of identifying the 'Module' (Form or Report) returning the error message?

I must be missing something!

MTB

[billw1100]

Thanks NeoPa for your suggestion, but prefixing the form name with "Form" isn't necessary for me because I always start my form names with "frm".

Thanks MTB. KIS principal wins again :) I don't even recall how I ended up with Me.Module.Name originally (copy and paste I expect).

As you might have guessed, I am fairly new to VBA programming, so please excuse me if I ask dumb questions in the future :)

[NeoPa]

That would certainly be fine for their needs MtB, but not a match for the question.

As it turns out the full module name isn't required after all, Me.Name is a fine and simple solution. It was only the OP's confusion that made the question different (and we like to answer the question where possible).