Compile Error: User-defined type not defined

Question

I was using the following VBA code and after I compiled the VBA code, I received a "Compile Error: User-defined type not defined" error message on line 11 at the point: "udtSQL As typSQLSelect".

Any help will be greatly appreciated.T

Many Thanks

Option Compare Database
Option Explicit

Private Sub cboCustomerID_AfterUpdate()
    
    sfmInvoice_UpdateRecordSource
    cboInvoice_UpdateRowSource
    
End Sub

Private Sub cboInvoice_AfterUpdate()
    
    sfmInvoice_UpdateRecordSource
    
End Sub

Private Sub cboInvoice_UpdateRowSource()
        
    Dim udtSQL As typSQLSelect
    Dim strSQL As String
    
    Me.cboInvoice = Null
    
    With udtSQL
        .Select = "SELECT DISTINCT qryInvoice.OrderNumber"
        .From = "FROM qryInvoice"
        .Where = "WHERE qryInvoice.OrderNumber Is Not Null"
        .OrderBy = "ORDER BY qryInvoice.OrderNumber"
        
        If (Not IsNull(Me.cboCustomerID)) Then
            .Where = .Where & " AND CustomerID = " & Me.cboCustomerID
        End If
    End With
    
    strSQL = BuildSQLSelect(udtSQL)
    
    Me.cboInvoice.RowSource = strSQL

End Sub

Function sfmInvoice_UpdateRecordSource()
    
    Dim udtSQL As typSQLSelect
    Dim strSQL As String
    
    If (Not IsNull(Me.cboCustomerID)) Then
        udtSQL.Where = "[CustomerID] = " & Me.cboCustomerID
    End If
    
    If (Not IsNull(Me.cboInvoice)) Then
        If (udtSQL.Where <> "") Then
            udtSQL.Where = udtSQL.Where & " AND "
        End If
        udtSQL.Where = udtSQL.Where & "[OrderNumber] = '" & Me.cboInvoice & "'"
    End If

    With udtSQL
        .Select = "SELECT *"
        .From = "FROM qryInvoice"
        If (.Where <> "") Then
            .Where = "WHERE " & .Where
        End If
    End With
    
    strSQL = BuildSQLSelect(udtSQL)
    
    Me.sfmInvoice.Form.RecordSource = strSQL
    
End Function

Private Sub cmdReset_Click()
    
    Me.cboCustomerID = Null
    Me.cboInvoice = Null
    
    sfmInvoice_UpdateRecordSource
    
End Sub
Private Sub CmdPreviewRpt_Click()
    
    If (Not IsNull(Me.cboInvoice)) Then
        DoCmd.OpenReport "rptInvoice", acViewPreview, , "OrderNumber=" & Chr(34) & Me.cboOrderNumber & Chr(34)
    Else
        MsgBox "You must select an invoice number before opening the report!", vbExclamation, "Can't Open Report"
        Me.cboInvoice.SetFocus
    End If
    
End Sub

Answer 1

typSQLSelect is not a built-in data type, and you don't have a type declaration of typSQLSelect in the part of the code that you have posted. Have you defined typSQLSelect elsewhere?

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Hi Hans,

No, I have not defined typSQLSelect elsewhere in the applicaton. How do I defined it?

Many thanks

Answer 3

The typSQLSelect data type and the BuildSQLSelect function must be part of a code library or code module.

How did you come by this code?

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

Hi Hans,

I borrowed this code from an old database application which RunningManHD, from this forum, created for me for that particular application. I uploaded the database and RunningManHD created the code in the application. I was scouring through that application to see how he defined it.

Many Thanks

   

Answer 5

Hi Hans,

Now that you have mentioned "code library or class modue", I found the class module in the old database application and I copied the class module into my new database application and now the compile error message has disappeared. That problem is fixed now. I found a new Compile error which popped up at line 46: -

".sfmInvoice." which is a part of Me.sfmInvoice.Form.RecordSource  

The Compile error is "Method or data member is not found."

Answer 6

Hi Hans,

I figured out the last issue above, as I had not defined the subform control name as "sfmInvoice" and now this problem is fixed.

Thank you again for your help in resolving these problems.

Many Thanks

.