Reserved Error 3201

[sCoRPion_tr]

Hi,

I have database and I am trying to create a search form. I have 2 list boxes on the form one of them includes manufacturer and other includes project types.

When I use forms individually or with other variables the search works and gives me the what I am searching for. However when I use both forms (by selecting one manufacturer and one project type) in the search I receive Runtime Error 3000 and Reserved Error 3201.

I've searched the forum but couldn't any similar problem.

The code is given below. Could anyone help?

Option Compare Database
Private Sub cmdSearch_Click()
On erorr GoTo errr
Me.List_subform.Form.RecordSource = "SELECT * FROM [List] " & BuildFilter
Me.List_subform.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varMan As Variant
Dim varPType As Variant
Dim varItem As Variant

varWhere = Null
varMan = Null
varPType = Null

If Me.txtName > "" Then
varWhere = varWhere & "[Project Name] like """ & Me.txtName & "*"" AND "
End If

If Me.txtGeology > "" Then
varWhere = varWhere & "[Geology].Value like ""*" & Me.txtGeology & "*"" AND "
End If

If Me.txtGeology2 > "" Then
varWhere = varWhere & "[Geology].Value like ""*" & Me.txtGeology2 & "*"" AND "
End If

If Me.cmbSqueezing = -1 Then
        varWhere = varWhere & "([Squeezing] = True) AND "
    ElseIf Me.cmbSqueezing = 0 Then
        varWhere = varWhere & "([Squeezing] = False) AND "
    End If
    
If Me.txtDiaFrom > "" Then
varWhere = varWhere & " [Diameter] >= " & Me.txtDiaFrom & " AND "
End If
If Me.txtDiaTo > "" Then
varWhere = varWhere & "[Diameter] <= " & Me.txtDiaTo & " AND "
End If

'Manufacturer Filter
For Each varItem In Me.listmanufacturer.ItemsSelected
varMan = varMan & "[Manufacturer] = """ _
& Me.listmanufacturer.ItemData(varItem) & """ OR "
    Next

	If IsNull(varMan) Then

   Else

If Right(varMan, 4) = " OR " Then
varMan = Left(varMan, Len(varMan) - 4)
End If

varWhere = varWhere & "( " & varMan & " )"
End If

'Project Type Filter
For Each varItem In Me.listptype.ItemsSelected
varPType = varPType & "[Project Type] = """ _
& Me.listptype.ItemData(varItem) & """ OR "
Next

If IsNull(varPType) Then

Else

If Right(varPType, 4) = " OR " Then
varPType = Left(varPType, Len(varPType) - 4)
End If

varWhere = varWhere & "( " & varPType & " )"
End If

If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function

 

[pbaldy]

This is a handy technique for debugging SQL in code:

http://www.baldyweb.com/ImmediateWindow.htm

 

[sCoRPion_tr]

Hi thank you very much for your help.

I've tried what you've said and Immediate windows gave that;

[Project Name] like "Project*" AND  [Diameter] >= 1 AND [Diameter] <= 10 AND ( [Manufacturer] = "Company 1" OR [Manufacturer] = "Company 2" ) ( [Project Type] = "Metro" OR [Project Type] = "Railway" )

I've added an AND between the Manufacturer and Project type section and the problem is solved

 

[pbaldy]

Happy to help!