How do i search a subform from the Mainform?

[DavidG]

Hi, I was just wondering how to go about searching for a certain parameter
within a subform using a control on the mainform? I basically want to be able
to have the same search features as the "find next" command, i.e. when the
search name is matched the record containing the match is displayed in full

Filters tend to remove the other information from the record (i have
multiple contact names and details for each record) but im still wondering if
there is a way to acheive the desired outcome using filters. The main problem
is being able to use the control on the mainform as my subform has to be
displayed in tabular form.

Any help would be greatly appreciated.

Thanks,

Dave.

 

[Jack Leach]

Are you looking for a way to reference the subform's properties?

For instance, say on the mainform you have a text box named txtSearchSub,
and you want to filter the records of the subform based on it...


Dim strFilter As String
strFilter = "ThisField = """ & Me.txtSearchSub & """"
Me.SubformControlName.Form.Filter = strFilter
Me.SubformControlName.Form.FilterOn = True


note that the name of the control you need to reference is not the subform's
name... rather it is the name of the control that contains the subform.

hth

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Allen Browne]

Dave, take a look at this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

There's a sample database you can download (free), and pull apart to see how
it works. Essentially, it's a continuous form with unbound boxes in the Form
Header section where you enter your filter values, and it displays the
results (one per row) in the continuous form.

It is possible to use a subform, but it's not really necessary. If you had a
subform control named Child1, you would set its filter with code like this:
Me.[Child1].Form.Filter = strWhere
Me.[Child1].Form.FilterOn = True

 

[DavidG]

Thanks for the repsonses guys.

Jack, i tried your code but im not sure if i have it working properly at the
moment. It seems to sort of work but still only searches the current record.
I think im using the wrong subform control name. I am new to access and not
100% sure what this might be. You say it is not supposed to be the subforms
name but rather its the name of the control that conatins the subform. Do you
mean the name of the main form that contains the subform?

Allen, thanks for pointing me towards your example. Your website has been
very useful for setting up my data base thus far. Unfortunately in this case
the way my form is set up means that the given example would be an unsuitable
way of filtering my data as users need to be able to enter as well as
retrieve data from the same form.

Basically, my subform is linked to a table named "contacts" and users can
input data to the subform who's info is subsequently stored in this table.
The subform is in the "details" section of my mainform and stores contact
details such as names and phone numbers specific to individual workers at a
particular company.

In the mainform header i have a seperate group of parameters whos details
are stored in a seperate table named "customers". This stores details
relevant to all workers at that company and other info such as delivery
adress etc. So essentially for each record, i have a mainform header that
provides general info for that company, and then a subform that displays
contact details for workers at that company. The contacts details and the
company details are linked using a "Customer_ID" key.

So as you can see i am in a pickle. I need to be able to use the same form
to input my data, but also search using a control on the mainform header. Its
difficult to explain but essentially, the find next function does exactly
what i want, but it will only search the current record (i.e. only the
contacts with the Customer_ID number specific to that record) and i cannot
find a way to configure my filters to do what i want.

Thanks for helping me out thus far though. I am willing to try anything
suggested and am determined to get this to work.

Cheers,

Dave.

Dave, take a look at this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

There's a sample database you can download (free), and pull apart to see how
it works. Essentially, it's a continuous form with unbound boxes in the Form
Header section where you enter your filter values, and it displays the
results (one per row) in the continuous form.

It is possible to use a subform, but it's not really necessary. If you had a
subform control named Child1, you would set its filter with code like this:
Me.[Child1].Form.Filter = strWhere
Me.[Child1].Form.FilterOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi, I was just wondering how to go about searching for a certain parameter
within a subform using a control on the mainform? I basically want to be
able
to have the same search features as the "find next" command, i.e. when the
search name is matched the record containing the match is displayed in
full

Filters tend to remove the other information from the record (i have
multiple contact names and details for each record) but im still wondering
if
there is a way to acheive the desired outcome using filters. The main
problem
is being able to use the control on the mainform as my subform has to be
displayed in tabular form.

Any help would be greatly appreciated.

Thanks,

Dave.

.

 

[Allen Browne]

Responses in-line

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the repsonses guys.

Jack, i tried your code but im not sure if i have it working properly at
the
moment. It seems to sort of work but still only searches the current
record.
I think im using the wrong subform control name. I am new to access and
not
100% sure what this might be. You say it is not supposed to be the
subforms
name but rather its the name of the control that conatins the subform. Do
you
mean the name of the main form that contains the subform?

Open the main form in design view.
Right-click the edge of the subform control, and choose Properties.
Look at the Name property ("Other" tab.)

The subform control's name is not necessarily the same as the name of the
form it contains (its Source Object.)

Allen, thanks for pointing me towards your example. Your website has been
very useful for setting up my data base thus far. Unfortunately in this
case
the way my form is set up means that the given example would be an
unsuitable
way of filtering my data as users need to be able to enter as well as
retrieve data from the same form.

There's no reason why you can't use this kind of filtering on any form. I
regularly use it in the Form Header of forms in Form view as well as
Continuous view, and you can edit the record in the filtered form.

 

[DavidG]

Hi Allen,

Just stepping through the code etc to see how i can possibly adapt for my
application, but i keep getting an error message when i run the search
function.

"Run-time error '438':
Object doesnt support this property or method.

When i de-bug it takes me to Line 25 "If Not IsNull(Me.txtFilterCity) Then"

Not too sure whats going on here, I'm using Access 2003 if that makes any
difference.

Thanks for your help thus far. Ive started trying to adapt your code for my
application so im sure ill have a few questions once i start trying to de-bug
.

Cheers,

Dave.

 

[Allen Browne]

The code assumes an unbound text box named txtFilterCity.
Sounds like you don't have that object.

With the form open (not design view), open the Immediate Window (Ctrl+G),
and enter something like this:
? TypeName(Forms![YourFormNameHere]!txtFilterCity)
It should respond with Textbox. Then try:
? TypeName(Forms![YourFormNameHere]!txtFilterCity.Value)
It should respond with Null, or whatever's in the box.

If it correctly returns text box but can't find the value, and there are NO
records in the form and no new records can be added, you may have struck
this bug in Access:
http://allenbrowne.com/bug-06.html