Filter a subform...

[Inquisitor99]

I have a main form with a linked subform. I have a text box on the main form allowing the user to enter data that they want to use to filter the form.

This works fine when the field being filtered is on the main form. It does not work at all when the field being filtered is on the subform.

How do I filter the subform, so that both the main form and the subform move to the corresponding record and remain synchronized?

Example:

To look up an ID# located on the MAIN form, I use the following, which works fine:

Me.Filter = "[ID#]=" & Me!LookupTextBox
Me.FilterOn = True

To look up a name on the subform, for example, I tried to use the following, which does not work:

Me.[mySubform].Form.Filter = "[Name]=" & Me!LookupTextBox
Me.[mySubform].Form.FilterOn = True

I want the subform to move to the record containing the 'Name' specified, and I want the main form to also move its pointer to the correct record to remain synchronized with the subform.

I now see why it does not work, because the subform will always have just the one record that is linked to the main form.

How can I work around this? Does anybody have any ideas?

Thanks.

 

[TheAceMan1]

How are ya Inquisitor99 . . .

In Access, linked [blue]Child Forms[/blue] can not/should not control [blue]Parent Forms![/blue] Change a record on the parent and the link forces the child to synchronize!

Hmmmmmmm . . . are the form/subform linked?

See Ya! . . . . . .

 

[Inquisitor99]

So AceMan, I understand your point that the subform should not control the Parent. I don't really understand what you mean by changing a record on the Parent.

Are you saying if I want to lookup a 'Name' located in the subform's recordset, I have to add the 'Name' field to the Parent's recordset? Please explain.

Thanks.

 

[TheAceMan1]

Inquisitor99 . . .

Sorry I wasn't clear, but I may have to modify my answer according to my prior question:

[blue]are the form/subform linked?[/blue]

That is, do the [blue]Master/Child Field[/blue] properties of the subform have values?

See Ya! . . . . . .

 

[Inquisitor99]

Yes they are linked.

 

[TheAceMan1]

Roger That Inquisitor99 . . .

Then its like I said:

[blue]Change/goto a record on the parent and the link forces the child to synchronize![/blue]

Meaning if you change record to be displayed on the mainform thru the subform, the subform will most likely change again when the mainform changes . . . due to the link!

Is this anymore apparent?

See Ya! . . . . . .

 

[Inquisitor99]

AceMan, I think I now understand what you are saying - but that does not work for what I am attempting to accomplish.

The 'Name' field is not listed in the recordsource of the main form - there is only an 'ID#' field in the recordsource of the main form. When the user wishes to lookup a record by the 'ID#', the lookup works fine - the main form changes and the subform synchronizes along with it.

If they want to lookup by 'Name', which is only listed in the recordsource of the subform, it does not work.

I wanted to know if there is a way to navigate both the main form and subform to the record containing the 'Name', eventhough the 'Name' is not listed in the recordsource of the main form.

 

[yetanotherjo]

Can you add Name to the main form?

 

[TheAceMan1]

Inquisitor99 . . .

All well & good! . . . But if there's no relationship in your tables (or form & subform) between [blue]ID# & Name[/blue] . . . I don't see how this can be accomplished . . .

Your thoughts!

See Ya! . . . . . .

 

[Inquisitor99]

I just came up with a possible solution, but I'm not sure if it will work.

I'm going to try to do a 'Find' for the 'Name' on the recordset for the recordsource of the subform, then use the ID# of the found record to pull up the corresponding record on the main form, which will in turn pull up the correct subform record.

Does that sound like it might work - or is that just totally wrong?

 

[Inquisitor99]

Bad news for me...

I tested my idea, and it works fine - but only if there is just one matching record. It does not work when there are multiple records that match the 'Name', and I do need to filter the main form to return as many records as match the search text.

 

[TheAceMan1]

Inquisitor99 . . .

Yes thats a viable soultion. It in fact answers my question about any relationship between [blue]ID# & Name![/blue]

Since the above is true . . . I would have a combobox on my mainform from which I could [blue]select the names on the subform[/blue] and goto the proper ID# on the mainform (ID# & Name in the same combobox). . .

See Ya! . . . . . .

 

[Inquisitor99]

Aceman, I didn't think of putting a combobox on the main form, but I think I will end up doing it that way, as I can't think of any other solutions.

Thanks All!