Position subform control in relation to another

[lynxsta911 via AccessMonster.com]

Hello all ...

I have a main form (frmOrderEntry) which contains 13 subform controls
(ctrOptionEntry1, ctrOptionEntry2, ...) and 14 command buttons. Each command
button changes the RecordSource of each subform. (It runs like a tab control,
but without having to place 13 subforms on each tab.)

Each subform control alternates between a continuous form and a single form,
depending on which RecordSource has been selected. As each subform is
identical (except for DefaultView), all I want to do at this point is change
the height of each subform control depending on whether it's SourceObject is
a continuous or single form. But this would cause some subform controls to
overlap, so I would like to adjust each subform control's positioning to be
in relation to the subform control above it. Is this even possible?

Following is the code that triggers the above events... (OptionTab references
a category which used to be on a Tab Control, but is now done by the command
buttons)

Public Sub FilterSubForm(ctrOptionTab As Control, ctrCategoryNum As Control,
ctrOptionEntryNum As Control, strSubForm As String, strSubFormcont As String)

'Sets each subform as single form or continuous, based on SingleSelect
If (DLookup("SingleSelect", "tblCategory", "CategoryID = " &
ctrCategoryNum)) = -1 Then
ctrOptionEntryNum.SourceObject = strSubForm
Else
ctrOptionEntryNum.SourceObject = strSubFormcont
End If

'Toggles between OptionalPricing or not, depending on togOptionalPricing
Dim strSubFilter1 As String
Dim strSubFilter2 As String
Dim strNum As String

If Forms!frmOrderEntry!togOptionalPricing = True Then
strNum = " = -1"
Else
strNum = " <> -1"
End If

'Filters each subform by the button that was selected
strSubFilter1 = "" & _
"SELECT tblOptionEntry.OptionID, tblOptionEntry.OrderID,
tblOptionEntry.Option, tblOptionEntry.Quantity, " & _
"tblOptionEntry.Price, tblOptionEntry.Total, tblOptionEntry.
OptionText, tblOptionEntry.OptionTab, tblOptionEntry.Category, " & _
"tblOptionEntry.OptionalPricing " & _
"FROM tblOptionEntry " & _
"WHERE tblOptionEntry.OptionTab = " & ctrOptionTab & _
" AND tblOptionEntry.Category = " & ctrCategoryNum

strSubFilter2 = " AND tblOptionEntry.OptionalPricing" & strNum

ctrOptionEntryNum.Form.RecordSource = strSubFilter1 & strSubFilter2


End Sub

Any help is greatly appreciated!
Sorry if my code is junk ... I'm a learn-as-I-go VB'er.
Lynx

 

[lynxsta911 via AccessMonster.com]

I just tried something, and I'm not sure what it's doing. I added some height
specifications into my single/continuous form "If" statement (code below).
The subform controls appear to be changing height as I need, and they are
adjusting their position accordingly (I have them bound in a stacked layout
format), but they are showing as empty boxes ...

'Sets each subform as single form or continuous, based on SingleSelect
If (DLookup("SingleSelect", "tblCategory", "CategoryID = " &
ctrCategoryNum)) = -1 Then
ctrOptionEntryNum.SourceObject = strSubForm
ctrOptionEntryNum.Height = 864
Else
ctrOptionEntryNum.SourceObject = strSubFormcont
ctrOptionEntryNum.Height = 1728
End If

Lynx

 

[lynxsta911 via AccessMonster.com]

So, nobody's listening

I really do appreciate everybody's info on this site. Very helpful.

I've tried running this code again, and it's working this time! I think it
must have been running slow on my memory stick and freezing up. But if anyone
is perchance reading this ... any suggestions would be greatly appreciated!
BTW, I'm running wonderful Access 2007 :-S

 

[Marshall Barton]

I've tried running this code again, and it's working this time! I think it
must have been running slow on my memory stick and freezing up. But if anyone
is perchance reading this ... any suggestions would be greatly appreciated!
BTW, I'm running wonderful Access 2007 :-S

I don't have a grip on what you are tring to do, but I can't
see where you are adjusting the subform control's Top
property to reposition them.

Another issue you need to keep an eye on is when you change
a subform control's SourceObject or a form's RecordSource,
Access may be overly helpful and use the common field name
associations to reset the Link Master/Child properties.
This may account for why you subforms are blank so put an
appropriate MsgBox, breakpoint or Debug.Print to check these
properties. If they are being reset, you need more code to
put them right again.

 

[lynxsta911 via AccessMonster.com]

Thanks, Marshall. Honestly, sometimes I have no idea what I'm trying to do
with this thing either! I'll try to be brief in explaining ...

My main form contains 13 subforms. With the 15 buttons I change the "WHERE"
criteria for the subform recordsources (trying to avoid using 102 subforms
here). Where I need to select more than one option, it changes the source
object to a continuous form. Sometimes the recordsource is null, then it
hides the subform. It's working well for now ...

I never did put in any code to adjust the Top property. In the form design
view I have the subforms bound within a stacked layout guide (might be new in
Access 2007?) which seems to be adjusting the position of the subforms
accordingly, keeping them the same distance apart. I did not expect that,
thus my first post.

Before I added those two lines "ctrOptionEntryNum.Height = 1728" and
"ctrOptionEntryNum.Height = 864", I didn't have a problem with the subforms
showing blank. And I haven't again since I did a compact/restore. I've been
running this thing off of a memory stick so could it have just been running
slow?

As for the Link Master/Child properties, if they are the same for all the
subforms could they still be reset? Every subform is identical (except for
single/continuous), and the recordsource for all subforms is from the same
table with just the WHERE criteria changing. I never thought the Link
Master/Child properties would change. BTW, how do I do that Debug.Print thing
... sounds rather useful.

Thanks for the tips. Looking forward to hearing back from you. If this all
sounds ridiculous, I welcome other ideas!

 

[Marshall Barton]

Thanks, Marshall. Honestly, sometimes I have no idea what I'm trying to do
with this thing either! I'll try to be brief in explaining ...

My main form contains 13 subforms. With the 15 buttons I change the "WHERE"
criteria for the subform recordsources (trying to avoid using 102 subforms
here). Where I need to select more than one option, it changes the source
object to a continuous form. Sometimes the recordsource is null, then it
hides the subform. It's working well for now ...

I never did put in any code to adjust the Top property. In the form design
view I have the subforms bound within a stacked layout guide (might be new in
Access 2007?) which seems to be adjusting the position of the subforms
accordingly, keeping them the same distance apart. I did not expect that,
thus my first post.

Before I added those two lines "ctrOptionEntryNum.Height = 1728" and
"ctrOptionEntryNum.Height = 864", I didn't have a problem with the subforms
showing blank. And I haven't again since I did a compact/restore. I've been
running this thing off of a memory stick so could it have just been running
slow?

As for the Link Master/Child properties, if they are the same for all the
subforms could they still be reset? Every subform is identical (except for
single/continuous), and the recordsource for all subforms is from the same
table with just the WHERE criteria changing. I never thought the Link
Master/Child properties would change. BTW, how do I do that Debug.Print thing
.. sounds rather useful.

Thanks for the tips. Looking forward to hearing back from you. If this all
sounds ridiculous, I welcome other ideas!

I haven't used A2007 enough to know the ins and outs of what
"stacked" subforms are doing, but it sounds interesting.
From your post above, it sounds like it's all working. At
least I don't see a followup question in there.

The Link Master/Child properties being reset when you change
a subform control's SourceObject was a feature/bug that
could very well have been modified/fixed in A2007.

As for Debug.Print it's just a print method that prints to
the Immediate Window. A very common debugging technique is
to scatter them strategically throughout a procedure so you
can see the sequence of statements and some important
variable/control values. See VBA Help for details.

 

[lynxsta911 via AccessMonster.com]

When you say the Link Master/Child properties were being reset, do you mean
the properties were being removed or cleared? I checked it after running the
form and they're all staying the same ...

Thanks for your input. I'll do some reading up about Debug.Print and I'll run
this like it is and see how it goes. Cheers!

Lynx

 

[Marshall Barton]

When you say the Link Master/Child properties were being reset, do you mean
the properties were being removed or cleared? I checked it after running the
form and they're all staying the same ...

Sometimes they might be cleared, but more often they would
be changed to be the names of fields that Access thought
were the fields you wanted. For example, fields with the
same name in both table are assumed to be linking fields or
fields with a predefined suffix (e.g.Key, ID, etc). See
Tools - Options - Tables - AutoIndex for a list. I
generally clear this list to help reduce automatic indexes
in tables, relationships in queries and Link Master/Child
properties.