Subform Based on a Junction Table

[Jenaccess]

Hi,

I'm working with Access 2007. I've designed a subform based on a junction table. The subform is not embedded on the main form. It's called from a command button on the main form. I put some VBA code in the command button that links the record, so that the main and subform show the same record.

My problem is I can't get the subform to update with the foreign key in the junction table. When I embed the subform this is not a problem, everything populates correctly. However, I'd like to embed as little as possible, in order to keep the main form clean (I have quite a few subforms). I know I could use tabs, but would prefer to have the subforms non-embedded if I can. Is what I'd like to do possible, or do I need to embed? Thank you!

 

[John Big Booty]

Instead of a pop up window, you could embed your sub-forms and show/hide them as required on the click of a button as required, this would overcome your current problem and keep you main form relatively clean.

Your other option is to use the OpenArgs of your DoCmd.OpenForm to pass the appropriate ID to your pop up form so that it can be saved in your new record.

This post has some sample code for using OpenArgs.

 

[Jenaccess]

John,

I can't thank you enough. The advice you gave me about OpenArgs just solved a problem I've had for years. If you wouldn't mind giving me one more piece of advice I'd really appreciate it.

My subform is now populating with the foreign key the way it's suppsed to. I have a link criteria in the command button calling the subform, so the records are matched, and I use the On Load OpenArgs to populate that foreign key. The only issue I'm having is that it works great when I put the first record in the subform (it's a continuous subform), but when I add an additional record, the foreign key does not populate. Is there an additional step I should take in either the command button or the OpenArgs?

Again, thank you so much!

 

[John Big Booty]

You could use the Sub-Form's On Current event and test if this is a new record and if so insert the OpenArgs in the appropriate control.

 

[Jenaccess]

That worked perfectly. Thank you so much for your help!

 

[John Big Booty]

Happy to have been able to help