 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / February 2008Form Control to have default value from subform
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. 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. 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 [quoted text clipped - 14 lines] >> I cannot get a combo or list box control on the main form to populate with >> the subform value as a default value. 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. > [quoted text clipped - 56 lines] >>> with >>> the subform value as a default value. 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. >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 . >Jeanette Cunningham > [quoted text clipped - 3 lines] >>>> with >>>> the subform value as a default value. 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 > Flange type is directly related to pipe spec thus the flange table. > The bolts are directly related to the pipe size. (size, quantity and [quoted text clipped - 17 lines] >>The bolt length on the main form can either be from the list of >>FlangetoFlangeBoltLength in tblBoltMaterials 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. 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 > 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. 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. >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 [quoted text clipped - 9 lines] >> the >> subform information.>The bolts are directly related to the pipe size. 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. 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. [quoted text clipped - 18 lines] >>>> the >>>> subform information.>The bolts are directly related to the pipe size. 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 [quoted text clipped - 36 lines] >>>>> the >>>>> subform information.>The bolts are directly related to the pipe size. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.