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

 

[JoeAtWork]

I also verified my query - there are 4863 records

Does your query actually prompt to enter an employee number? If not, you need to actually add the [EmpNo] parameter in the query design. Open the query in design mode, under the EMPLOYEE column enter in the Criteria row the following:

[EmpNo]

Try running your query, it should prompt you with an "Enter Parameter Value" dialog box. Enter a known employee number - if it returns a record the query is correct and you can save it.

Now go back to your code, make sure you are creating the right type of parameter. If EMPLOYEE is a string field use would you had originally, if it's a long integer use something like:

Set param = cmd.CreateParameter("EmpNo", adInteger, adParamInput, , EmpNo)

I've tested this scenario and it works. Here's the SQL statement for my query:

SELECT ExcelTable.ID, ExcelTable.Desc
FROM ExcelTable
WHERE (((ExcelTable.ID)=[EnterID]));

Here's the code I used, which used the above query to populate the txtDesc box on my form.

Private Sub cmdGetData_Click()
    Dim cmd As ADODB.Command
    Dim param As ADODB.Parameter
    Dim rs As ADODB.Recordset
    
    Set cmd = New ADODB.Command
    cmd.CommandText = "qryPromptID"
    cmd.ActiveConnection = CurrentProject.Connection
    
    Set param = cmd.CreateParameter("EnterID", adInteger, adParamInput, , txtID.Value)
    
    cmd.Parameters.Append param
    
    Set rs = cmd.Execute
    
    If Not (rs.BOF And rs.EOF) Then
        txtDesc = Nz(rs("Desc"), "")
    Else
        MsgBox "No record with ID = " & txtID.Value & " was found."
    End If
    
    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
    
End Sub

 

[lp2131]

I don't mean to sound stupid but where does my Form load fit into the above code? I want to try yours but can't figure out what to leave in and what to cut out of mine.

 

[JoeAtWork]

My code is more or less the same as yours, just that it works on a different table and loads less fields. Your code is mostly correct, I think, you just have to concentrate on two things.

1. The design of your query - does it actually have an EmpNo parameter? I'm not talking about code here, I'm talking about the actual query you are calling, "qryEmpInfo". If you run it manually, does it prompt for EmpNo?

2. If the above is true, the only line of your code that might need to be changed is:

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

 

[lp2131]

Joe,

Sorry for not getting back to this sooner but I am covering for 2 folks who are out for extended time. I am just now getting to take a look at this again. The answer to question 1 is yes. I substituted the parameters line above and still nothing. If you or anyone else has any other suggestions, I'd love them.

Thanks!!!!