Create a search window

[SusanK4305]

Ok I Idk why i cna't do this any more (seeing as I did it back in 2010, oh well.
Purpose: To create a form [Search]that contain 3 unbound text fields (Filter1, Filter2, and Filter3). When one clicks the search aka (cmdFilter) the subform [Search Results] is filtered based on the 3 text fields.
What I need help on: I need a VB code the will make the filter go from one Filter to the other then show results in the sub form.

Example:
Filter1: Smith
Filter2: John
Filter3: (Blank)


Sub form results:
Smith John A.
Smith John D.
etc....

* Form called [Search] has no record source ie unbound
* Form called [Search Results] has a record source of qrySearch

This is what I have as my code so far, but I know it isn't correct... looks off. Please help

Private Sub cmdFilter_Click()
Dim strWhere As String
 
    If Not IsNull(Me.Filter1) Then
        strWhere = strWhere & "([Search Results]![SNN] Like ""*" & Me.Filter1 & "*"") AND "
    End If
 
    If Not IsNull(Me.Filter1) Then
        strWhere = strWhere & "([Search Results]![Last Name] Like ""*" & Me.Filter1 & "*"") AND "
    End If
 
    If Not IsNull(Me.Filter1) Then
        strWhere = strWhere & "([Search Results]![First Name] Like ""*" & Me.Filter1 & "*"") AND "
    End If
    If Not IsNull(Me.Filter2) Then
        strWhere = strWhere & "([Search Results]![SNN] Like ""*" & Me.Filter2 & "*"") AND "
    End If
 
    If Not IsNull(Me.Filter2) Then
        strWhere = strWhere & "([Search Results]![Last Name] Like ""*" & Me.Filter2 & "*"") AND "
    End If
 
    If Not IsNull(Me.Filter2) Then
        strWhere = strWhere & "([Search Results]![First Name] Like ""*" & Me.Filter2 & "*"")"
    End If
    ' And same for Filter 3
End Sub

[ariful alam]

For this, I don't think you need a VBA Code.
1. First you create the search form with 3 text boxes those you called Filter1, Filter2, Filter3. And Save the form as Search.

2. Now create the Search Result select query and set the criteria for the fields which are responsible for search result. in every criteria select Search forms Filter fields in the place of # of this -> 'Like "*" & # & "*"' (without single quotation)

3. now Save the query and make a form for this query.

4. Now open the Search form in Design view and add a button on it. set the buttons command to open form and select the query result form as open form.

5. Test it.


I added a Access 2007 here as zip. you can check that.

[zmbd]

SusanK4305:
You might want to review the small artical written by NeoPa
Example Filtering on a Form.
And the additional discussion about using subforms between Rabbit and myself:
Discussion: Advantages of a Subform vs. Filtering

-z

[NeoPa]

@Susan,

Please check out Before Posting (VBA or SQL) Code before posting again. You should probably also check the instruction threads at the top of the forum to avoid posting in a way that's against the rules. After >82 posts it's not unreasonable to expect questions that conform to the posted rules.

As for your question, I believe zmbd has given you all the help that should be required, but if you find difficulty with anything from one of the linked threads then please specify clearly your problem and someone will be happy to help you.

[SusanK4305]

ariful alam ,

The prob with this is that there is nothing telling the openform cmd where to get the criteria’s.

[SusanK4305]

NeoPa,

I don't mean to be disrespectful, but if I understood what all those instuctions ment I prob. would have a better idea on a simple search. I would be happy to format my question any way you (the site) would like, but I am not that advanced.

[zmbd]

Susan,
Could you be more specific... is this in Ariful's post?

[ariful alam]

@SusanK4305,

sorry, i didn't get what about u are saying. can you pls, explain.

[zmbd]

@Susan:
I'm sure that NeoPa is only trying to help you improve your posts so that we have a common basis to help you with... much like in college or at work, reports are often required to be in a specific format. If you are having trouble understanding how to post something, ask.

Now, back to your question... even Ariful is at a loss so that's two of us in regards to #5.

I really think you should rethink your form. Having three text boxes that can take a combination of 3 different types of data in any order means that you will need some fairly complicated code - that's 10 combinations allowing for replacement and repetition. Can you really search for "John" entered three times or the same SSN entered three times?

It seems to me that either a series of cascading comboboxes or a form/subform might be easier solutions to your question. Have you had a chance to review the information in NeoPa's tutorial? There is a link there for the cascading combobox too.

-z

[SusanK4305]

ariful alam,

I didn't see the attach Database before, sorry. I took a look at it and just repeated what i read in the db. It works for now, Thank you. :)

[SusanK4305]

zmbd,

I know he is trying to help. I just don't understand. I will try to look at it again tonight and hope it clicks in my head.

Ariful Alam's DB will work for now. I am still looking at NeoPa's tutorial... trying to understand and see how mine works in it.

Pease note: I didn't plan on searching for John 3 time. :) I will keep looking at NeoPa's tutorial. The cascading comboboxes sound familiar.Thank y'all.

[SusanK4305]

NeoPa

Ok so I think I get what to do up to 2.a (Debug \ Compile Project). It just looks like it shows me where the "error" is in my codes. I am lost, however I do get the copy/ past in B 2 and I think I get the part about Code Tag I will try to do what I can Next time, but knowing me I will prob mess it up.

[NeoPa]

Susan,

If your code doesn't compile then you should be asking questions about how to resolve any such issues first. Trying to work with code that doesn't even compile is frustrating for everyone involved, especially yourself, as people go up blind alleys.

On the plus-side, compilation issues are generally pretty basic and easy to sort out. If you really have difficulties getting your code to compile then just say so in your question, pass as much detail as required, and someone will almost certainly be able to help you quite easily.

What doesn't make sense for anyone though, is to have multiple issues within a single question thread. It's like trying to pump water up to the basin in the bathroom, but with some of the pipework missing. A horrible mess, with all your water ending up in the walls rather than your basin.

The rules aren't meant to be complicated. I'm a little surprised that anyone who does IT work could find them so, but they are there to make life easier for all concerned. They're all just basic sense really, but sometimes it takes people with experience to write them down in a way that makes it easier for people to follow. Think of it this way. Every action you take that is not consistent with the rules is likely to make your question less likely to receive a helpful response quickly and easily. Follow the rules, as laid down, and you have the best chance of getting exactly that.

I hope this helps to clarify.