Apply Filter on a Subform

[tssparky]

Hello all, needing some help on this please.


I made a form based on a products table query. Made a searchbox and button, works great.
code for the search function is as follows.


Private Sub Search_btn_Click()

DoCmd.ApplyFilter "", "[PartNumber1] Like ""*"" & Forms![OrderForm_F]![Small_Part_Search_F].Form![SearchBox] & ""*"" Or [PartNumber2] Like ""*"" & Forms![OrderForm_F]![Small_Part_Search_F].Form![SearchBox] & ""*"" "

End Sub


This works fine on its own.


If I add this form to another form, so this form becomes the subform, it all falls apart from here.


I have tried to use a button and textbox on the main form, no luck. Also tried this piece of code, but all it does is replace the Value in the table rather than filtering it.


Public Sub Search_btn2_Click()

Dim strSQL As String

strSQL = "Select * from Supplier_Products_Q where"
strSQL = strSQL & Forms![OrderForm_F].Form![Small_Part_Search_F]![PartNumber] = " & Me!Search_txt2"
Forms![OrderForm_F].Form![Small_Part_Search_F]![PartNumber] = strSQL
Me.RecordSource = strSQL




I've been struggling with this for weeks, driving me nuts. I had a look at access.mvps.org/access/forms/frm0023.htm to get this code.




So to sum up..


I have a mainform with a subform. Subform has an apply filter command on a button click.


There is no data link between Mainform and Subform.


What i'm trying to do is be able to search for a product and use the "ID" to manually add to an order on another subform on same page.


I read that you cant use the 'ApplyFilter' command on a subform.






Can Anyone Please help?
I hope you understand what I mean.:banghead:


Thanks


Nick

 

[GinaWhipp]

Not sure I understand completely but if all you trying to do is a create a look-up for easy data entry then why not just use a pop-up? Then the User can click a button and send it back to the control you are trying to populate. Or did I misunderstand?

 

[tssparky]

Yeah I guess I could do that, 1 was just hoping to have it all on one form. I was going to use 'tabs' but had the same 'mainform'/'subform' issue when trying to filter the subform.

 

[tssparky]

Here is a pic of the form if that helps. not formatted yet, just trying to get it to function first.

 

[GinaWhipp]

Wow, that is a large picture, good thing I have a large monitor!

With all that is going on with that Form (and Subform) I would opt for a pop-up. You have to think of your Users and you really don't want them scanning all over the place for information when you can give them what they need right in front of them. Sometimes, less is more!

 

[JHB]

Let us stick to the filter, (check if the subform actually is called "Small_Part_Search_F" in the form, also check that "PartNumber" is the correct name, then I can see you called it 3 different names in your code, "PartNumber", "PartNumber1", "PartNumber2")

Private Sub Search_btn2_Click()
   Me.[Small_Part_Search_F].Form.Filter ="[PartNumber] = '" & Me!Search_txt2 & "'"
   Me.[Small_Part_Search_F].Form.FilterOn = True
End Sub

 

[tssparky]

haha, sorry, just wanted to make sure you could read it.... You should see my actual search form or customer form.


This isn't going to be what the end user sees, just trying to get it to function as intended.



When on "My Customer Form" you can click to add a purchase order, parsing the customer details to the "OrderForm" [MainForm]
I can then select an item on the order subform but it needs a part "id" to find the part i'm trying to order. In order to find the ID out of a list of 200'000 plus parts, a search form is needed to find the result im after.



If I could have the search function in a popup, how can i add multiple parts to the subform? So if for example, I search for a part and click "add to order", then do another search and click "add to order" again to add the second part?
I could probably work out how to parse the one record but my skills will fall over trying to add part 1, then a search, then add part 2


Sorry if this is confusing, its confusing me, that I can tell you, haha.


Thanks

 

[tssparky]

[SOLVED] Re: Apply Filter on a Subform

Let us stick to the filter, (check if the subform actually is called "Small_Part_Search_F" in the form, also check that "PartNumber" is the correct name, then I can see you called it 3 different names in your code, "PartNumber", "PartNumber1", "PartNumber2")

Private Sub Search_btn2_Click()
   Me.[Small_Part_Search_F].Form.Filter ="[PartNumber] = '" & Me!Search_txt2 & "'"
   Me.[Small_Part_Search_F].Form.FilterOn = True
End Sub

Worked Great, thanks so much JHB, you are a legend.........

 

[JHB]

You're welcome, good luck.

 

[tssparky]

Is there a chance you can help me with the wildcard syntax. What I have done is wrong and doesn't work.
Guessing something like, but not...

Me.[Small_Part_Search_F].Form.Filter = "[PartNumber] =(Like ""*"" & Me.Search_txt2 & ""*"")"


Or
Me.[Small_Part_Search_F].Form.Filter = "[PartNumber] ='Like" & "'*" & Me.Search_txt2 & "*'"


I just don't know.. Very frustrating to be teaching myself without any local help. Got so much done but so much to still do......


Thanks again

 

[JHB]

I think that is correct, (not tested):

Me.[Small_Part_Search_F].Form.Filter = "[PartNumber] =Like '*" & Me.Search_txt2 & "*'"

 

[tssparky]

Says




Run-Time error '3075'
(Missing Operator) in Query Expression '[PartNumber]=Like'*ovbl*"

 

[JHB]

Have you copy my code line or have you type it in, (then I see it is missing a space after the like and missing a ' after the last *)?
If you can't get it post your database with some sample data, zip it then you haven't post 10 post yet.

 

[tssparky]

Yeah I copied and pasted



I made a small Db to demonstrate in attachment..



Search for either b,c,d,e,f,g


have commented out the working filter, as you will see.


Thanks again for your time.. I very much appreciate it..


Nick

 

[isladogs]

Remove the = sign before Like

Me.[Subform].Form.Filter = "[PartNumber] Like '*" & Me.Search_txt & "*'"

 

[tssparky]

Remove the = sign before Like

e.[Subform].Form.Filter = "[PartNumber] Like '*" & Me.Search_txt & "*'"

Thank you
Thank you
Thank you


Thought it was just a simple error, simple when you know anyway

Nick

 

[isladogs]

You're welcome (x3)

 

[Rvishakya]

Let us stick to the filter, (check if the subform actually is called "Small_Part_Search_F" in the form, also check that "PartNumber" is the correct name, then I can see you called it 3 different names in your code, "PartNumber", "PartNumber1", "PartNumber2")

Private Sub Search_btn2_Click()
   Me.[Small_Part_Search_F].Form.Filter ="[PartNumber] = '" & Me!Search_txt2 & "'"
   Me.[Small_Part_Search_F].Form.FilterOn = True
End Sub

Thx. It help me a lot