check out my new form and help me plz :)

hunterfan

as an alternative to using a searchbox- especially when you want to search potentially loads of different fields - just use the binoculars icon.

click in a field - then press the binoculars, and search away.
 
gemma-the-husky said:
hunterfan

as an alternative to using a searchbox- especially when you want to search potentially loads of different fields - just use the binoculars icon.

click in a field - then press the binoculars, and search away.
Click to expand...

thanks for the thought...I'll give that a try.

On second thought, that does work, but I'd like to use a multi field search where I can just enter the criteria in a search box and it will filter all the records to show me just the ones matching the criteria.
 
hunterfan48 said:
OP???? btw I'm glad you can track time LOL
Click to expand...
I just look at the time of the post if my brain tells me this is an old thread :D

OP mean Original Post

Is SearchResults the name of a listbox? ItemData applies to listboxes and combo boxes.
 
Yes it is...and I figured it did because that's wat it highlighted when I tried using a subform (that is named SearchResults and also is based off the same query as the list box.)

So, my question in regarding to the code, what do I need to change to allow me to search my subform instead of the list box?

Here's the code that I'm using for the list box in my search form.

Option Compare Database
Private Sub SearchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = SearchFor.Text
'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll
SrchText.Value = vSearchString
'Requery the List Box to show the latest results for the text entered in Text Box SearchFor
Me.SearchResults.Requery

'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
Exit Sub
End If
'Set the focus on the first item in the list box
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of the List Box
DoCmd.Requery
'Returns the cursor to the the end of the text in Text Box SearchFor
Me.SearchFor.SetFocus
If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If
End Sub


Thanks for your help!
 
John...sorry I didn't get back to ur pm. I will check it out when I get home...thanks for your help!

Btw...your sample has only one field. How would I add it for each of the fields I want to search.

On a side note, back when I was using my list box, could I have fields that I want included in the list box but not to be searched in between the fields I was actually searching in my query. Would that cause any conflict?
 
Code: 
Private Sub Text0_Change()

    Dim strFilter As String
    
    Me.Refresh
    
    strFilter = "sport like '*" & Me.Text0 & "*'"
    strFilter = "ID like '*" & Me.Text0 & "*'"
    strFilter = "year like '*" & Me.Text0 & "*'"
    strFilter = "manufacturer like '*" & Me.Text0 & "*'"
    strFilter = "set like '*" & Me.Text0 & "*'"
    strFilter = "player name like '*" & Me.Text0 & "*'"
    strFilter = "card type like '*" & Me.Text0 & "*'"
    strFilter = "card number like '*" & Me.Text0 & "*'"
    strFilter = "condition like '*" & Me.Text0 & "*'"
    strFilter = "team like '*" & Me.Text0 & "*'"
    
    Forms!Form4!frmSubform.Form.Filter = strFilter
    Forms!Form4!frmSubform.Form.FilterOn = True
    
    Me.Text0.SelStart = Len(Me.Text0)
    
    
End Sub

Ok, so I got it to work partially. LOL Like you mentioned in your sample John, it was just for one field. Well I would like it to search more than one, but it's causing me a few problems. What and where would I put the extra code to be able to do that?

As of right now, when I type in the search box it narrows it down to nothing. As well, when I try using a space in my text box (i.e., Joe Montana) it won't let me...it keeps on writing as such (JoeMontana)

And last, I need to add the code to deal with nulls. Where can I find that?

Thanks for all the help guys!!
 
1. You have code which is causing problems for having spaces in the text box. It is this code right here:

Code: 
Me.Text0.SelStart = Len(Me.Text0)


2. I would rewrite the code like this (although I wouldn't use the same box for everything) and I'm assuming that the subform is on the same form as the text box for searching:
Code: 
Dim strFilter As String
 
   strFilter = "[sport] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[ID] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[year] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[manufacturer] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[set] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[player] name like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[card type] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[card number] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[condition] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[team] like '*" & Me.Text0 & "*'"
    
    Me.frmSubform.Form.Filter = strFilter
    Me.frmSubform.Form.FilterOn = True

Now, notice that you have to use OR for everything in order to have something show up. Also, your field names need to be in brackets (or at least the ones that are Access Reserved Words like Year and Set and also any with spaces in the names).
 
Also, is Year a text or numeric field? Same with Card Number and card type and ID, etc. Any of them that are NOT text fields you don't want the single quotes.
 
Bob,
Thank you so much! I haven't had time to work on it yet, but I'll get back to you with the results! Again, thank you for your help!
 
A few things...
ID is the only one that isn't a text field...I just removed it from the code.

I'm able to type, but nothing will query...it all remains the same as I type. Please instruct...thanks!

Code: 
Option Compare Database
Private Sub Text0_Change()

    Dim strFilter As String
 
    strFilter = "[sport] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[year] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[manufacturer] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[set] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[player name] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[card type] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[card number] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[condition] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[team] like '*" & Me.Text0 & "*'"
    
    Forms!Form4!frmSubform.Form.Filter = strFilter
    Forms!Form4!frmSubform.Form.FilterOn = True
End Sub
 
Put a debug.print in your code just after the final line which gets concatenated:

Debug.Print strFilter


so then you can go look at it and see if the filter is building the string correctly.
 
boblarson said:
Put a debug.print in your code just after the final line which gets concatenated:

Debug.Print strFilter


so then you can go look at it and see if the filter is building the string correctly.
Click to expand...

Which final line do you mean?

This one>>>Forms!Form4!frmSubform.Form.FilterOn = True

Or is it this one shown below??

strFilter = strFilter & "[team] like '*" & Me.Text0 & "*'"

What should I look for to see if it's building correctly? I'm afraid that I won't have any idea what that means...
 
hunterfan48 said:
A few things...
ID is the only one that isn't a text field...I just removed it from the code.

I'm able to type, but nothing will query...it all remains the same as I type. Please instruct...thanks!

Code: 
Option Compare Database
Private Sub Text0_Change()
 
    Dim strFilter As String
 
    strFilter = "[sport] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[year] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[manufacturer] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[set] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[player name] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[card type] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[card number] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[condition] like '*" & Me.Text0 & "*' OR "
    strFilter = strFilter & "[team] like '*" & Me.Text0 & "*'"
 
    Forms!Form4!frmSubform.Form.Filter = strFilter
    Forms!Form4!frmSubform.Form.FilterOn = True
End Sub
Click to expand...


btw...do I need to change the code for [sport] above? Why does it not have another strFilter afer the (= symbol) shown for set, player name, card type, etc. below that one?

Thanks...
 
hunterfan48 said:
btw...do I need to change the code for [sport] above? Why does it not have another strFilter afer the (= symbol) shown for set, player name, card type, etc. below that one?
Click to expand...
It doesn't need it because it is the very first line and there is no other line of strFilter to concatenate on. Remember why you are doing that in the lines following. You are adding on more to the line and the only lines that need to retain the strFilter part are any lines past the first line of code.
 
thank you Bob...so where do I put this debug.print line? Where does that go in the code?
 
Anywhere after the code is fine but I would usually put it between these two lines:

Code: 
    strFilter = strFilter & "[team] like '*" & Me.Text0 & "*'"
 
[COLOR=red]    Debug.Print strFilter[/COLOR]

    Forms!Form4!frmSubform.Form.Filter = strFilter
 
this is going to sound really stupid...but I inserted the line, but I don't know what to do next?

I clicked 'Debug', then 'Compile' n it doesn't do anything. lol

Sorry I'm so naive...I'm a total beginner so I appreciate the help. Please clarify what i do from here...thanks!
 
You must log in or register to reply here.

Similar threads

B
Expanding related tables and splitting database
Replies
10
Views
362
Pat Hartman
P
P
Solved Trouble creating a filter on a form displaying records built in a query
Replies
4
Views
461
p_bij
P
I
Combine and sum data from a form to a report
Replies
1
Views
143
pbaldy
pbaldy
G
Again, query where dealing with empty fields
Replies
13
Views
444
theDBguy
theDBguy
M
Solved Form Filter and RecordSource Don't Query New Record
Replies
13
Views
650
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom