Setting a subforms recordsource

[Guest]

Hi All,

I have a main form with a combo box on it. When a particular value is
selected, say Blue, a subform on that form is made visible which has it's own
combo box and textbox. The options of the subform combo box are dependant on
the combo box value selected in the main form. So if Blue is selected on the
main form, the subform combo box has shades of blue and the text box
describes those shades. If the colour in the main form combo box has no entry
on the subform, the subform is set to invisible. The data for the subform is
held in individual tables (as lookup tables) which become the source data for
the subform. Currently I have a separate subform for each value in the main
form combo box for which I have additional data. Each subform is on the main
form exactly on top of each other and the correct subform is made visible
depending on the colour selected on the main form combo box.

I was wondering if it were possible to have just one subform and then have
it's recordsource property set, at runtime, by the value of colour selected
in the main form combo box. I can't seem to expose this property when adding
code to the main form combo box.

Any ideas would be very much appreciated,
Ian.

 

[strive4peace]

one table, one subform, LinkMasterFields, LinkChildFields
---


Hi Ian,

instead of several tables with the same structure, make a table for all
the Colors you might list on the main form (and this table will also
serve as a source for that combobox)

*Colors*
ColorID, autonumber
Color, text

now, create a ColorID field in one of the tables that correlates to a
record in the Colors table for your specific color

colorID, Long Integer

then, use an UPDATE query to update the ColorID for the first set of data

UPDATE Tablename SET ColorID = 1;

WHERE:
1 is the ColorID that correlates to this data set

then use an Append query to move the data into this main table from one
of your other tables. For the ColorID field, jsince this is not IN your
data (as it is why you have seperated the tables), lookup the
appropriate ColorID from the Colors table and make a calculated column:

field --> 2
AppendTo --> ColorID

WHERE:
2 is the ColorID that correlates to this data set

then, with all data sets in one table, use just one subform and use the
LinkMasterFields and LinkChildFields properties of the subform control
to filter the records that you want.

LinkMasterFields --> ColorID
LinkChildFields --> ColorID

the mainform will have a combobox:
Name --> ColorID
RowSource --> Select ColorID, Color FROM Colors ORDER BY Color;
ColumnCount --> 2
ColumnWidths --> 0;1.5
ListWidth --> 1.5

then, make sure that ColorID is actually ON the subform and also Name =
ColorID -- its Visible property can be No

*** Subform Control vs Subform: The Difference between a Subform Control
and a Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

~~~~ turn on Properties window ~~~~

When you are in the design view, turn on/off the Properties window -->

1. from menu: View, Properties
OR
2. right-click and choose Properties from the shortcut menu

and then click on various objects. The properties window changes as you
change what is selected. If you have multiple objects selected, the
values for the properties they have in common will be displayed


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Thanks

one table, one subform, LinkMasterFields, LinkChildFields
---


Hi Ian,

instead of several tables with the same structure, make a table for all
the Colors you might list on the main form (and this table will also
serve as a source for that combobox)

*Colors*
ColorID, autonumber
Color, text

now, create a ColorID field in one of the tables that correlates to a
record in the Colors table for your specific color

colorID, Long Integer

then, use an UPDATE query to update the ColorID for the first set of data

UPDATE Tablename SET ColorID = 1;

WHERE:
1 is the ColorID that correlates to this data set

then use an Append query to move the data into this main table from one
of your other tables. For the ColorID field, jsince this is not IN your
data (as it is why you have seperated the tables), lookup the
appropriate ColorID from the Colors table and make a calculated column:

field --> 2
AppendTo --> ColorID

WHERE:
2 is the ColorID that correlates to this data set

then, with all data sets in one table, use just one subform and use the
LinkMasterFields and LinkChildFields properties of the subform control
to filter the records that you want.

LinkMasterFields --> ColorID
LinkChildFields --> ColorID

the mainform will have a combobox:
Name --> ColorID
RowSource --> Select ColorID, Color FROM Colors ORDER BY Color;
ColumnCount --> 2
ColumnWidths --> 0;1.5
ListWidth --> 1.5

then, make sure that ColorID is actually ON the subform and also Name =
ColorID -- its Visible property can be No

*** Subform Control vs Subform: The Difference between a Subform Control
and a Subform ***

The first click on a subform control puts handles* around the subform
object.
*black squares in the corners and the middle of each size -- resizing
handles

The subform object has properties such as

Name
SourceObject
LinkMasterFields
LinkChildFields
Visible
Locked
Left
Top
Width
Height

the subform control is just a container for the subform.

the subform itself is an independent form -- you can open it directly
from the database window and it has the same properties of the main
form. It is only called a subform because of the way it is being used.

To summarize, when you are in the design view of the main form, the
first click on the subform is the subform control -- you will see the
handles around the edges -- and the second click gets you INTO it -- you
will see a black square where the rulers intersect in the upper left of
the "form" you are "in" (and this is the same as if you went to the
design directly)

me.subform.controlname --> the subform control
me.subform.controlname.form --> the form inside the subform control

~~~~ turn on Properties window ~~~~

When you are in the design view, turn on/off the Properties window -->

1. from menu: View, Properties
OR
2. right-click and choose Properties from the shortcut menu

and then click on various objects. The properties window changes as you
change what is selected. If you have multiple objects selected, the
values for the properties they have in common will be displayed


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Guest]

Hi Crystal

Thanks for such a prompt and extremely thorough reply. I wondered if I was
approaching this from the worng angle and you have given me some great advice.

Thanks again, very much appreciated,
Ian.

 

[strive4peace]

you're welcome, Ian happy to help

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*