Subforms driving Main Form through selection

[JimLee9876]

A recently re-designed MS-Access 2000 database has been broken down into several One-to-Many tables with referential integrity with the established relationships. These 'sub-tables' each have their own respective subforms, including the main table itself (all using an ID Number as the key).

What is needed is a way to allow a search by some value in a subform (an unbound combo-box query), and to choose some other record than what is currently showing. Then have the main form (and all the other subforms) automatically show this newly selected record.

Have been working on this for quite awhile without much success. Any tips, samples, and/or suggestions would be most appreciated.

 

[herbivorous]

I am not quite sure what you mean. I have a form that displays data about one of my organization's members at a time. On the same form is a subform that shows all members in alphabetical order on which one can select the member that one wants to see more info for on the main form; there is a also a search box that allows the user to start typing in the name, with the subform and the main form reflecting the closest match.

Is this the sort of thing you mean? If so, I can share the design with you.

 

[OnTheFly]

I have had to do this also. It is quite complicated but I will give you a quick overview of what I did.

I have an application that has upwards of 10 sub forms on it. What I did was to create a search form that comes up based on the double click event on a particular field in any sub form. It then brings up the form and asked for what they want to search for. From there it runs a builds and runs a query on the appropriate information from the search form and displays all the matches in a second form. From there the user can click a Go To command button that gets to the main ID. I then use the Bookmark property of the forms recordset and got to the matching main record. I them do the same with the subform that has the matching record.

I know this is complicated and the coding is quite complex but hopefully it will give you a start.