Showing ALL RELATIVE FIELDS in sub form

[mopat]

If there are two categories in a query, "people search capabilities" & "technical People" and the user enters half of the phrase into the search box, "people", Is there anyway that both of the categories appear in the subform?

As of now when i search "people" both categories show up in the Query1 window but nothing appears in the subformQuery1.

 

[vbaInet]

Have a look at this link regarding using LIKE:

http://www.techonthenet.com/sql/like.php

 

[mopat]

Thanks for the link!


I have this code within Query1 that allows for the "like" function to work. Its only when I put the sub form in that the "like" function didn't seem to work.



SELECT Expertise.ID, Expertise.FirstName, Expertise.Expertise1
FROM Expertise
WHERE (((Expertise.Expertise1) Like "*" & [Forms]![Form1]![txtmopat] & "*"));


is there anyway to make this code work for the sub form?

 

[boblarson]

Subform syntax is different. When there is a subform you have to refer to the subform CONTROL (control on the main form which houses the subform) and NOT the subform itself (unless they are named the same). You also have to use .FORM. in between that control and any property, method, or control on the subform since Access needs to know if you are wanting something from the subform control or the subform.

 

[vbaInet]

And here's a link on referring to controls:

http://www.mvps.org/access/forms/frm0031.htm

 

[boblarson]

And here's a link which might help understanding subforms.

 

[mopat]

I really appreciate the links guys, Thanks a lot!

Bob,

When you say "You also have to use .FORM. in between that control and any property, method, or control on the subform since Access needs to know if you are wanting something from the subform control or the subform." where do I enter that information and what should I include?

 

[boblarson]

For example:

[Forms]![Form1]![txtmopat]

If Form1 is the subform and the subform control is ALSO named Form1 then you can use

Forms!ParentFormName.Form1.Form.txtmopat

But if the subform control is named Child2 for example then you would use

Forms!ParentFormName.Child2.Form.txtmopat

If you have the code on the subform and you want to refer to something on the main form then, in code you can use

Me.Parent.SomeControlNameHere

Does that help at all?

 

[boblarson]

By the way this also may be a good reference for you.

 

[mopat]

Im really sorry Im not catching on,

As of now, I have the code [Forms]![Form1]![txtmopat] in my SQL.

"Form1" is in regards to the search form and my subform is called "Query1 subform" so what I did was I tried replacing "Form1" with "Query1 subform" and nothing came up.

Am I typing this code into the SQL or am I typing this somewhere else?

Sorry for the amateur questions, Im really new at this

 

[boblarson]

Okay, let's go a little slower.

You are trying to refer to the MAIN FORM text box in the SQL for the SUBFORM? You are NOT trying to refer to something on the subform. Is that correct?

 

[mopat]

As of now, Whatever I type into the MAIN FORM text box in the SQL is the primary search. I would like all the results to appear in the subform.

So for example,
If i type in "PEOPLE" into the main form text box, I would like all possible hits, "PEOPLE search" & "technical PEOPLE" to show up in the subform.

Prior to creating a subform, the results would show up in another box with all the possible hits and im wondering why its not showing all the possible hits in the subform.

Hope this helps!

 

[vbaInet]

Your subform is linked to the ID on the main form (via the Link Master Fields and Link Child Fields properties) so it will only show results "*people*" results related to that ID.

 

[mopat]

I understand but is there any way for it to show all the relative fields that pertain the word "people" in it?

Almost like a keyword search that shows all the relative items.

 

[vbaInet]

Get rid of the Link Master Fields and Link Child Fields relationships

 

[mopat]

I tried it and It didnt work.

I think its because now the subform isnt linked to anything in particular so it just shows everything. Its as good as looking at the table.

Attached is the Form im trying to build.Maybe it might be easier to figure out exactly what Im trying to accomplish if you saw it.

If there are two categories in a query, "people search capabilities" & "technical People" and the user enters half of the phrase into the search box, "people", Is there anyway that both of the categories appear in the subform?

As of now when i search "people" both categories show up in the Query1 window but nothing appears in the subformQuery1.

 

[vbaInet]

All you needed was to remove the Link Master Fields and Link Child Fields properties from the subform and requery. Or do you not know how to remove these? The db you sent still had them on.

Then you need to requery the subform. So only this line to the click event of Run Query (comment out the code you have already):

Me.Query1_subform.Requery

Edit: I've re-attached it. Have a look.

 

[mopat]

Because this is my first project using access im still new to some of the terms and tools.

So let me get this right.

1. Remove the Link Master Fields and Link Child Fields properties from the subform
2. Remove the "On Click" event for the search button & replace with:
Me.Query1_subform.Requery

and it should work?

 

[vbaInet]

Spot on mopat

I've done it anyway and re-attached it. But try yours first and see how you get on.

 

[mopat]

I just saw the re attachment and it looks great!


Is there any way so that the subform doesnt display all the items unless it has been searched?