Error appeared in VBA 2003 when form opened

[NorthK]

I am experimenting to create a 3-tier vba app. User can use it to search employee data and display on it. But when I click to open the form, a dialogue box appears with the following error:

The expression On Load you entered as the event property setting produced the following error:
*****************************************************************************
The detail:
This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.
*****************************************************************************

But I don't understand what "For example, if the OnOpen property of a form is set to =[Field],.." means and referring to. What's wrong with the code in On Load? My class declaration has problem or some place else?

I have included part of the code as follow. I have it stuck for a month. Please advise me. Thanks very much.

----------------------------------------------------------
Private Sub Form_Load()

On Error GoTo HandleError

Set objEmp = New clsEmp
Set rsEmp = New ADODB.Recordset

Call LoadRecords
MsgBox "Form Load"
Exit Sub

HandleError:
GeneralErrorHandler Err.Number, Err.Description, PROJECTS_EMP, "Form_Load"
Exit Sub

End Sub
----------------------------------------------------------
Sub LoadRecords()

On Error GoTo HandleError

MsgBox "Load record"
'populate the main recordset
Set rsEmp = objEmp.RetrieveEmp

'if the recordset is empty
If rsEmp.BOF And rsEmp.EOF Then
Exit Sub
Else
'populate the object with values in the recordset
objEmp.PopulatePropertiesFromRecordset rsEmp

'populate the controls on the form with the current record
Call PopulateListbox
End If

Exit Sub

HandleError:
GeneralErrorHandler Err.Number, Err.Description, PROJECTS_EMP, "LoadRecords"
Exit Sub

End Sub
----------------------------------------------------------
Sub PopulateListbox()

On Error GoTo HandleError
'populate the listbox
Set rsEmp = New ADODB.Recordset
Set rsEmp = objEmp.RetrieveEmp(objEmp.EmpID)
PopulateListBoxFromRecordset Me.lstEmp, rsEmp, 1
rsEmp.Close
HandleError:
GeneralErrorHandler Err.Number, Err.Description, PROJECTS_EMP, "PopulateListBox"
Exit Sub

End Sub

 

[MajP]

It is not referring to your form_Load event, but to the OnLoad property. Normally in this property you tell it you want to run the "Event Procedure" or you put in a user defined function. In the properties of the form there is an OnLoad property. To run a vba event procedure you need to put in
"[Event Procedure]"
or you could put a function in there
=someFunction()

So my guess you accidently pasted something in there instead of [Event Procedure].

 

[PHV]

Anyway, objEmp is defined and created in Form_Load but used in LoadRecords and PopulateListbox.

Tip: use the Option Explicit instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NorthK]

Thanks MajP,

I found Event procedure in the OnLoad property of the Form, which is wrong. Now the error is gone but I have similar error as below when I clicked the Search button on the form. The following are the declaration on the form and code for the Search button. I checked the button property but only the Even procedure in the 'OnClick', nothing else, what's wrong? As I have declared everything.

PHV, thanks for reply, I just forgot to paste the declaration code in the form last time and now I have it included.

Any advices?

Thanks

NorthK

***************************************************************************

Error in dialogue box:

The expression "OnClick" you entered as the event property setting produced the following error:
This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.

-------------------------------------------------------
Option Compare Database
Option Explicit
Dim rsEmp As ADODB.Recordset
Dim objEmp As clsEmp
Const PROJECTS_EMP = "frmEmp"
-------------------------------------------------------
Private Sub cmdSearch_Click()
On Error GoTo HandleError

Set rsEmp = objEmp.RetrieveEmp
objEmp.PopulatePropertiesFromRecordset rsEmp
Call PopulateListbox

HandleError:
GeneralErrorHandler Err.Number, Err.Description, PROJECTS_EMP, "cmdSearch"
Exit Sub
End Sub

 

[MajP]

I am a little confused, when you say it is wrong. If I want the following event to occur when I click the button

Private Sub cmdSearch_Click()
   
End Sub

Then in the controls's properties for "onClick" I need
"[Event Procedure]".

If I want it to run a macro or function I can put that in lieu of the "[Event Procedure]" such as
myFunction()
someMacro

So again I think this error comes from the fact the "[Event Procedure]" should be in the "onClick" property of cmdSearch. Telling it to run your event procedure.

 

[NorthK]

Majp:

The [Event Procedure] IS IN 'onClick' property of cmdSearch and it is already the 'way' to tell it to run the procedure. The error message is just too vague and point me to nowhere to find the source of the problem at all. No idea to what 'User-defined type not definited' is referring...At least, I need to know what area i should look for... :< Any advices are appreciated!

I have also included the code in the class and the method which is used by the object objEmp.

*****************************************************************************
More detail Error message:
The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
There may have been an error evaluation the function, event, or macro.
*****************************************************************************

CODE IN CLASS - CLSEMP
*********************************************************************
Option Compare Database
Option Explicit
Const CLS_EMP As String = "clsEmp"
Dim intEmpID As Integer
Dim strFname As String
Dim strNname As String
---------------------------------------------------
Sub PopulatePropertiesFromRecordset(rsEmp As ADODB.Recordset)

On Error GoTo HandleError

'Populate the object with the current record in the
'recordset
Me.EmpID = rsEmp!intID
Me.Fname = rsEmp!txtFname
Me.Lname = rsEmp!txtLname

Exit Sub

HandleError:
GeneralErrorHandler Err.Number, Err.Description, CLS_EMP, "PopulatePropertiesFromRecordset"
Exit Sub

End Sub

 

[MajP]

Sorry you are confusing me. In your prior post you said the error was:

The expression "OnClick" you entered as the event property setting produced the following error:
This error occurs when an event has failed to run because Microsoft Office Access cannot evaluate the location of the logic for the event. For example, if the OnOpen property of a form is set to =[Field], this error occurs because Access expects a macro or event name to run when the event is fired.

I believe I gave the correct solution for this error

Now you say the error is

More detail Error message:
The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
There may have been an error evaluation the function, event, or macro

This normally has nothing to do with the the contents of the procedure, but naming conventions. Most often this is when
A user-defined function or a subroutine has the same name as a module. This type of error can occur even if the procedure is not contained in the module with the same name. To avoid this type of error, change the name of the procedure or function so that it is different from the name of any module that you have in the database or project.

Hit debug, compile and it may point to a dual name condition.

You have a lot of global variables. They should be dimensioned as "public" or "private" nod "dim". You have a constant with I believe the same name as the class. Not sure if that is legal.

You call CLS_EMP as a class. It looks like a standard module not a class module. If this is in a class module you can not call the procedure in that way and would likely give you the above error.

 

[MajP]

should say

You have a lot of global variables. They should be dimensioned as "public" or "private" not as "dim". You have a constant, which I believe has the same name as the class.