Form Control to have default value from subform

[dancox via AccessMonster.com]

I have a bolt takeoff form based on a bolt takeoff table.
The user selects a pipe size and the corresponding bolt qty, diameter and
length appear in the subform.

The length is default for a flange to flange connection. If the user
unchecks a check box, I want the user to enter a custom value.

I cannot get a combo or list box control on the main form to populate with
the subform value as a default value.

 

[Jeanette Cunningham]

Dan,
I'm not sure what your form is trying to do, but your process sounds
back-to-front.
Normally the main form is the parent and a value on the parent is used to
set a default on the child (subform).
You are asking for the opposite - I don't know if it is do-able.
Post some more details:
--form - table, primary key, foreign key, any other significant fields
--same for subform
--explain how the default value will be used on the main form.

Jeanette Cunningham

 

[dancox via AccessMonster.com]

This is my table structure.

tblBoltGasketTakeoff
PK IDBoltGasketTakeoff
IDBolts
IDGasket
FlangeQty
BoltLength
IDISO
FlangetoFlange Yes/No

tblBoltMaterials
PK IDBolt
PipeSize
BoltQty
BoltDiameter
FlangetoFlangeBoltLength

tblGasketMaterials
PK IDGasket
PipeSpec
GasketType

tblISO
PK IDISO
ISONumber

The Main Form is based on tblBoltGasketTakeoff. Subforms are based and
tblGasketMaterials and tblBoltMaterials. By selecting the PipeSpec on the
main form based on control source IDGasket. The subform populates the
corresponding gasket materials. The next selection is PipeSize based on the
IDBolts PipeSize field. The subform populates with Diameter and Quanitity of
bolts. On the main form I also want Bolt Length. The bolt length comes from
tblBoltMaterials for a FlangetoFlange connection, but if it is not a
FlangetoFlange to connection, then the user has to be prompted to enter a
custom value from a list or type in their own value.

Dan,
I'm not sure what your form is trying to do, but your process sounds
back-to-front.
Normally the main form is the parent and a value on the parent is used to
set a default on the child (subform).
You are asking for the opposite - I don't know if it is do-able.
Post some more details:
--form - table, primary key, foreign key, any other significant fields
--same for subform
--explain how the default value will be used on the main form.

Jeanette Cunningham

I have a bolt takeoff form based on a bolt takeoff table.
The user selects a pipe size and the corresponding bolt qty, diameter and

[quoted text clipped - 5 lines]

I cannot get a combo or list box control on the main form to populate with
the subform value as a default value.

 

[Jeanette Cunningham]

Dan,
Checking that I'm understanding your setup.
The bolt length on the main form can either be from the list of
FlangetoFlangeBoltLength in tblBoltMaterials
OR
The user can type in a different value?

If the above is correct, I suggest an additional field for bolt length like
so:
FlangetoFlangeBoltLength
OtherBoltLength

FlangetoFlangeBoltLength comes from tblBoltMaterials which is related
one-to-many to tblBoltGasketTakeoff
OtherBoltLength is not related to tblBoltMaterials

Your code would make sure that a user could enter either a
FlangetoFlangeBoltLength or OtherBoltLength but not both.
The FlangetoFlangeBoltLength would be chosen from a combo on the main form
which would add the ID from tblBoltMaterials,
but there is not a related table for OtherBoltLength.

I'm going to suggest a slight change to the way the tables are set up. I
don't know anything else about your app. If the table descriptions you sent
in the last post are the only table in your app, then the changes I outline
below would fit OK, but if you have several other tables, then maybe we need
to look at them all.

I'm not happy about the structure of the tables. The
FlangetoFlangeBoltLength looks as though it should be in a separate Lookup
Table, the PipeSize looks as though it should be in a separate Lookup Table
which populates a combo on the main form and the BoltDiameter looks as
though it should be in a separate Lookup Table which populates a combo on
the main form. That leaves BoltQty to be put in tblBoltGasketTakeoff as a
field.
In other words replace tblBoltMaterials with 3 separate lookup tables and
move BoltQty into tblBoltGasketTakeoff .
tblGasketMaterials looks OK as does tblISO

I suggest that PipeSpec and GasketType are also separate lookukp tables with
combos on the main form (unless PipeSpec and GasketType can be selected as a
pair because the same PipeSpec always goes with a particular GasketType) .
That removes both subforms from the main form.

Jeanette Cunningham

This is my table structure.

tblBoltGasketTakeoff
PK IDBoltGasketTakeoff
IDBolts
IDGasket
FlangeQty
BoltLength
IDISO
FlangetoFlange Yes/No

tblBoltMaterials
PK IDBolt
PipeSize
BoltQty
BoltDiameter
FlangetoFlangeBoltLength

tblGasketMaterials
PK IDGasket
PipeSpec
GasketType

tblISO
PK IDISO
ISONumber

The Main Form is based on tblBoltGasketTakeoff. Subforms are based and
tblGasketMaterials and tblBoltMaterials. By selecting the PipeSpec on the
main form based on control source IDGasket. The subform populates the
corresponding gasket materials. The next selection is PipeSize based on
the
IDBolts PipeSize field. The subform populates with Diameter and Quanitity
of
bolts. On the main form I also want Bolt Length. The bolt length comes
from
tblBoltMaterials for a FlangetoFlange connection, but if it is not a
FlangetoFlange to connection, then the user has to be prompted to enter a
custom value from a list or type in their own value.

Dan,
I'm not sure what your form is trying to do, but your process sounds
back-to-front.
Normally the main form is the parent and a value on the parent is used to
set a default on the child (subform).
You are asking for the opposite - I don't know if it is do-able.
Post some more details:
--form - table, primary key, foreign key, any other significant fields
--same for subform
--explain how the default value will be used on the main form.

Jeanette Cunningham

I have a bolt takeoff form based on a bolt takeoff table.
The user selects a pipe size and the corresponding bolt qty, diameter
and

[quoted text clipped - 5 lines]

I cannot get a combo or list box control on the main form to populate
with
the subform value as a default value.

 

[dancox via AccessMonster.com]

Flange type is directly related to pipe spec thus the flange table.
The bolts are directly related to the pipe size. (size, quantity and flange
to flange length)

The only variable is length. The user only has to select pipe spec and size
and the rest of the information is in my table. The only reason the length
changes - is if the connection is not flange to flange, but the diameter or
quantity do not change.

Basically I am trying to populate the bolt length field in my Bolt Gasket
Takeoff table using flange to flange length as a default or user entered
length from a list or custom value.

 

[Jeanette Cunningham]

Do you have one combo for pipe spec, one combo for size and a combo for
length?
If you want to use flange to flange length as a default, it needs to be in a
combo on the main form.
If user indicates that it is flange to flange, then you set the combo's
rowsource to a lookup table with flange to flange lengths (set as default),
if not, then you set the combo's rowsource to the other list. If the user
wants to type in a value, provide a textbox - as it appears you don't want
to add this length to any existing list of lengths. Make sure that a user
can either select from the combo or type in the textbox - but they can't do
both.

Jeanette Cunningham

 

[dancox via AccessMonster.com]

the only reason I am displaying the subform information is so the user can
see the result of there pipe spec or size selection. They will not alter the
subform information.>The bolts are directly related to the pipe size.

 

[Jeanette Cunningham]

I still suggest that pipe spec and size selection are done from combos on
the main form. If you want to show the related info on a subform after the
user selects from the combo on the main form, that's fine.
I still suggest the arrangement in my last reply for length - the length is
recorded directly in the main form.
Is there something I am missing?

Jeanette Cunningham

 

[dancox via AccessMonster.com]

Your not missing anything. I have never been able to make the default value
for the length combo box be the flange to flange length. I still need a
check box or some other way for the user to say the connection is flange to
flange yes or no. Based on that result, they will either get the flange to
flange length or direction to enter a custom value for length.

 

[Jeanette Cunningham]

You can set the rowsource for the length combo box to a query that shows the
selection of possible flange to flange lengths.
When user selects a pipe spec and size, does that impact on the possible
choices for flange to flange lengths?, if this is the case, we can change
the row source of the length combo to do this. What do you want to show for
the default value for the length combo?

Yes could use a checkbox and make both the combo and textbox disabled until
the user checks which length type they want.
Sometimes I do this sort of thing by putting both the combo and the text box
near each other and put a label that tells users to choose either a length
from the combo or type in their own. You can put code to clear the combo if
user types in the length and vice versa and pop up a message box telling the
user what's happening.

Jeanette Cunningham

 

[dancox via AccessMonster.com]

Yes, the pipe size ties directly to the flange to flange bolt length. Can I
use the flange to flange length as a default value for the Combo Box and
provide a custom list of lengths for all other bolt lengths in a drop down?

I want the default value to show the flange to flange length if the check box
for flange to flange is checked. If the check box is un checked, I would
like it to say "Enter Length" or blank if text is not allowed in the control.

You can set the rowsource for the length combo box to a query that shows the
selection of possible flange to flange lengths.
When user selects a pipe spec and size, does that impact on the possible
choices for flange to flange lengths?, if this is the case, we can change
the row source of the length combo to do this. What do you want to show for
the default value for the length combo?

Yes could use a checkbox and make both the combo and textbox disabled until
the user checks which length type they want.
Sometimes I do this sort of thing by putting both the combo and the text box
near each other and put a label that tells users to choose either a length
from the combo or type in their own. You can put code to clear the combo if
user types in the length and vice versa and pop up a message box telling the
user what's happening.

Jeanette Cunningham

Your not missing anything. I have never been able to make the default
value

[quoted text clipped - 20 lines]

 

[Jeanette Cunningham]

Yes, I now understand what is required.
Create a union query for the length combo box.
The union query uses the table with custom lengths unioned to the default
value for flange to flange bolt length
You will have a query to select the correct value for flange to flange bolt
length depending on user selection for pipe spec and size.
We will need a way to mark which item on the list is the default value, so
we can set the combo to this default value.
You can do this with an extra field created in the union query - I will call
it DefValue

For example: If you know how to do union queries
"SELECT BoltLength, 1 AS DefValue FROM TableBoltLength WHERE . . . " _
& "UNION " _
& "SELECT BoltLength, 2 As DefValue From TableLengthList ' _
& "ORDER BY BoltLength"

The above query in datasheet view will give you a list that contains both
the default value and the list values for length.
Length DefValue
1.5 1
2.2 2
0.6 2
1.9 2

Where I have: SELECT ... FROM TableBoltLength WHERE . . .
Replace TableBoltLength with your table or tables joined
Build the where clause to include both pipe spec and size from the 2 combos
on the main form
Replace TableLengthList with your table

The above union query is the row source for the length combo
Make column 1 the combo's bound column
When you want to set the default for the combo:
Me.MyCombo.DefaultValue = "SELECT BoltLength, DefValue FROM qUnion WHERE
DefValue = 1"
Where qUnion is the union query from above - you can save the union query
and use it, or you can build it in code.

So the combo will have the default value depending on the user's selection
for pipe spec and size.
The combo's row source will show all the list values including the default
value when it is dropped down.

Jeanette Cunningham

Yes, the pipe size ties directly to the flange to flange bolt length. Can
I
use the flange to flange length as a default value for the Combo Box and
provide a custom list of lengths for all other bolt lengths in a drop
down?

I want the default value to show the flange to flange length if the check
box
for flange to flange is checked. If the check box is un checked, I would
like it to say "Enter Length" or blank if text is not allowed in the
control.

You can set the rowsource for the length combo box to a query that shows
the
selection of possible flange to flange lengths.
When user selects a pipe spec and size, does that impact on the possible
choices for flange to flange lengths?, if this is the case, we can change
the row source of the length combo to do this. What do you want to show
for
the default value for the length combo?

Yes could use a checkbox and make both the combo and textbox disabled
until
the user checks which length type they want.
Sometimes I do this sort of thing by putting both the combo and the text
box
near each other and put a label that tells users to choose either a length
from the combo or type in their own. You can put code to clear the combo
if
user types in the length and vice versa and pop up a message box telling
the
user what's happening.

Jeanette Cunningham

Your not missing anything. I have never been able to make the default
value

[quoted text clipped - 20 lines]

the
subform information.>The bolts are directly related to the pipe size.