Need Help with Search Code

[cstickman]

Hello Everyone,

I found a code on the internet that I liked, but I cannot get it to work. I have a form with that has a subform within it and then two combo boxes of headers. The user has an option to select from either one and click on the search button. I will attach all the code that was inserted into vba. When I click on one of the drop down lists it is asking me for parameters values. Any help or suggestions would be greatly appreciated. Thanks

<code>
Private Sub cmbclear_Click()
Me.frmSearch1Sub.Form.RecordSource = "SELECT * FROM TravelerApproved "
Me.frmSearch1Sub.Form.Requery
cmbdefecttypes = ""
cmbgenerals = ""
cmbdefecttypes.SetFocus
End Sub
Private Sub cmdsearch_Click()
On erorr GoTo errr
Me.frmSearch1Sub.Form.RecordSource = "SELECT * FROM TravelerApproved " & BuildFilter
Me.frmSearch1Sub.Requery
Exit Sub
errr:
MsgBox Err.Description
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim tmp As String
tmp = """"

varWhere = Null

If Me.cmbdefecttypes > "" Then
varWhere = varWhere & "[defecttype] like " & Me.cmbdefecttypes & " AND "
End If

If Me.cmbgenerals > "" Then
varWhere = varWhere & "[questiontype] like " & tmp & Me.cmbgenerals & tmp & " AND "
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

Private Sub Form_Load()
cmbclear_Click
End Sub
</code>

 

[llkhoutx]

You posted

When I click on one of the drop down lists it is asking me for parameters values.

That indicated that you have a column in your combo box that is not defined. That column is probably misspelled.

 

[cstickman]

Thank you for the quick response. I went back and double checked and everything is spelled correctly. Thanks

 

[llkhoutx]

Use a breakpoint and single step through your code, checking to be sure control and variable values are what you think they should be.

 

[smig]

Before you copy some code you find on the internet try to understand what this code exectly do and how it work.

If you want us try to help you make it readable:
1. Put your code into

 section
2. Make some spaces between subs
3. Put some comments

 

[jdraw]

Further to the comments by llkhoutx and smig, see these debugging techniques by Chip Pearson. They can be very helpful in problem resolution.

 

[cstickman]

I agree completely with all of you. I broke it apart and with doing so I was able to understand the code and fix my mistake. I have it working now and I am finally done with this project. Thanks everyone!!!

 

[smig]

Glad you solved it