Compile Error

[bill1one]

I keep getting the following compile error:
"User defined type not defined"

I have looked through my code several times and can't see anything.

Here is my code. Any suggestions?

Private Sub cmdSearch_Click()
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

strSQL = "SELECT tblParticipants.ID, tblParticipants.Name " & _
"FROM tblParticipants"

strWhere = "WHERE"

strOrder = "ORDER BY tblParticipants.Name;"

If Not IsNull(Me.txtTitle) Then
strWhere = strWhere & " (tblParticipants.Name) Like '*" & Me.txtName & "*' AND"
End If


strWhere = Mid(strWhere, 1, Len(strWhere) - 5)


Me.lstName.RowSource = strSQL & " " & strWhere & "" & strOrder

lstName.SetFocus

End Sub

 

[GKProgrammer]

It does look OK.

Granted that I don't know exactly what info you base your searches on but the only thing that looks out of place from the code is that you are searching if txtTitle is Null and not txtName, is this on purpose?

Regards,
gkprogrammer

 

[TheAceMan1]

How are ya bill1one . . . . .

In:

[blue]strWhere = strWhere & " (tblParticipants.Name) Like '*" & Me.txtName & "*'  AND"

strWhere = Mid(strWhere, 1, Len(strWhere) - [purple][b]5[/b][/purple])[/blue]

[purple]5[/purple] leaves no leading space for concating [blue]Order By[/blue]. Error is generated here.

Change [purple]5[/purple] to [purple]4[/purple]!

See Ya! . . . . . .

 

[bill1one]

I have made the suggested changes and continue to get the same compiler error?

 

[bill1one]

It seems to be getting hung up at the top, at
dbNm As Database

 

[bill1one]

Fixed it; had to do with my references.

 

[TheAceMan1]

bill1one . . . . .

You still needed to change 5 to 4!

See Ya! . . . . . .