Select and Display Subform

[Nanette]

Is it possible to select and display one of many subforms on a main form
depending on the data that is in a field in the main form?

If yes, how is this done?

 

[Marshall Barton]

Is it possible to select and display one of many subforms on a main form
depending on the data that is in a field in the main form?

If yes, how is this done?

A general approach could be:

Select Case [a field]
Case v1
Me.subformcontrol1.Visible = True
Me/sunformcontrol2.Visible = False
Case v2
Me.subformcontrol1.Visible = False
Me/sunformcontrol2.Visible = True
Case Else
'whatever
End Select

That's a very crude way to do this. However, a better way
would depend on the values in [a field].

If the subforms are all the same size and in the same place,
you might even be able to only have one subform control.
This way you would set the control's SourceObject property
instead of making one visible and the rest invisible.

 

[Nanette]

Thanks Marshall,

I'll do some research on having one subform control and adjusting the
SourceObject property.

Is it possible to select and display one of many subforms on a main form
depending on the data that is in a field in the main form?

If yes, how is this done?

A general approach could be:

Select Case [a field]
Case v1
Me.subformcontrol1.Visible = True
Me/sunformcontrol2.Visible = False
Case v2
Me.subformcontrol1.Visible = False
Me/sunformcontrol2.Visible = True
Case Else
'whatever
End Select

That's a very crude way to do this. However, a better way
would depend on the values in [a field].

If the subforms are all the same size and in the same place,
you might even be able to only have one subform control.
This way you would set the control's SourceObject property
instead of making one visible and the rest invisible.

 

[Nanette]

Hi Marshall,

After your comments about the size of the subform, I looked at the forms
again and it looks like I will have to have two subforms because if a field
value is R there will be more rows of data than if a field value is C. And,
there will also be several products that will have different rates that have
either a field value of R or C, therefore I believe that I'll also have to
include setting the SourceObject property as you suggested.

Is this possible, to choose one of two subforms based on a value of R or C
and then set the subforms individual SourceObject properties (to bring in the
correct rates) based on the product # value and R or C value?

Thanks Marshall,

I'll do some research on having one subform control and adjusting the
SourceObject property.

Is it possible to select and display one of many subforms on a main form
depending on the data that is in a field in the main form?

If yes, how is this done?

A general approach could be:

Select Case [a field]
Case v1
Me.subformcontrol1.Visible = True
Me/sunformcontrol2.Visible = False
Case v2
Me.subformcontrol1.Visible = False
Me/sunformcontrol2.Visible = True
Case Else
'whatever
End Select

That's a very crude way to do this. However, a better way
would depend on the values in [a field].

If the subforms are all the same size and in the same place,
you might even be able to only have one subform control.
This way you would set the control's SourceObject property
instead of making one visible and the rest invisible.

 

[Marshall Barton]

Yes, you can do that. OTOH, Mmybe you can just use the tall
one and not worry about some blank space in the short one??

To use two subforms, the code could be vaguely like:

If Me.[a field] = "R" Then
Me.subformshort.Visible = False
Me.subformshort.SourceObject = ""
Me.subformtall.Visible = True
Select Case [other field]
Case thisvalue
Me.subformtall.SourceObject = "this form"
Case thatvalue
Me.subformtall.SourceObject = "that form"
Case Else
MsgBox "Whoops " & [other field]
End Select
Else ' Me.[a field] = "C"
Me.subformtall.Visible = False
Me.subformtall.SourceObject = ""
Me.subformshort.Visible = True
. . .
End If

You will probably need to manipulate the subform's Link
Master/Child Fields properties. If you need help with that,
please explain how each of the form object's (used as
subforms) record source data is related to the main form's
data.

If this approach gets too messy, you can always go back to
your original approach.
--
Marsh
MVP [MS Access]

After your comments about the size of the subform, I looked at the forms
again and it looks like I will have to have two subforms because if a field
value is R there will be more rows of data than if a field value is C. And,
there will also be several products that will have different rates that have
either a field value of R or C, therefore I believe that I'll also have to
include setting the SourceObject property as you suggested.

Is this possible, to choose one of two subforms based on a value of R or C
and then set the subforms individual SourceObject properties (to bring in the
correct rates) based on the product # value and R or C value?

I'll do some research on having one subform control and adjusting the
SourceObject property.

Nanette wrote:

Is it possible to select and display one of many subforms on a main form
depending on the data that is in a field in the main form?

If yes, how is this done?


A general approach could be:

Select Case [a field]
Case v1
Me.subformcontrol1.Visible = True
Me/sunformcontrol2.Visible = False
Case v2
Me.subformcontrol1.Visible = False
Me/sunformcontrol2.Visible = True
Case Else
'whatever
End Select

That's a very crude way to do this. However, a better way
would depend on the values in [a field].

If the subforms are all the same size and in the same place,
you might even be able to only have one subform control.
This way you would set the control's SourceObject property
instead of making one visible and the rest invisible.

 

[Nanette]

Thanks Marshall,

I met with the boss again yesterday and now there will be fewer things on
the list (things are changing daily here) so I'm going to go with one form
and will try your suggestion of setting the control's SourceObject property.

So to get the form to have the correct data, I assume I need to write
something in the SourceObject property of the subform.

Now I'm confused again. What I know of Source Object property is that it
calls data from a table. All the different data for this subform is in one
table. Should I write a query?

The table I'm speaking of is the Rate table. It contains:
id_Rate
RatePercent
RateDollar
Service
Hull_Code
RFQType (options: R or C or B)

The subform looks similar to:

Service Name Rate Rate
Rate
Service Name Rate Rate
Rate
Etc.

The main form has the RFQType (coming from the RFQ table) and the Hull_Code
is in a subform (coming from the LineItem table). Tried to get it to come in
to the main form using DLookUp, but I'm not being able to get that to work
yet. These two values determine what data to goes into the subform. I.E. Hull
17 with RFQType R will bring up different data than Hull 18 with RFQType R,
and still different data will come from Hull 18, RFQType C.

Hope I'm explaining this well enough for you to understand? I'm still a bit
dazed and confused ) but still trying!

Yes, you can do that. OTOH, Mmybe you can just use the tall
one and not worry about some blank space in the short one??

To use two subforms, the code could be vaguely like:

If Me.[a field] = "R" Then
Me.subformshort.Visible = False
Me.subformshort.SourceObject = ""
Me.subformtall.Visible = True
Select Case [other field]
Case thisvalue
Me.subformtall.SourceObject = "this form"
Case thatvalue
Me.subformtall.SourceObject = "that form"
Case Else
MsgBox "Whoops " & [other field]
End Select
Else ' Me.[a field] = "C"
Me.subformtall.Visible = False
Me.subformtall.SourceObject = ""
Me.subformshort.Visible = True
. . .
End If

You will probably need to manipulate the subform's Link
Master/Child Fields properties. If you need help with that,
please explain how each of the form object's (used as
subforms) record source data is related to the main form's
data.

If this approach gets too messy, you can always go back to
your original approach.
--
Marsh
MVP [MS Access]

After your comments about the size of the subform, I looked at the forms
again and it looks like I will have to have two subforms because if a field
value is R there will be more rows of data than if a field value is C. And,
there will also be several products that will have different rates that have
either a field value of R or C, therefore I believe that I'll also have to
include setting the SourceObject property as you suggested.

Is this possible, to choose one of two subforms based on a value of R or C
and then set the subforms individual SourceObject properties (to bring in the
correct rates) based on the product # value and R or C value?

I'll do some research on having one subform control and adjusting the
SourceObject property.

:

Nanette wrote:

Is it possible to select and display one of many subforms on a main form
depending on the data that is in a field in the main form?

If yes, how is this done?


A general approach could be:

Select Case [a field]
Case v1
Me.subformcontrol1.Visible = True
Me/sunformcontrol2.Visible = False
Case v2
Me.subformcontrol1.Visible = False
Me/sunformcontrol2.Visible = True
Case Else
'whatever
End Select

That's a very crude way to do this. However, a better way
would depend on the values in [a field].

If the subforms are all the same size and in the same place,
you might even be able to only have one subform control.
This way you would set the control's SourceObject property
instead of making one visible and the rest invisible.

 

[Marshall Barton]

I met with the boss again yesterday and now there will be fewer things on
the list (things are changing daily here) so I'm going to go with one form
and will try your suggestion of setting the control's SourceObject property.

So to get the form to have the correct data, I assume I need to write
something in the SourceObject property of the subform.

Now I'm confused again. What I know of Source Object property is that it
calls data from a table. All the different data for this subform is in one
table. Should I write a query?

The table I'm speaking of is the Rate table. It contains:
id_Rate
RatePercent
RateDollar
Service
Hull_Code
RFQType (options: R or C or B)

The subform looks similar to:

Service Name Rate Rate
Rate
Service Name Rate Rate
Rate
Etc.

The main form has the RFQType (coming from the RFQ table) and the Hull_Code
is in a subform (coming from the LineItem table). Tried to get it to come in
to the main form using DLookUp, but I'm not being able to get that to work
yet. These two values determine what data to goes into the subform. I.E. Hull
17 with RFQType R will bring up different data than Hull 18 with RFQType R,
and still different data will come from Hull 18, RFQType C.

This would have been a good starting point. I think most of
what's gone before about multiple subforms, source object,
etc. was just confusing the issue.

What you've described above is a single rates subform that
coordinates with the main form and the line items subform.
As far as I can tell at this point, there never was a need
for all that other fooling around.

If I'm finally on the right track, this can be done by
setting the subform's record source to a simple query that
retrieves all possible related records from the rates table
(maybe the entire table??). All the filtering can be done
via the Link Master/Child properties.

To do this, the first thing is to make sure each
synchronizing value is available in a main form text box. I
think you alreay have a main form text box for the RFQtype.

To get the Hull_Code from the line items subform onto the
main form, add a hidden text box (named txtHull) to the main
form's header or footer section. Then add a line of code to
the line items form's Current event procedure:
Parent.txtHull = Me.Hull_Code

With that taken care of, the rates subform control's Link
Master Fields property would be:
RFQType;txtHull
and the Link Childs Fields property:
RFQType;Hull_Code

Give this a try and see where things stand.

 

[Nanette]

Thanks for putting up with my confustion Marshall.

I'll let you know how things work out.

 

[Nanette]

Hi Marshall,

Having a bit of trouble. I had to change the RFQType (on the main form) to a
list box so that it sychronizes with the RFQNo combo box (on the main form).
The RFQNo combox is used to select the correct RFQNo, which another list box
is also based on. This other list box displays three FKs so the user can
select the items he wants. This will eventually fill in another subform that
displays certain data about each RFQNo. I tried to set the RFQType to a text
box to do this, but that didn't work.

I keep getting a message box that asks for the RFQType. Even after I choose
a RFQNo and a RFQType is displayed in the list box.

 

[Marshall Barton]

Having a bit of trouble. I had to change the RFQType (on the main form) to a
list box so that it sychronizes with the RFQNo combo box (on the main form).
The RFQNo combox is used to select the correct RFQNo, which another list box
is also based on. This other list box displays three FKs so the user can
select the items he wants. This will eventually fill in another subform that
displays certain data about each RFQNo. I tried to set the RFQType to a text
box to do this, but that didn't work.

I keep getting a message box that asks for the RFQType. Even after I choose
a RFQNo and a RFQType is displayed in the list box.

 

[Marshall Barton]

Having a bit of trouble. I had to change the RFQType (on the main form) to a
list box so that it sychronizes with the RFQNo combo box (on the main form).
The RFQNo combox is used to select the correct RFQNo, which another list box
is also based on. This other list box displays three FKs so the user can
select the items he wants. This will eventually fill in another subform that
displays certain data about each RFQNo. I tried to set the RFQType to a text
box to do this, but that didn't work.

I keep getting a message box that asks for the RFQType. Even after I choose
a RFQNo and a RFQType is displayed in the list box.

I'm getting confused again. Let's at least try to get our
nomenclature straight. A column in a table/query is called
a **field**. A thingie on a form/report is called a
**control**. Access often defaults to using the same name
for a control that is bound to field. This can lead to
confusion, sometimes even Access gets confused about using
the same name for two different objects. To avoid this
confusion, whenever you have code, an expression or a
property the refers to a field or its bound control, it is a
good idea to change the name of the control to something
else. It is a common practice to set the bound control's
name to the field name prefixed by three characters that
indicate the type of the control. For example, if your list
box is bound (ControlSource property) to the RFQType field
in the form's record source table/query, then set the list
box's name to lstRFQType.

With all that in mind, what are the names of the fields in
the main form and the subform record source? What are the
names of the main form controls that are bound to the fields
that are related to whatever it is that you now have? From
now on, please use the control and field names instead of
"other listbox", "a text box", "another subform" or any
other hand waving phrase.

The Link Master Fields property can refer to either controls
on the main form or to fields in the main form's record
source table/query.

The Link Child Fields property can only refer to fields in
the subform control's SourceObject form's record source
table/query.

The value placed in a combo or list box's bound field is
determined by their BoundColumn property,

When you are prompted for a value when you open a form or
report (or query), it means that whatever you are prompted
for is not in the form/report's RecordSource or combo/list
box's RowSource query's table. This implies that you added
the RFQType field to the wrong or inappropriate query.

 

[Nanette]

Hi again Marshall,

Thanks for the information.

Answers to your questions are:

With all that in mind, what are the names of the fields in the main form and
the subform record source?

Main Form and Fields
Table Name (from Record Source): tblRFQ
Table Fields: RFQNo (PK), RFQType

Sub Form and Fields
Sub Form Name: sfrmBOESelection
TableName (from Record Source): LineItem
Table Fields: RFQNo (PK), Change (PK), LI (PK), Desc, Qty, PayingHull, etc.

Sub Form Name: sfrmBOETitle
TableName (from Record Source): BOE
Table Fields: RFQNo (FK), Change (FK), LI (FK), PrimeID, BoeDate, AssistNo,
etc.

SubForm: sfrmLF_B1
TableName (from Record Source): tblRate
TableFields: id_Rate (PK), RatePercent, RateDollar, Service (Lookup to
tblService), Hull_Code (Lookup to tblHull), Cat (Lookup to tblCategory)
This is a pricing matric table, which I was given instruction on. Still
uncomfortable with it.


What are the names of the main form controls that are bound to the fields
that are related to whatever it is that you now have?

Controls:
cboRFQNo: combo box. RecordSource: tblRFQ

lstRFQType: list box, bound to cobRQFNo combo box. RecordSource: SELECT
RFQ_Type FROM tblRFQ WHERE RFQNo=Forms!frmBOESelection!cboRFQNo;

lstChange: list box, Record Source: SELECT Change, LI, RFQNo FROM LineItem
WHERE RFQNo=Forms!frmBOESelection!cboRFQNo;


The Link Child Fields property can only refer to fields in the subform
control's SourceObject form's record source table/query.
??Are you saying that the Link Child Fields property can only refer to the
table/query fields that are the base for the subform?

 

[Marshall Barton]

Answers to your questions are:

With all that in mind, what are the names of the fields in the main form and
the subform record source?

Main Form and Fields
Table Name (from Record Source): tblRFQ
Table Fields: RFQNo (PK), RFQType

Sub Form and Fields
Sub Form Name: sfrmBOESelection
TableName (from Record Source): LineItem
Table Fields: RFQNo (PK), Change (PK), LI (PK), Desc, Qty, PayingHull, etc.

Sub Form Name: sfrmBOETitle
TableName (from Record Source): BOE
Table Fields: RFQNo (FK), Change (FK), LI (FK), PrimeID, BoeDate, AssistNo,
etc.

SubForm: sfrmLF_B1
TableName (from Record Source): tblRate
TableFields: id_Rate (PK), RatePercent, RateDollar, Service (Lookup to
tblService), Hull_Code (Lookup to tblHull), Cat (Lookup to tblCategory)
This is a pricing matric table, which I was given instruction on. Still
uncomfortable with it.


What are the names of the main form controls that are bound to the fields
that are related to whatever it is that you now have?

Controls:
cboRFQNo: combo box. RecordSource: tblRFQ

lstRFQType: list box, bound to cobRQFNo combo box. RecordSource: SELECT
RFQ_Type FROM tblRFQ WHERE RFQNo=Forms!frmBOESelection!cboRFQNo;

lstChange: list box, Record Source: SELECT Change, LI, RFQNo FROM LineItem
WHERE RFQNo=Forms!frmBOESelection!cboRFQNo;


The Link Child Fields property can only refer to fields in the subform
control's SourceObject form's record source table/query.
??Are you saying that the Link Child Fields property can only refer to the
table/query fields that are the base for the subform?

I don't see the information for the subform we are trying to
synchronize in there???

Following the **control** naming convention I described
earlier.

If I understand your description above, you have a list box
named lstRFQType that is bound to the RFQ_Type field in the
main form's table. The list box's ColumnCount property is 1
and the BoundColumn is 1.

You also have subform, sfrmLF_B1 that, among other things,
has a field named Hull_Code from table tblRate. You are
using the code I posted a while back in the subform's
Current event to set the main form text box, txtHull, to the
value of the Hull_Code field.

If I have that straight, then subform LinkMasterFields
property should be
lstRFQType;txtHull

I don't see the critical information for the subform we are
trying to synchronize in there??? so I am not sure what the
link child property needs to be. Whatever, it needs to be
the related **fields** in the table used as the subform's
record source. I ***think*** it might be
RFQType;Hull_Code

So, the answer to your final queastion above, Yes, the
subform's record source table must include the fields
referred to in the subform control's link child property.
(This means that you can not synchronize a main form record
to a subform's set of records unless the subform's data
includes the linking fields.)

 

[Nanette]

Hi Marshall,

Just wanted to let you know I have not forgotten about our conversation.
I've just been laid up with the flu for the last several days. I'll get back
to work tomorrow and use your suggestions.