check out my new form and help me plz :) (1 Viewer)

gemma-the-husky

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 21:39
Joined
Sep 12, 2006
Messages
15,662
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.
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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.
 
V

vbaInet

AWF VIP
Local time
Today, 21:39
Joined
Jan 22, 2010
Messages
26,374
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.
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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!
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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?
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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!!
 
boblarson

boblarson

Smeghead
Local time
Today, 13:39
Joined
Jan 12, 2001
Messages
32,059
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).
 
boblarson

boblarson

Smeghead
Local time
Today, 13:39
Joined
Jan 12, 2001
Messages
32,059
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.
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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!
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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
 
boblarson

boblarson

Smeghead
Local time
Today, 13:39
Joined
Jan 12, 2001
Messages
32,059
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.
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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...
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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...
 
boblarson

boblarson

Smeghead
Local time
Today, 13:39
Joined
Jan 12, 2001
Messages
32,059
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.
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
thank you Bob...so where do I put this debug.print line? Where does that go in the code?
 
boblarson

boblarson

Smeghead
Local time
Today, 13:39
Joined
Jan 12, 2001
Messages
32,059
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
 
H

hunterfan48

Registered User.
Local time
Today, 13:39
Joined
Aug 17, 2008
Messages
436
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
Data display question (was Me)
Replies
18
Views
426
bob fitz
bob fitz
P
A problem occurred while Microsoft Access was communicating with the OLE server or ActiveX control
2
Replies
36
Views
940
pdanes
P
Privateer
Help with my Image
Replies
3
Views
214
moke123
moke123
R
Looking to Build a basic CRM database
Replies
11
Views
784
CJ_London
C
S
Can anyone help me out with my Loan Management Database project?
Replies
4
Views
618
Pat Hartman
P

Users who are viewing this thread

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