Building a Search Form

[acdev2012]

I've created a form, with multiple combo boxes, to select choices that will display a list data results based on my selections in a datasheet format. When I make my selections and click on the "Add Filter" button (which I've created on my search form)...nothing happens...I get no data results....

Any suggestions?????

 

[sparks80]

Hi,
As this is something many people want to do there are lots of useful references on-line (including on this forum!).

As a starting point you could do far worse than take a look at this link, from Allen Browne:

http://allenbrowne.com/ser-62.html

 

[acdev2012]

Thx...I've already read through this one and tried to use it but, it didn't work for me.

 

[sparks80]

I can happily help you implement the search form, but it would help if you could provide some more details first. At the moment it is impossible to tell what is causing the error.

1. What are you using to return the search results. Is it a listbox, continuous form, other?
2. What is the record source for the listbox, form.....
3. What is linked to the "Add filter" button? Is it a macro or VBA code?
4. If the answer to question 3 is VBA Code, can you post the code you are working on.

Alternatively I will happily take a look at your search form if you would like to post a sample of the database (with any confidential data or objects removed).

 

[rodmc]

check this thread out, theres quite a few links here

http://www.access-programmers.co.uk/forums/showthread.php?t=214355&highlight=gromit

 

[acdev2012]

Thank you for this link. I reviewed the dbsearchform example and it is exactly what I'm looking for. I modified my search form, based on the example but, I can't get it to work.

 

[acdev2012]

PLEASE HELP!!!!!

I've been trying to get my search form to work and I am completely STUCK!!! I reviewed the dbsearchform example and modified it to use in my search form but, the code that is used on the combo boxes is not working for me. On the dbsearchform example, part of the code for one of the combo boxes begins as follows, "SELECT 0 as CompanyID, "<all>" as CompanyName..." When I use that code on my combo box, I get a syntax error message stating that an operator is missing and the "all" does not populate in my combo box as it does in the dbsearchform example.

I'm at a loss.....

 

[rodmc]

can you post the db in mdb format?

 

[acdev2012]

I can post the db but, I can post the vba code that I'm using...if that will help.

 

[rodmc]

post away!

 

[acdev2012]

Option Compare Database
Option Explicit
Private Sub btnClear_Click()
Dim intIndex As Integer

' Clear all search items
Me.txtFirstName = ""
Me.txtLastName = ""
Me.txtState = ""
Me.txtCondition = ""
Me.cboJobCode = 0
Me.cboDepartment = 0
End Sub
Private Sub btnSearch_Click()

' Update the record source
Me.frmSubCandidates.Form.RecordSource = "SELECT * FROM qryCandidateDetails " & BuildFilter

' Requery the subform
Me.frmSubCandidates.Requery
End Sub
Private Sub Form_Load()

' Clear the search form
btnClear_Click

End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter

' Check for LIKE First Name
If Me.txtFirstName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
End If

' Check for LIKE Last Name
If Me.txtLastName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtLastName & "*"" AND "
End If

' Check for State
If Me.txtState > "" Then
varWhere = varWhere & "[State] > " & Me.txtState & " AND "
End If

' Check for Condition
If Me.txtCondition > "" Then
varWhere = varWhere & "[Condition] > " & Me.txtCondition & " AND "
End If

' Check for JobID
If Me.cboJobCode > 0 Then
varWhere = varWhere & "[JobID] = " & Me.cboJobCode & " AND "
End If

' Check for DeptID
If Me.cboDepartment > 0 Then
varWhere = varWhere & "[DeptID] = " & Me.cboDepartment & " AND "
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere
End Function

 

[rodmc]

first thing I notice is this

If Me.txtState > "" Then
varWhere = varWhere & "[State] > " & Me.txtState & " AND "
End If

Not sure why you've got that, so If [State] is greater/larger than Me.txtState?????
not sure what you trying to do here

 

[rodmc]

Can I make a suggestion, comment out the filters (check like) and uncomment them one by one, so in other words leave only First name filter in and then try it, if that works uncomment the next one, and so on so forth

 

[rodmc]

should also say that most of your code looks ok until you get to the lines of codes Ive quoted, Im not so sure about them, the logic in them doesnt make any sense to me. Its look like your saying if this State has more characters then include them in the search results

Can anyone else confirm that

 

[acdev2012]

I'm using code from a sample searchform.db example that was given to me on this thread so...I'm not sure how the code is supposed to work. I've modified it, based on what I think it should be but....I'm still having problems.

Also, I'm not quite clear on how to comment out the filters (check like) and uncomment them one by one....

 

[rodmc]

In the BuildFilter section - see how you've got

' Check for State

put an apostrophe ' in front of the code for each section of the filter, dont do it for the first filter criteria (Firstname), cos your going to test if that filter works first

 

[rodmc]

if you cant get it, you might be better posting the mdb file and I'll make the changes, print off your existing code before you do then you can compare it to mine and see what Ive done