Toggling record sources of subforms

[Arnold]

Hi there,

I have a form with 2 subforms to organize bottles in mind. Here's some
background info:

Mainform = frmProduct, which contains fields for pricing, status, etc.
of complete bottles (bottle + cap).

The mainform has 2 independent subforms that must be visible and
side-by-side on the mainform:

sfrmBottle, which has fields for bottle dimensions, name, etc., and

sfrmCap, which has fields for bottle cap specs, name, color, etc..

Since many caps can fit 1 bottle, and many bottles can fit 1 cap, there
is a many-to-many relationship, so there are 2 main tables (tblBottles
and tblCaps) and a join table (jtblProducts).

The mainform's record source would be the join table, jtblProducts, and
the subform's record sources would be the other 2 tables. So, before a
product can be finalized, the particular bottle + cap combination must
be entered into the subforms, and then 'applied' via cmdbuttons to the
final product. The cmdbuttons will change the BottleID and CapID on
the main form to match the ID's on the subforms.

I've seen something similar to this before, but only 1 subform was
involved; the data in the subform's underlying table had to be entered
in prior to being applied to the main form's record. The main form's
record was the default record until otherwise changed by the 'apply'
cmdbuttons.

Desired functionality: I'd like to be able to toggle the filtering
(record sources) of the subforms with the mainform. Users could browse
through the various bottle and cap records in the subforms separately
until they find a combination they like, then 'apply' them both to a
final product. Users then could turn the parent/child linking back on
to continue looking through all the existing bottle + cap combinations
that have already been established on the main form, while having the
the corresponding data in the bottles and caps subforms change
accordingly as they browse.

Hope this make sense and isn't too far-fetched. Any help in
brainstorming would be greatly appreciated.

Thanks,

Arnold

 

[Reid]

Your request is not far-fetched, but I think you're making the process too
difficult. If I understand the situation, I would create an unbound master
form with a tabbed interface. One tab, named "Existing Products," would
contain frmProduct as a subform. frmProduct in turn is based on the junction
table and has two bound/child subforms, sfrmBottle and sfrmCap. Here the
user scrolls through existing products -- one of your desired objectives.

On a second tab, named "Combinations," you could put two independent,
unbound bottle and cap subforms (basically duplicates of sfrmBottle and
sfrmCap). This tab would have the "Apply" button and simply add a record to
the junction table based on the current bottle and cap in the unbound
subforms (refresh frmProduct afterward to reflect addition).

Hope this helps.

Reid

 

[Guest]

Thanks for responding Reid,

Interesting approach with the tabs. The main reason why I wasn't wanting to use tabs was because of a client request. I'm also trying to build in some image control functionality (which is probably more of a reason To Use tabs).

Let me explain...I'd like to incorporate thumbnails for all of the bottles, caps, and bottle + cap combinations (light jpegs have been created for most of the bottles, caps, and bottle + cap combinations) in the forms. In each form, there would be an image control linked to the thumbnail, and a field for the image's corresponding path.

The client would like all of the form/subform data, and corresponding thumbnails, visible all on 1 screen, not within Tab controls. Users want the main form fields accross the top of the screen, and the subforms split across the bottom. Users could navigate in and out of the subforms with cmdbuttons, and apply bottles and caps in the subforms to make a completed bottle + cap combo on top.

The trick is that users want to peruse records in the subforms independently of the main form, but also want to be able to review completed bottle + cap combinations in the mainform with the subforms linked, hence the initial question of toggling recordsources. Does that make sense. Sorry for the book.

Thanks,

Arnold

 

[Reid]

I learned some interesting things while testing on the method you suggested.
First, I found the following statement in the "LinkChildFields,
LinkMasterFields Properties" online help info:

The properties can only be set in Design view or during the Open event of a
form or report.

Sounds pretty cut-and-dry, but I found this statement to be false. I made
two subforms, Sub1 and Sub2 and added and linked them to the main form (PK =
"ID"). I found that I could easily break and restore the subform
relationships with seemingly no ill-effect. Here's how:

Break subform relationship to main form:

Me.Sub1.LinkChildFields = vbNullString
Me.Sub1.LinkMasterFields = vbNullString
Me.Sub2.LinkChildFields = vbNullString
Me.Sub2.LinkMasterFields = vbNullString

Restore subform relationship to main form:

Me.Sub1.LinkChildFields = "FK1"
Me.Sub1.LinkMasterFields = "ID"
Me.Sub2.LinkChildFields = "FK2"
Me.Sub2.LinkMasterFields = "ID"

No time left. Gotta go. HTH.

Reid Kell
(e-mail address removed)

Thanks for responding Reid,

Interesting approach with the tabs. The main reason why I wasn't wanting

to use tabs was because of a client request. I'm also trying to build in
some image control functionality (which is probably more of a reason To Use
tabs).

Let me explain...I'd like to incorporate thumbnails for all of the

bottles, caps, and bottle + cap combinations (light jpegs have been created
for most of the bottles, caps, and bottle + cap combinations) in the forms.
In each form, there would be an image control linked to the thumbnail, and a
field for the image's corresponding path.

The client would like all of the form/subform data, and corresponding

thumbnails, visible all on 1 screen, not within Tab controls. Users want
the main form fields accross the top of the screen, and the subforms split
across the bottom. Users could navigate in and out of the subforms with
cmdbuttons, and apply bottles and caps in the subforms to make a completed
bottle + cap combo on top.

The trick is that users want to peruse records in the subforms

independently of the main form, but also want to be able to review completed
bottle + cap combinations in the mainform with the subforms linked, hence
the initial question of toggling recordsources. Does that make sense.
Sorry for the book.

 

[Guest]

Thanks Reid,

I was playing around with creating 2 queries that filtered the subforms as unbound differently. I coded the link fields as you suggested, and it seems to work well. Thanks much,

Arnold

 

[Reid]

I learned some interesting things while testing on the method you suggested.
First, I found the following statement in the "LinkChildFields,
LinkMasterFields Properties" online help info:

The properties can only be set in Design view or during the Open event of a
form or report.

Sounds pretty cut-and-dry, but I found this statement to be false. I made
two subforms, Sub1 and Sub2 and added and linked them to the main form (PK =
"ID"). I found that I could easily break and restore the subform
relationships with seemingly no ill-effect. Here's how:

Break subform relationship to main form:

Me.Sub1.LinkChildFields = vbNullString
Me.Sub1.LinkMasterFields = vbNullString
Me.Sub2.LinkChildFields = vbNullString
Me.Sub2.LinkMasterFields = vbNullString

Restore subform relationship to main form:

Me.Sub1.LinkChildFields = "FK1"
Me.Sub1.LinkMasterFields = "ID"
Me.Sub2.LinkChildFields = "FK2"
Me.Sub2.LinkMasterFields = "ID"


Reid Kell
(e-mail address removed)

Thanks for responding Reid,

Interesting approach with the tabs. The main reason why I wasn't wanting

to use tabs was because of a client request. I'm also trying to build in
some image control functionality (which is probably more of a reason To Use
tabs).

Let me explain...I'd like to incorporate thumbnails for all of the

bottles, caps, and bottle + cap combinations (light jpegs have been created
for most of the bottles, caps, and bottle + cap combinations) in the forms.
In each form, there would be an image control linked to the thumbnail, and a
field for the image's corresponding path.

The client would like all of the form/subform data, and corresponding

thumbnails, visible all on 1 screen, not within Tab controls. Users want
the main form fields accross the top of the screen, and the subforms split
across the bottom. Users could navigate in and out of the subforms with
cmdbuttons, and apply bottles and caps in the subforms to make a completed
bottle + cap combo on top.

The trick is that users want to peruse records in the subforms

independently of the main form, but also want to be able to review completed
bottle + cap combinations in the mainform with the subforms linked, hence
the initial question of toggling recordsources. Does that make sense.
Sorry for the book.