Compile Error: User Defined Type Not Defined

[lp2131]

Hi,

I am just starting out in the world of VBA and I am creating a DB which is borrowing code from another DB (created from my programmer co-worker). Even though the functionality in each DB is the same, mine keeps coming up with a compile error. My co-worker is on vacation for 2 wks...I'd appreciate any help. It errors out at the Set prm line. Thanks!

Code:

Private Sub EmployeeNumber_AfterUpdate()
Dim EmpNo As Integer

EmpNo = Me.EmployeeNumber.Value

Set prm = New ADODB.Parameter
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

'Setup the command object
With cmd
.CommandText = "qryEmpInfo"
.CommandType = adCmdUnknown

'create the parameter
Set prm = .CreateParameter("EmpNo", adVarChar, adParamInput, 5)
.Parameters.Append prm
'set the parameter's value
.Parameters("EmpNo") = EmpNo

'Associate the command object with a connection
.ActiveConnection = CurrectProject.Connection

'request the recordset
Set rs = .Execute

 

[MajP]

Check your references under "Tools". You probably need a reference to ADODB.

 

[lp2131]

I just verified that I do have MS ADO Ext. 2.8 checked in References...

 

[MajP]

Need Microsoft Active X Data Objects x.x library

 

[lp2131]

That helped except I get a Run time error 424 Object required. Debug starts at Active Connection.

Thanks!

 

[PHV]

Replace this:
CurrectProject.Connection
with this:
CurrentProject.Connection

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lp2131]

Okay...now I am getting a Run time Error 3021: Either BOF or EOF is True, or the current record has been deleted. Debug starts at first line of form load. I have verified that the record is there.

Formload:
If Me.EmployeeNumber.Value = rs!EMPLOYEE Then
Me.[Last Name].Value = rs!LAST_NAME
Me.[First Name].Value = rs!FIRST_NAME
Me.[Middle Initial].Value = rs!MIDDLE_INIT
Me.ADDR1.Value = rs!ADDR1
Me.ADDR2.Value = rs!ADDR2
Me.CITY.Value = rs!CITY
Me.STATE.Value = rs!STATE
Me.ZIP.Value = rs!ZIP
Me.[Process Level].Value = rs!PROCESS_LEVEL
Me.[Union Code].Value = rs!UNION_CODE
Me.SUPERVISOR.Value = rs!SUPERVISOR
Me.[Date Hired].Value = rs!DATE_HIRED
Me.[Adj Hire Date].Value = rs!ADJ_HIRE_DATE
Me.[Emp Status].Value = rs!EMP_STATUS
Me.Position.Value = rs!R_POSITION
Me.DESCRIPTION.Value = rs!DESCRIPTION
Me.Phone.Value = rs!PHONE_NBR
Me.[EEO Class].Value = rs!EEO_CLASS
Me.SEX.Value = rs!SEX
Me.Schools_Sch_Name.Value = rs!Schools_Sch_Name
End If

rs.Close
Set rs = Nothing
Set cmd = Nothing
Set prm = Nothing

End With

End Sub

 

[JoeAtWork]

The error you are getting indicates there were no matching records in your query. To check for this possibility, check if both the BOF and EOF properties of the recordset are True, e.g.

If Not (rs.BOF And rs.EOF) Then
    If Me.EmployeeNumber.Value = rs!EMPLOYEE Then
        Me.[Last Name].Value = rs!LAST_NAME
        Me.[First Name].Value = rs!FIRST_NAME
        Me.[Middle Initial].Value = rs!MIDDLE_INIT
        Me.ADDR1.Value = rs!ADDR1
        Me.ADDR2.Value = rs!ADDR2
        Me.CITY.Value = rs!CITY
        Me.STATE.Value = rs!STATE
        Me.ZIP.Value = rs!ZIP
        Me.[Process Level].Value = rs!PROCESS_LEVEL
        Me.[Union Code].Value = rs!UNION_CODE
        Me.SUPERVISOR.Value = rs!SUPERVISOR
        Me.[Date Hired].Value = rs!DATE_HIRED
        Me.[Adj Hire Date].Value = rs!ADJ_HIRE_DATE
        Me.[Emp Status].Value = rs!EMP_STATUS
        Me.Position.Value = rs!R_POSITION
        Me.DESCRIPTION.Value = rs!DESCRIPTION
        Me.Phone.Value = rs!PHONE_NBR
        Me.[EEO Class].Value = rs!EEO_CLASS
        Me.SEX.Value = rs!SEX
        Me.Schools_Sch_Name.Value = rs!Schools_Sch_Name

        '**** Think you need to add this too ****
        rs.Update

     End If
End If

rs.Close
Set rs = Nothing
Set cmd = Nothing
Set prm = Nothing

 

[lp2131]

Okay - done. Now I get another Compile Error - End With Without With

Again, many thanks!

 

[ItIsHardToProgram]

You have a "end with" without a "with" to begin with....

example of with:

With me
.visible = true
.value = "ab"
end with

 

[lp2131]

Here is my entire code...there was a With earlier in the code (see first post)...Many thanks!

Private Sub EmployeeNumber_AfterUpdate()
Dim EmpNo As Integer

EmpNo = Me.EmployeeNumber.Value

Set prm = New ADODB.Parameter
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

'Setup the command object
With cmd
.CommandText = "qryEmpInfo"
.CommandType = adCmdUnknown

'create the parameter
Set prm = .CreateParameter("EmpNo", adVarChar, adParamInput, 5)
.Parameters.Append prm
'set the parameter's value
.Parameters("EmpNo") = EmpNo

'Associate the command object with a connection
.ActiveConnection = CurrentProject.Connection

'request the recordset
Set rs = .Execute

'Formload

Formload:
If Not (rs.BOF And rs.EOF) Then
If Me.EmployeeNumber.Value = rs!EMPLOYEE Then
Me.[Last Name].Value = rs!LAST_NAME
Me.[First Name].Value = rs!FIRST_NAME
Me.[Middle Initial].Value = rs!MIDDLE_INIT
Me.ADDR1.Value = rs!ADDR1
Me.ADDR2.Value = rs!ADDR2
Me.CITY.Value = rs!CITY
Me.STATE.Value = rs!STATE
Me.ZIP.Value = rs!ZIP
Me.[Process Level].Value = rs!PROCESS_LEVEL
Me.[Union Code].Value = rs!UNION_CODE
Me.SUPERVISOR.Value = rs!SUPERVISOR
Me.[Date Hired].Value = rs!DATE_HIRED
Me.[Adj Hire Date].Value = rs!ADJ_HIRE_DATE
Me.[Emp Status].Value = rs!EMP_STATUS
Me.Position.Value = rs!R_POSITION
Me.DESCRIPTION.Value = rs!DESCRIPTION
Me.Phone.Value = rs!PHONE_NBR
Me.[EEO Class].Value = rs!EEO_CLASS
Me.SEX.Value = rs!SEX
Me.Schools_Sch_Name.Value = rs!Schools_Sch_Name
rs.Update

End If

rs.Close
Set rs = Nothing
Set cmd = Nothing
Set prm = Nothing

End With

End Sub

 

[ItIsHardToProgram]

Could you copy paste DIRECTLY from VB please?

 

[ItIsHardToProgram]

My guess would be

Private Sub EmployeeNumber_AfterUpdate()
Dim EmpNo As Integer

EmpNo = Me.EmployeeNumber.Value

Set prm = New ADODB.Parameter
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset

'Setup the command object
With cmd
    .CommandText = "qryEmpInfo"
    .CommandType = adCmdUnknown
    
    'create the parameter
    Set prm = .CreateParameter("EmpNo", adVarChar, adParamInput, 5)
    .Parameters.Append prm
    'set the parameter's value
    .Parameters("EmpNo") = EmpNo
    
    'Associate the command object with a connection
    .ActiveConnection = CurrectProject.Connection
    
    'request the recordset
    Set rs = .Execute
[red]End with[/red]

and remove the End With before the End sub in the form load.

 

[lp2131]

Made that change and now I get a Block If Without End If error...

 

[ItIsHardToProgram]

f Not (rs.BOF And rs.EOF) Then
If Me.EmployeeNumber.Value = rs!EMPLOYEE Then
Me.[Last Name].Value = rs!LAST_NAME
Me.[First Name].Value = rs!FIRST_NAME
Me.[Middle Initial].Value = rs!MIDDLE_INIT
Me.ADDR1.Value = rs!ADDR1
Me.ADDR2.Value = rs!ADDR2
Me.CITY.Value = rs!CITY
Me.STATE.Value = rs!STATE
Me.ZIP.Value = rs!ZIP
Me.[Process Level].Value = rs!PROCESS_LEVEL
Me.[Union Code].Value = rs!UNION_CODE
Me.SUPERVISOR.Value = rs!SUPERVISOR
Me.[Date Hired].Value = rs!DATE_HIRED
Me.[Adj Hire Date].Value = rs!ADJ_HIRE_DATE
Me.[Emp Status].Value = rs!EMP_STATUS
Me.Position.Value = rs!R_POSITION
Me.DESCRIPTION.Value = rs!DESCRIPTION
Me.Phone.Value = rs!PHONE_NBR
Me.[EEO Class].Value = rs!EEO_CLASS
Me.SEX.Value = rs!SEX
Me.Schools_Sch_Name.Value = rs!Schools_Sch_Name

rs.Update

End If
[red]End if[/red]

 

[lp2131]

Done...now, no errors but my form does not populate.

 

[ItIsHardToProgram]

cna't help you with that....

 

[lp2131]

Thanks anyway! I really appreciate your help. Anyone else out there have any ideas?

 

[JoeAtWork]

Well your form doesn't populate because your query is not finding any matching records, as I said in my previous post.

Are you sure you're inputting an EmployeeNumber that exists?

If so, what data type is it? In your code:

Set prm = .CreateParameter("EmpNo", adVarChar, adParamInput, 5)

you are indicating it is character data. If in fact it is a number you should change adVarChar to a number type, perhaps adInteger.

Basically, what you need to find out is why your query is returning no matching records.

 

[lp2131]

I took your suggestion and changed the adVarChar to adInteger. There is still no change. I also verified my query - there are 4863 records in it and I am trying to pull data on those employees. The EmployeeNumber field is a combo box and I am using the drop down list to select an employee. So the data is there but I just can't get it to populate the form.

Any other suggestions?

Thanks so much for all your efforts to date!