check out my new form and help me plz :) (2 Viewers)

[boblarson]

You run your procedure just like normal. But then you look in the IMMEDIATE WINDOW (see screenshot below as to what is the Immediate Window) for the output.

 

[hunterfan48]

thank you!

I'm not sure what to look for, but I'll go ahead and give it a try. Hopefully I can figure out what's going wrong.

Thanks again Bob!

 

[hunterfan48]

Sorry Bob...I'm completely lost. I have no idea where to go from here. I have my code up in the regular window, but I'm not sure what to exactly put in the immediate window.

Again, I REITERATE I'm a beginner to this VBA code and I'm sorry for not being able to understand.

If you have the patience, please show me what I need to put where and explain why I am doing that so that I can catch on.

Your help has helped a lot so far...I just need some further clarification on what I'm doing.

Thanks,
Brady

 

[boblarson]

1. You do not put anything in the immediate window.

2. You put the

Debug.Print strFilter

in the code just after the point where the strFilter is fully built.

3. Then you just run your code as normal.

4. When the code errors out, just stop it and then go look at the Immediate Window and you should see the entire string that was built by the code. Copy it and paste it here on the forum.

Does that help?

 

[hunterfan48]

Yes. But in reference to point #3, How do I run my code? When I click Run, or click the green arrow, or push (F5) is that what you are referring to???

If so, it brings up a menu for macros. Would I create a macro here? Or am I pressing the wrong button? I don't think I should be getting a popup for macros should I???

Thanks,
Brady

UPDATE

I decided to follow it and just see what would happen. I created a macro, which opened up a new window. I deleted everything in that window and pasted the code I've been working on. Then I clicked the Green Arrow, (F5), and it brought up a alert message that said the following:

Compile Error:
Invalid use of Me keyword

Does this help?

It marked it in this first line of code

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

btw...none of this showed up in my immediate window.

 

[boblarson]

You don't need a macro to run it.

C'mon - think about it. Where is your search code? Don't you have it set up so that you type something into the text box it is doing the search? So, wouldn't it be logical, that to run it, you would open the form up and start typing something into the text box? THAT is how you run it. Just like you would be using the program.

 

[hunterfan48]

Bob...thank you again.

Here is what I came up with.

[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'
[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'
[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'
[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'
[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'
[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'
[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'
[sport] like '**' OR [year] like '**' OR [manufacturer] like '**' OR [set] like '**' OR [player name] like '**' OR [card type] like '**' OR [card number] like '**' OR [condition] like '**' OR [team] like '**'

 

[boblarson]

So the question is - where is the typing values that you have. So, because you have this in the change event of the text box, you need to change your code to refer to the .TEXT (this is one of the RARE times you use .Text and not .Value because the text box hasn't gone through its update events yet). So, your code should look like this instead:

Private Sub Text0_Change()

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

 

[hunterfan48]

When I enter that code and try running it, I'm able to type and it starts to filter. However, after just three letters it filters it down to nothing. It does this on every word I start to type in. I also added the brackets because I thought that might be the problem, but it wasn't.

Also, I'm not able to type spaces in between the words in my search text box anymore. I figure it was due to this...

Me.Text0.SelStart = Len(Me.Text0.Text)

But, after removing it now it won't let me type anything after the 1st letter. It just changes it.

Here's my latest code...

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

 

[boblarson]

How about posting a copy of the database so we can play with it?

 

[hunterfan48]

Sure, I can do that. My database is way too big...even for zipping to upload on here. John pointed out some tips for zipping it down.

Is there an easy way to make a copy of my database and take some stuff out so I can upload it here?

Thanks for being so helpful and wanting to work with me on it...you have been a great help!

 

[boblarson]

First off for the copy to upload - make sure there are no pictures embedded in the forms, reports or stored in the tables.

Second, remove any forms or reports that are not part of the search form.

Third, run Compact and Repair

Fourth, Zip the file

 

[hunterfan48]

Is there an easy way to get rid of pictures in the form, report, or tables? Or do I need to manually go through and delete them. For example, I'd go into my table and filter it to those that have an attachment and delete those correct??

Is there an easier way that I'm missing out on?

The rest I will do no problem...

 

[boblarson]

It would be a manual process.

 

[hunterfan48]

The form is 'Form4'

I deleted a bunch of stuff from this extra copy so I could get this uploaded. Thanks...

 

[boblarson]

You're gonna laugh when you see the fix. I had to laugh myself because I just didn't see it before (and I looked at it from your post quite a bit). The working code needs to be:

Private Sub Text0_Change()
    Dim strFilter As String
 
 
    strFilter = "[sport] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[ID] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[year] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[manufacturer] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[set] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[player name] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[card type] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[card number] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[condition] like '*" & Me.Text0.Text & "*' [COLOR=red]OR "[/COLOR]
    strFilter = [COLOR=red]strFilter &[/COLOR] "[team] like '*" & Me.Text0.Text & "*' "
 
    Forms!Form4!frmSubform.Form.Filter = strFilter
    Forms!Form4!frmSubform.Form.FilterOn = True
 
    Me.Text0.SelStart = Len(Me.Text0.Text)
 
End Sub

 

[hunterfan48]

After all the work I made you do, I know now why you made me go to the work of uploading a copy! Hahaha LOL o well it was a good learning lesson!! Thank you Bob so much for your help!

Anyways, I haven't had a chance to try it yet, but am I'm gonna be able to enter spaces with this code still in there??

Me.Text0.SelStart = Len(Me.Text0.Text)

If I search for Michael Jordan, I want it to let me put a space between Michael and Jordan.


Thanks for all your help!

 

[hunterfan48]

Just tried it and it works!! Hahah Yes!

Another quick question if you don't mind.

I can search one field great, but let's say I wanted to search two or more fields. Instead of just searching for 'Minnesota Vikings' cards, I want to search for 'Minnesota Vikings 2002' cards meaning Vikings cards from the year 2002.

Would this take a lot more work in the code to enhance my search? A simple workaround would be to search one field, and then do a filter from within the subform, which was the reason why I wanted a subform

Thanks again Bob...you were of great help!

 

[boblarson]

Remove the

Me.Refresh

it isn't needed

and then remove this:

Me.Text0.SelStart = Len(Me.Text0.Text)

as it isn't needed if you remove the me.refresh which isn't needed.

 

[boblarson]

Just tried it and it works!! Hahah Yes!

Another quick question if you don't mind.

I can search one field great, but let's say I wanted to search two or more fields. Instead of just searching for 'Minnesota Vikings' cards, I want to search for 'Minnesota Vikings 2002' cards meaning Vikings cards from the year 2002.

Would this take a lot more work in the code to enhance my search?

Not possible with what you have. You would be venturing into Google type territory here. So, either provide a search on different fields or accept the partial which you have here.