Find acCmdFind records on main form via subform where record source is same table

[WallyZ]

I have split my main form fields into several sub forms with the record source being the same table.

My main form now has no control fields of its own.

Because the sub form is automatically filtered to the one record dependent on the master child link properties
performing a find via RunCommand acCmdFind will only ever be able to find that one record.

How can I make the find command perform the search on the main form based on the control of the subform?

I need to be able to use the find command to search on any selected field!

[ItsMe]

How can I make the find command perform the search on the main form based on the control of the subform?

I am thinking the only way will be to use VBA. Also, how are you managing to use Master and Child link fields when the data is in the same table? It sounds as though your pains are associated to the fact that your data is not following database Rules of Normalization.

[WallyZ]

I am thinking the only way will be to use VBA. Also, how are you managing to use Master and Child link fields when the data is in the same table? It sounds as though your pains are associated to the fact that your data is not following database Rules of Normalization.

Since you haven't seen the schema it would be difficult to make a judgement call regarding the normalization.

Also I'm not sure how the presentation layer effects the DB design?

So that everyone is on the same page I will describe the simplest example that demonstrates the issue.

tblCust
ID Auto Number
CustNo Number
CustName String
CustPhone String

Use Form Create Wizard to make a simple Tabular form.

frmCust
Record Source tblCust

Use Form Design to create an empty form call it frmMaster
make the record source tblCust
add no fields.

Drag frmCust onto frmMaster
make the master/child link = ID

Using the record selector on the master form will obviously cause the cust subform to update.

If the record selector on the cust form is left on it will be noticed that there is only ever one record regardless of how many records are in shown on the master form.

Performing a find (Ctrl-F) will always result in any other record not been able to be found. This is understandable!

Of course in this simple example there is no need to use a subform on another form as there are only three fields, why not just drop them on the master form directly!!!

However this is a simple example only. My actual DB / forms are more complex and I reuse my subform on several forms.

It works well with the only exception being finding via acCmdFind (ctrl-f) is restricted to the one currently selected record.

[ItsMe]

I have split my main form fields into several sub forms with the record source being the same table.

Since you haven't seen the schema it would be difficult to make a judgement call regarding the normalization.

I am not trying to criticize you. What you have described here seems unconventional and I apologize if your issue is not associated to your data structure.

However, I do not understand what you are trying to accomplish and, therefore, I am unable help.

[WallyZ]

Managed to figure it out and get it going. First of all here is a link to a working example.

https://1drv.ms/u/s!AgghqTS6bkpAhWUHP-jeKS9IonsB

There were two main things to get iit going:

1. The main form Find Button calls the Sub Form Find Button
2. The subForm performs the following step in this precise order:
a. Sets focus to previous control
b. Sets the recordset of the subform to be the that of the parent form (after all they are they same table)
c. Sets focus to the active control

The order of a., b. & c. is critical.