Search Box Contained within Subform

[Elwood07]

Hello.
I have a main form "frmParent" with 2 subforms. One subform is an input field and the other is simply for lookup off a table, and not linked to the main form.
the lookup subform is called "sbfrmSearchAssociate" with a Search Box "txtSearch" and 2 fields called "LastName" and "FirstName". There's a 3rd field the user is actually trying to retrieve the information from.

The search box should search in LastName and FirstName for the text entered in the search box.
The command button has the following Apply Filter code in the Where Condition:

=[LastName] Like "*" & [Forms]![sbfrmSearchAssociates]![txtSearchBox] & "*" Or [FirstName] Like "*" & [Forms]![sbfrmSearchAssociates]![txtSearchBox] & "*"

and it works fine when I have the form open on it's own. When I try to use that in the subform, I've changed the code to:

[Forms]![frmParent]![sbfrmSearchAssociates].[Form]![LastName] Like "*" & [Forms]![frmParent]![sbfrmSearchAssociates].[Form]![txtSearchBox] & "*"

And for some reason it tries to filter the other subform in the main form, even thought I don't have the search subform linked to anything.


Also, it says I can only have 255 characters so I can't also include the search for the first name.
Finally, I don't even think I should be using "Apply Filter" because ie if I search for "John' it will only pull the first occurrence of the "Johnson" and doesn't allow me to filter to the next name "Johnston'.

Can anyone help me perfect this syntax? Each time I hit the button I want to cycle through my list until I get to the right record.

Thanks in advance.

[Elwood07]

basically i'm just trying to replicate the search box that it on the bottom of the form to begin with, but since my users are unfamiliar with access, i'm trying to make the search feature more obvious for them.
i just don't know the code/syntax to apply to "Search" command button and search box from a text field.
thx

[ssanfu]

I am having a hard time understanding the setup.

The command button has the following Apply Filter code in the Where Condition:

What is the complete code for the button?


Maybe post a picture of "frmParent"??


Or post your dB for analysis...

[Elwood07]

Hi Steve-

That's the whole code.
It's an embedded macro attached to the onclick event. I attached a screen shot. The search for SAP box is what I'm referring to. Instead of typing into the little search box that is part of the navigation bar of the subform, I want to insert a text box and a command button that executes the search.

Thanks.

[ssanfu]

Would have to see the macro.

Can you post the dB? (Remove sensitive data or all data, compact and zip it.)

[Elwood07]

I've attached the db. Thanks for taking a look at it.
Also if you have any suggestions on how i can get a running total on the main form of distributed gratuity amounts from the subforms i'd appreciate it. Maybe i just insert another subform query?
Thanks.


sampleService Charge Distribution Form.zip

[ssanfu]

I got the subform filter to work. You were trying to filter on a name equal to a name. I don't use macros, so I converted it to VBA code. Had to add buttons to remove the filter, goto prev record and goto next record.


Didn't look at the running sum. Try searching the forum of Google Access running sum.