How do I open subforms with a certain criteria in the new Navigation Form

[trolleri]

There is no master/child relationships of the forms, because that does not seem to be the way the Navigation Form feature functions. I have searched all of the controls, and there seem to be no Link Master Field or Link Child Field in the Navigation Forms. This is why I ask for help to get this done by VBA-coding.




From what I have read on the web; when you click a pane/button in a Navigation Form, the control "NavigationSubform" refresh with the associated subform for that button. Thus the Navigation Form does not load all subforms when frmNavigation first opens, but only loads and resfreshes the one subform associated with the one button.


What I need the Navigation Form to do: When clicking on a button, and refreshing the control "NavigationSubform" with the appropriate subform, I need it to do the matching with the SSN value in the header of frmNavigation (the first circle in the picture above).


I need the NavigationSubform to only show the record with the correct SSN, every time I press a button in the Navigation Form.


I do notice the navigation bar in the bottom of the subform, but it is a request that end-users will not use that navigation bar. It is simply too easy to put data into the wrong record, overwriting data, if using those buttons down there. Remember I have more than 18 subforms when I am done.


I plan to make a make-new-record-button like explained here

[burrina]

I have never seen something like this before.But, I am sure it can be done. Personally I would never design something like this. Anyway, I am working on it so be patient! I should have the solution shortly!

Here is a screenshot of what it should or will look like, stay tuned!

[burrina]

Okay, check this out and see if it works for you. It is a scaled down version, but you can copy over the 2 forms and make it work. ALWAYS backup your copy first and or edit and rename!

[trolleri]

Yes your revised version do open frmVisit1Crf1 at the correct record, but you have also removed the Navigation Form!

When frmVisit1Crf1 is simply put as the only and regular subform inside frmNavigation, you had the possibility to edit the Link Master/Child fields, giving me just the same result as mentioned above (post #9).

But it is a request to have, all subforms in the same main form (frmNavigation), and then have tabs for switching between all the specific subforms. I need the correct record to show up on all subforms when I click on the respective subform navigation button. So this is sadly not the solution I was looking for.

The Navigation Form is a new feature in Access 2010 and 2013, and it is not yet well describeb on the web - at least not this specific problem.

[trolleri]

From this link:
"A possible alternative approach which might enable you to retain your current form with the navigation form would be to base each of the forms used in the navigation form on a query which references the Record Number control of your Facility Audit form as a parameter and requery each of these forms in the Current and AfterInsert event procedures of the Facility Audit form. A conventional subform is referenced via the Form property of the subform control which contains it, but, having no experience of using navigation forms, I'm afraid I don't know exactly how you would reference a form within the tabs of a navigation form."

I am beginning to think you where spot on, when you first proclaimed there must be a more easy solution ;-)

I have however worked with the Tab Control before (see this link), but dismissed that approach, because I would get too many scrollbars, and the Tab Control could not automatically resize according to the subforms different sizes.

[burrina]

In my haste and scaling it down I did lose the other subforms. If you are willing to edit the form and put them back fine. I have spent all the time I am willing to invest in this. The solution I offered works and is the root of the problem.I do not like this type of db because of just such issues. Nor do I have any more hair to pull out troubleshooting this. I wish you good luck with your project and all the best.

[burrina]

Here is my final try at helping you. Hope this works for you!

[trolleri]

This last revision has the perfect solution for my problem!
Thank you very much for all the time you spent on this project burrina!

To the rest of you reading along this thread, what burrina did was to change the record source of the subform tblVisit1Crf1 from simply "tblVisit1Crf1" to this:

SELECT tblVisit1Crf1.* FROM tblVisit1Crf1 WHERE (((tblVisit1Crf1.SSN)=[Forms]![frmNavigation].[SSN]));

In picture below I show where to paste the code. The left circle indicate how to correctly select the subform in design view, double-clicking the corner in the circle. The right circle is where you paste the code.



When I apply this change to all subforms, only editing the subform-name the three times it is mentioned, I finally got the solution I was searching for :-D

Attached is a draft including the solution given by burrina: 30 - Navigation Subforms.zip

P.S.
Last night, while is did scour the web, I found this webpage (link), informing:
"After making your navigation form, you can set the NavigationWhereClause in the button’s property pane, to make the Navigation button automatically apply a filter when the button is selected. This makes it super easy to filter an existing view without creating different forms or queries."
After that, I tried different codes, but could get any working since I am very inexperienced. There might be different solutions to this problem, but burrina gave me the one mentioned above in this post, and this one I am going to use.

[burrina]

Your very welcome. This was a tuff one!

[trolleri]

... yet the solution was quite simple and brilliant for my database :-)