3

I created two forms in Microsoft Access 2010: let's call them Form1 and Form2.

A Button on Form1 is supposed to call Form2. Form2 has two option buttons that are inside and optiongroup form, and two buttons, one of which is cancel. I created the following code which worked perfectly for a while: 

Private Sub cmdCancel_Click()
    DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdCreateFactsheet_Click()
Dim sFund As String

If Me.OptionGroup = 2 Then
    On Error Resume Next
        sFund = Me.ComboFundliste.Value
    On Error GoTo 0
    If sFund = "" Then
        MsgBox ("Please select a Fund")
        End
    Else
        Call modAdvisoryFactSheet.FactSheetSelection(sFund)
    End If
End If

End Sub

Private Sub frmSelection_Open()
    Me.OptionGroup.DefaultValue = 1
End Sub

Private Sub OptOneFund_GotFocus()
    If Me.OptionGroup = 2 Then
        Me.ComboFundliste.Enabled = True
    End If
End Sub

Private Sub OptAllFunds_GotFocus()
    If Me.OptionGroup = 1 Then
        Me.ComboFundliste.Value = ""
        Me.ComboFundliste.Enabled = False
    End If
End Sub

Now, every single Sub produces an error when I try to open form2, or if I open form2 manually, when I try to klick on any of the controls.

The message is always the same: The expression On Click you entered as the event property setting produced the following error: variable not defined

I realize this must have something to do with how I reference the form, but I don't understand why this worked well and then suddenly stopped working. I didn't change anything as far as I can remember and I don't see what is wrong!

Any help appreciated.

rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • Try debugging your code. Click on the margin of the line that calls your form 2 (this should come up with a brown highlight. When you go back to your form and click the button, the control will go to the VBA code, now manually walk through the code using `F8` button, you will know where the error occurs. – PaulFrancis Jun 10 '15 at 09:20
  • I don't even get to the point where I can start stepping through the code. I have tried commenting out lines of code, but in order for it to open the form without an error message, I need to comment out the entire sub! – rohrl77 Jun 10 '15 at 09:27
  • The best place to start (as mentioned in my comment) `the line that calls your form 2`. – PaulFrancis Jun 10 '15 at 09:28
  • i am calling Form2 using the On Click Event Property in Form1 using a macro (not VBA Code, but the built in ones you create using Macro Tools)... so, as I said, I don't get to a first line of code. – rohrl77 Jun 10 '15 at 09:33
  • Open-up the VBA editor (alt+F11) and look at Tools > References... Is there anything listed as "MISSING"? I'd also consider redoing your macro expression (either as macro or in VBA if you can) and maybe do decompile as well - take a backup first so you can get back to what you had if it doesn't help. – Matt Hall Jun 10 '15 at 09:54
  • @Matt Hall no nothing is missing in the references. I have actually already tried redoing the code. I attempted to do it all in VBA, but I get the same error message. I think it has to do with MS Access' inability to find the code one I attempt to run it. strange thing is, that if I go to form properties and click the "..." button in the property field it takes me to the correct line of code!?? – rohrl77 Jun 10 '15 at 11:20
  • 2
    When you did it in VBA did you catch the line of code it was highlighting the error on? The other thing you can do is Debug > Compile and see if breaks somewhere (if it does, tell us where). Can you walk us through the line `Call modAdvisoryFactSheet.FactSheetSelection(sFund)`. – Matt Hall Jun 10 '15 at 11:27
  • Running the compiler I have finally gotten an error message that pointed at a line of code... in the module you are asking about there was a variable that was not defined! Now it works... thank you so much for that idea! – rohrl77 Jun 10 '15 at 11:32
  • Please put it as an answer so that I can mark it as correct! – rohrl77 Jun 10 '15 at 11:33

2 Answers2

2

This answer really belongs to Matt Hall. Matt, if you want to answer it, I'd be happy to give you the checkmark. For the time being, I'll put this up so it's clear what the solution was:

I had to run Debug>Compile in order to see the line that caused the error. It turned out that I had a Variable in a Sub several level down, that wasn't properly defined.

rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • 1
    That's ok.. I didn't have answers, just suggestions to try.. glad it works now :) – Matt Hall Jun 11 '15 at 15:49
  • My problem got worked after following this link http://stackoverflow.com/questions/10637622/communicating-with-the-ole-server-or-activex-control . Please refer to the comments suggested by Abu Hassan on the link. Thanks :) – srbhattarai Oct 26 '16 at 14:10
2

This did not quite cover the problem but I wanted to share my really simple solution. I have an Access 2010 Split DB that runs a form on startup. Occasionally, I'll get "The Expression X you entered as the event property produced the following error...". Since this occurs at start up, X initially = "On Load".

Clicking through the dialog box will bring up the form, but then ALL the other controls will produce the same error with X being the event that would normally be handled (On Click, Dbl Click, After Update, etc.).

A temporary fix occurs by just opening the Visual Basic window through Database Tools or switching to Design View and then View Code. The form will then work until you close the DB. When you reopen it, the error occurs again.

A longer fix occurs by opening the Visual Basic window and making ANY change. By any, I do mean adding a space or deleting one. Saving the form and exiting fixes the problem DB until.....it decides to happen again.

Otha Stubblefield
  • 21
  • 2