Full Version: When And How To Use Popup Forms
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
bburn
First of all I'm sure I'm not normalized but what I have is working for me now.
make windows and there are a gazillion values (potential fields) associated with each style and there are 12-15 different varieties of styles. I know that each of these values could/should be included in a single table but to simplify my views, particularly when creating calculating expressions in the query grid, I've broken the values down into different tables which are...
General specifications for each different style (the values that don't change among styles that are the same) tblGenSpecDoubleHung, tblGenSpecCasement, tblGenSpecDoor for example.
Specific specification for each window within that style (the gen specs are the same but only the location in the home, width and height are different), which are contained in tblUnitSpecs
Then even if the style and size are the same between different units, some might have grids (muntins) or other peculiarities that others don't so I've got some tables like tblMuntins, tblLugs, tbl MeetingRailOffset related to tblUnit 1:m (how do you abreviate one to many?).
These tables are all in decending order 1:m and my forms are the same way, frmGenSpecHung - frmUnit.
Things are working correctly. But my question is, when I'm working with a particular unit in frmUnit which is in continious form mode, and it has muntins, lugs or an offset meeting rail I want to be able to click to bring up the appropriate form in which to enter that data. I can bring up the form with the button I created but how do I link the PK of tblUnit with the FK of frmMuntins, for example? Must I force it programatically or is there a builtin feature of which I'm not aware that will do this for me? Then once I do that, I'd like to be able to click (or something) to automatically relate the same sub information to the next unit which might very likely have the same characteristics.
Am I asking too much?
Bill
cpetermann
Bill,
You are not asking too much, however...
you may be trying to do too much too soon.
Can't install the glass 'til the frame is ready;)
In your your previous thread
I've listed the reasons why your current table design is and will continue causing problems and why the design advice and demo Here
is the Best option.
Bill, ultimately, it's your db. You have to decide.
We are happy help you fix current or prevent future issues--it's quicker and easier now--in the design phase. <
Much more difficult, time-consuming, and frustrating pullhair.gif
to try to shim and plane to make design errors work after it's in use.
You are a skilled Craftsman.
Individuals trust you, Bill, to advise the best product for their needs,
and to design and create that product.
You stake your reputation on the quality that final product.
So do we.
Trust the skilled UA Craftsmen/women.
It's a window of opportunity.
bburn
Wow, your analogies show you have a firm grip on my situation.
et me offer another for your consideration...
I'm in a crunch and have to fill several contracts that are already over due. I've been using the same machine for years and although it's not the ideal machine for it's particular purpose I've learned to make it work and the products are perfect. It breaks before the job is done. Someone gives me a $150,000 CNC router for free. It will do the job much faster, and it's more flexible. The only problem is that I don't know how to use it. It's obvious it will take me MONTHS to learn. While I'm learning, two things DON'T happen; a) the job doesn't get done and b) I dont' get paid. Now, I could pay someone to come in a teach me how to use the CNC but I CANT AFFORD THE PRICE, or I could pay a price I can afford and get the old machine fixed and have it running in a matter of days. What shoud I do?
My solution would be to get the old one fixed and get the jobs done. I still have the new one here and once the jobs are done I can begin learning how to use the better, faster, more flexible machine at my leisure.

Cynthia and Tina (if you're out there), I don't question that the more normalized design you suggest is better and I do trust your experience and recommendations. Furthermore, I appreciate the fact that you would take the time to help me get the right tool for the job. However, without someone holding my hand, or paying someone to implement the design, there is no way I can get it up and running in a timely manner. See where I'm at?
Bill
BruceM
Cynthia makes excellent points, and well expressed.
It is potentially troublesome to have many different categories. If one of the problems is that one type of product has a different set of parts or features than another, you can adjust the list available to the subform according to the general information on the main form. In terms of tables this is a main table and a related table (BTW, 1:m is as good a way as any to express this).
If I understand correctly, one issue is that rails, stiles, muntins, panels, glass, etc. are many different sizes. If your main record is a door with a width and a height and other general information, you need a list of parts in the child table (subform), including their dimensions, profile, sizes, counts, etc. If their is a possibility of more than one type of wood in the door, you need to allow for that.
In short, you need to allow for any possibility without having to create another table. If your door table assumes some sort of stile and rail construction, what happens if a customer wants a slab door? Your door table needs to allow for that. Same for windows.
Generally speaking, a typical setup is a table of parts (rail, stile, muntin, etc.), a table for items (each door or window is one record), and a junction table between them. This is the most flexible, but it may be enough just to have a lookup table of parts. In either case the subform table would provide a place to list attributes such as length, width, and profile for each part. You end up with a record for the door, and related records for all of its components. The related records could be used to generate a cut list, if that is part of your intention.
If you are referring to the sort order, this is not relevant. Use queries to sort the data. Don't worry about their order in the table.
bburn
Bruce,
You said...
"Generally speaking, a typical setup is a table of parts (rail, stile, muntin, etc.), a table for items (each door or window is one record), and a junction table between them. This is the most flexible, but it may be enough just to have a lookup table of parts." (BTW, how do you use that quote button?)
I think we are on the same page. Isn't the following relationship setup (what I've done in my current DB) what you just said? With the twist that I add/edit the odd and misc. specs for that particular unit in their own tables. Look...
tblContracts
ContractID PK
Description
Date
Amount
tblGenSpesHung
GenSpecsHungID PK
ContractID FK
StyleWidth - default 2.25
TopRailWidth - default 2.225
BottomRailWidth - default 2.9375
(ten or twelve more characteristics of my standard double-hung)
tblGenSpesCase
GenSpecsCaseID PK
ContractID FK
StyleWidth - default 2.125
TopRailWidth - default 2.125
BottomRailWidth - default 2.75
(ten or twelve more characteristics of my standard casement)
Several more tables with values describing the standard specs of various styles of units (windows or doors)
tblUnit
UnitID PK
GenSpecsHungID FK
GenSpecsCaseID FK
Several other GenSpecs for each style I commonly make FK
Location
Height
Width
tblMuntin (grids)
MuntinID PK
UnitID FK
MuntonLocation (Upper sash, Lower sash or Both)
VMuntonQty
VMuntonWidth
HMuntonQty
HMuntonWidth
tblMR_Offset (used if the glass heights are not equal, bottom sash - top sash)
MR_OffsetID PK
UnitID FK
MRPosition (distance from top rail)
The only thing I don't have here is the Junction Table you mentioned - how and why?
There are also two ways to look at which table comes next after Contract. Yes the contract is made up of Units so one might consider tblContract 1:m tblUnits 1:m tblGenSpecs (several) and tblUnits 1:m tblMuntons
However, it might be viewed as the contract is made up of one or many styles of units (tblGenSpecsHung, tblGenSpecsCase etc.) each of which are made up of units of various sizes. Because of the way in which I want to calculate and view my data I've chosen this one.
Furthermore, when I tried to work with the db Tina recommended I couldn't figure out how to do the calcs. She had specs as records in a table rather than fields in a table...
tblSpecs
SpecName
SpecDefaultValue
(with, for example, JambWidth, Jamb Height and so on as records in the table rather than fields in the table)
Which I can see is indeed more flexible. However, when I began trying to do calculations in a query I couldn't figure out how to pull, for example, JambWidth and JambHeight for each unit record to multiply to get JambArea for each unit record (not a calc I do but a simple example). If those values are contained in fields I can simply create an expression JambArea:[JambWidth] * [JambHeight] and have a record in the query grid with the result.
Also, please take a look at a reply I made to Cynthia to get a feel from whence I come.
Thanks Bruce
bburn
Cynthia and Bruce and any of you other 96 viewers,
It's pretty egotistical of me to think this matters to anyone but me but just in case...
I've given it much thought and decided to do away with all the GenSpec tables as well as the Accessory tables such as tblMuntin, and included all the specs I can think of for any window or door (unit) I build and included them all in tblUnitSpecs which is a child of tblContracts, child of tblProperties (I'll fit a customers table in there somewhere, likely as a child of tblContracts). I've done this and tested the data and things are relating properly and data is going where it's supposed to go.
However, by the time I'm done creating all the fields I need to describe all the possible attributes in tblUnitSpecs, of all the styles of units I produce, I wouldn't be surprised if there are over 100 fields. Of course this would be better accomplished if I could have a table with only a couple of fields like..
UnitID PK
ContractID FK
SpecName
SpecDefault
This is what Tina was suggesting. If someone could tell me how I could then do calculations in a query expression on the values of these fields among one another I'd really consider going this route. The problem is that in every expression I've ever created, I specify the field names in the expression. For example JambArea: [JambWidth] * [JambHeight]. How do you do this when you don't have field names to specify in the query (with out a bunch of where clauses)?
cpetermann
Bill,
Is difficult as I'm sure it was, I think you've made the correct decision. <
Let's get the table/s designed to best accomplish your needs.
And the forms for you to easily enter the data.
accesshawaii
Just to throw my 2 cents in here. I understand that you're behind and you're looking to get paid but putting it together where the tables are not setup properly is not going to benefit you nor the client. It's going to lead to nothing but headaches down the road when they start getting inaccurate reporting and figures and pointing the finger of blame at you.
It would be much better to explain the situation to the client and tell them you need a little more time to ensure that everything is working correctly, so they have a quality product.
bburn
Cynthia,
orry, I tend to ramble. Here's a specific question....
If I put all of the dozens and dozens of attributes of all my products into one table as fields it's messy. But by using queries to pull out only the attributes for a given unit type it's manageable and very much like if I had them in seperate tables for each style. Also, I can easily create the expressions I need to calculate the dozens of more attributes required for the unit cut lists reports. However, I see the value of storing them as records in a table that only has four or five fields, one of which would be SpecName and another SpecDefault....
Here's te question...
If they are stored that way, how do I specify a particular attribute value (record) in a query expression?
cpetermann
Bill,
On the query design grid, you would specify the criteria in the Criteria line.
First let's get the table/s designed, ok. <
Each Window -- regardless of style MUST have what ?
Outer measurement? Height & Width
Inner Measurement? Height & Width
Each Door--regardless of style MUST have what ?
Height
Width
Depth
Right or Left open?
sorry if you've already gone over this, just easier to keep in one thread.
BTW to use the quote feature --copy whatever text you want to put in a quote box,
Select the Quote ballon icon button you will then see a couple of brackets [ Click between the brackets and paste ]
bburn
Those are correct but let's try to use the industry words I'll be using to avoid confusion down the road.
very window will have the following properties at the very minimum
AllWindow
Room - Room in which the unit is located this should probably be a combobox
RoomUnit - Number of the unit within the room. Your dining room may have unit 1, 2 and 3
JambWidth - Inside dimension, width of the existing frame into which my replacement sash are being placed
JambHeight - Inside dimension, height of the existing frame into which my replacement sash are being placed
AllDoor
Room - Room in which the unit is located this should probably be a combobox
RoomUnit - Number of the unit within the room. Your dining room may have unit 1, 2 and 3
JambWidth - Inside dimension, width of the frame into which my replacement doors are being placed
JambHeight - Inside dimension, height of the frame into which my replacement doors are being placed
HingeLR - You know (Radio)
SwingIO - Swings to the inside or outside (Radio)
HAs I view this it occurs to me that the only thing that makes these units ABSOLUTELY UNIQUE is the Room and RoomUnit. Several different units, regardless of style might have exactly the same height and width. It's only the locations that precicely identify a unit. But you are correct, every unit (door or window) does eventuyally need H & W.
Another question you might ask is what will I be inputing for each unit and how do I want to see the input fields?
Once I put in the Property Info and Contract Info I need to start inputing the units, as many as 20 to 40 or more per contract. It would be easiest to do it by first Selecting the unit style for the record (which could have it's default set on the contract table since if a home has DH most of the windows will be DH and likewise for casement) then tabbing from Room to RoomUnit to Width to Height, with options to change the Style default values, add muntins etc.
Make sense?
B
BruceM
More likely it would be a parent table (one customer:many contracts, not the other way around).
You probably could, although I am not quite sure I understand the purpose of SpecDefault. I think SpecName would be Double Hung, Casement, Door, etc., but I'm not clear on SpecDefault. You may also want overall information such as Cynthia mentioned (overall size, hinge side, etc.).
You will also need a related table for the unit's components (or attributes). Most likely these would be selected from a master list (table) of all possible components. It should be possible to filter the list according to the SpecName (or maybe it will be a SpecID). I think this is also part of the general thinking.
Now I will watch this thread with interest (I have built thousands of doors in my time), but will tend to be an observer for now. The conversation has been going along just fine. I will just add that I agree with Cynthia that you seem to be on the right track.
cpetermann
Bill,
Are all door */windows custom?
FOR do customers have the option of Custom/Standard sizes?
cpetermann
Bruce,
My dad ran a lumberyard for 40+ years,
where I had to dust paint cans & weigh nails.
Used to be able to tell the difference between them.
Now can only tell the difference by sight between Shingle nails & Finish nails
2 of my brothers were carpenters.
One of them sold windows for years.
The other is still a very talented finish carpenter.
Odabble ; I understand running board feet & 16 on center.
And still love the smell of sawdust!
Great to know that someone with industry knowledge will have my 6!!
Thanks!
bburn
Cynthia,
o. All my products are custom. To be very specific I have a niche maket wherein I fabricate energy effecient "replica" replacement sash for homes that might be considered historic. I have a product call the "magnum" which looks exactly like their existing window (100 years old) but with hp insulated glass and contemporary weather-stripping. These are almost 2" thick. I also make a true replica, identical to the original in EVERY respect and these are monolithic glass and either 1.375" thick or 1.75" depending on the original window.
For Bruce, two things...
First, just to prove I'm not totally inept, consider the fact that I may do several jobs on the same property. Also consider that a contract may have many customers (H & W, business partners, etc) and I've been beaten about the shoulders on this forum before about normalization, therefore - properties 1:m contract 1:m customers 1:m phones, same with addresses and emails.
Secondly, there might be a tblStyle that would contain as records IGHung, IGCase, IGDoor, StdHung, StdCase, StdDoor, StormHung, StormCase, StormDoor. It was suggested that this table be related to the tblSpecs that would (under this scenerio, which I'm not advocating) contain the records IG-WoodThick, IGGlassThick, IGStyleWidth, etc with their corresponding values in the Default field of the record. Again, that's a whole lot neater than having a table full of fields for each of those values but I'm afraid doing the hundereds of calcs might be troublesome since I still haven't seen an example of how to use anything but a field name in an expression.
Cool about your door experience. Unless it's forbotten on this forum please tell me more. BTW my web is www.heirloomwindows.com and my email address is there
cpetermann
Bill,
Could suggest that you edit out your email address asap!
Trollers & spammers you know <
cpetermann
Bill,
easurements in inches and fractions in decimals?
decimals to the nearest 16th or 32nd?
bburn
Never fractions. Only decimals to the 16th. Got'em all lernt :-)
cpetermann
Great!
ist them for me.
.5
.25
.75
.125
.625
.875
.0625
.1875
.3125
.4375
.5625
.6875
.8125
.9375
I did I miss any?
bburn
There are 15 of em'
0625
.125
.1875
.25
.3125
.375
.4375
.5
.5625
.625
.6875
.75
.8125
.875
.09375
I've been going to the shop to work between posts but it appears you're on it right now, so I'll stick by the puter. We could do this some other way if you'd like. A phone call or email?
B
bburn
Is there anything I can be doing to help? Otherwise I'm just setting here on my thumbs.
cpetermann
Bill,
You go work <
Gonna "play" with this awhile.
Will post when I have something to show you.
BruceM
Didn't mean to suggest ineptness. It was my error in assuming a model similar to the one with which I was familiar. It may be that the customer table in your scenario ends up being a junction table between the customers table and the contracts table, which would allow you to keep a master list of customers and still add customer records related to the contract records. I will leave further discussions of the design to you and Cynthia, since another voice may muddy the waters. BTW, there is nothing wrong with using field names in calculations. It's done all the time. It may be that you will eventually limit the recordset just to what is needed at the moment so that the program doesn't get bogged down with calculations you don't need, but again I will leave the design conversation to you and Cynthia.
The business itself sounds very interesting. I will check out the web site, and contact you from there rather than in this forum about tangential topics.
Cynthia, I could tell you had more than drive-by knowledge about woodworking and related topics. Now I know why <
Ostill build things when I can or when my 19th century house requires.
cpetermann
Don't sit on your thumbs!
You need them to hold lumber <
on't worry, there WILL be homework tongue.gi
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>cpetermann</div>
  <div class='postdate'>Sep 22 2011, 02:26 PM</div>
 </div>
 <div class='postcontent'>
  Bruce--<br />I'm also pretty good at Trivial Pursuit <
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 22 2011, 03:26 PM</div>
 </div>
 <div class='postcontent'>
  C,<br /> just looked at the thread and saw your "resume" for the first time. Cool! But what is running board feet? I also noticed that you had the decimals correct. When I first looked I didn't think so because they weren't in ascending order and didn't look any further. When people ask what I do I tell them I mainly make sawdust but historic windows are a by-product.<br />Sooner or later we'll get around to conversing about what I call the genspecs or list of every specification for every style of unit I make. I thought I'd get ahead of the game by sending you a zipped db in which the only object is the table that contains them. This list is only the specs for units with insulated glass. I'll also need to add all the specs for storm windows and storm doors as well as the specs for replica windows and doors which do not have IG units.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 22 2011, 04:12 PM</div>
 </div>
 <div class='postcontent'>
  C,<br />gain, sooner or later you might want to see some sample data. I put a spreadsheet together with actual contract info. It's attached.<br />Y />gain, sooner or later you might want to see some sample data. I put a spreadsheet together with actual contract info. It's attached.<br />YOur community theater is doing "A few good men". Due to my advancing age, I couldn't be one of the defendants. But I was cast as Cmd Stone, the head doctor that conspires with the Jack Nickleson character to lie about the death being due to poisoning. We have rehearsal tonight so I won't be back till after 9:00. You're on Central right?<br />B
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 22 2011, 04:19 PM</div>
 </div>
 <div class='postcontent'>
  Woops, I guess I have to zip all files and posted before I realized the xls didn't attach. Here it is.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>cpetermann</div>
  <div class='postdate'>Sep 23 2011, 04:48 AM</div>
 </div>
 <div class='postcontent'>
  Bill,<br />On the specifications you attached<br />Are the T/B allowances listed standard allowances for each product type Door/DH/Case<br />For are T/B allowances customized to meet Individual Property requirements ?<br />Hope rehearsal went ok!
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 23 2011, 07:05 AM</div>
 </div>
 <div class='postcontent'>
  Not sure what you mean by T/B allowances? But all the specs pertain more to the style or unit than they do the property. Even though my windows are "Custom" I've tried to standardize things, unless there is a compelling reason to vary. For example, if I notice that all the bottom rails in a particular property a bit bigger I might change the spec BRWidth for the entire job (or set the default to the greater width for this job only), otherwise only the height and width might vary. BTW, all the allowances are to give me some play with fit. For example the IG_W_Allowance is the amount I take off of the Jamb Width to determine the finish width of the sash. If I didn't it would fit too tightly, I guess a better term would be CutBack so I could abreviate CB.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>BruceM</div>
  <div class='postdate'>Sep 23 2011, 07:46 AM</div>
 </div>
 <div class='postcontent'>
  I don't know if this applies, but there are a number of ways to set defaults.  One is to use a table of defaults, another is to set the default value of a control bound to a field.  For the former it would typically be a small table, so DLookup doesn't cost much processing overhead.  For the latter, default value applies only to new records, so it won't affect existing records.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 23 2011, 08:07 AM</div>
 </div>
 <div class='postcontent'>
  Bruce,<br />I'm glad you reminded me that one could use a tbl of defaults and I think that's what Tina was suggesting. It might be easier to reference the value of field to temporarly change that value than referencing an attribute of the field and changing it's value. It's also good to be reminded that the attribute, default value, affects only new records. But you mention "small table", but what does that mean? Is 10 records small, is 100 records small, is 1000 records small? Kind of relative isn't it?
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>BruceM</div>
  <div class='postdate'>Sep 23 2011, 11:58 AM</div>
 </div>
 <div class='postcontent'>
  I sometimes use a one-record table for general information such as company name, path to a file, and other details that would be a nuisance to hunt down and change in several places if the information changes.  Fewer than 20 fields, generally.  I use a single record because the information in each field is dissimilar.<br /> also assemble tables of people who will be copied on certain e-mails, and things like that.  Maybe a dozen records.<br />However, a table of a thousand records is still pretty small by Access standards.  You can look up information in a few milliseconds, particularly if you index the fields that contain the information by which you will be searching.  Generally speaking, if I find myself repeating the same code or hard-coding the same information I look for a way to get the information from a single source, be it a DLookup in a query or text box expression, or a stored procedure that can be called as needed.  Such procedures may be used for calculations, to use one example of the uses of stored procedures.  In this example you could take all of the pieces of wood for a unit, add some waste allowance and other considerations, and calculate the number of board feet needed.  This is more about describing what can be done rather than trying to get into the specifics.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>cpetermann</div>
  <div class='postdate'>Sep 23 2011, 06:49 PM</div>
 </div>
 <div class='postcontent'>
  Bill,<br />orry, Fridays have one of our grandsons all day.<br />And now the two older ones are here for the night.<br />Haven't gotten anything to attach yet.<br />Another question:<br />Are  your clients homeowners, Companies, Foundations?
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 23 2011, 07:11 PM</div>
 </div>
 <div class='postcontent'>
  A) I don't believe you! You look too young in your picture to have grandkids :-)<br />) Could be any of the above. 90% are residences. But...<br />I just bid on a church<br />I've done two other churches in the past  <br />I did the building the local Visitors Bureau occupies<br />It's likely that I'll do the local Masonic Lodge<br />I've done work for Wabash College, our local all male LibArts school (at which my son is a Senior)<br />I've worked on the local Carnegie library<br />I did a building for a development firm that was turning it into Yuppy appts.<br />My customers come in all shapes and sizes like my jobs. If they've got money and bad windows I'll do 'em
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>cpetermann</div>
  <div class='postdate'>Sep 24 2011, 05:10 AM</div>
 </div>
 <div class='postcontent'>
  Bill,<br /> grandkids--#8 to arrive sometime around Christmas.<br />On another topic, I see that you have posted a couple of new questions in the forums.<br />These posts are asking essentially the same question that we are attempting to address in this thread.<br />Orealize that you are eager to get a solution up and running, however<br />starting new threads is probably not the best route to go right now.<br />You have to provide the same background info over and over <br />and most people who reply won't research your previous threads<br />to see what has been previously suggested.<br />We first have to get the tables set up and related correctly.<br />then the forms need to be created and you will need to test them<br />to see that the data is displaying correctly and in a manner <br />that is both efficient and intuitivefor you.<br />Then the qrys can be created to perform the needed calculations.<br />The qrys will need to be tested to see that the data returned is correct and as you require it to be displayed.<br />Reports come last.<br />Just as you can't put the glass in a window until:<br />the design is created,<br />materials calculated.<br />Components arranged,<br />wood milled, <br />Parts assembled,<br />the framework assembled,<br />and the mechanics work correctly.<br />I apologize if I'm not responding quickly enough,<br />but I would rather measure twice and cut once.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 24 2011, 06:50 AM</div>
 </div>
 <div class='postcontent'>
  Cynthia,<br />Hope you had fun with your gks. I heard that's the best part of the whole kid thing. With five kids, one of which married and two in college, my turn is not far behind.<br />Ocertainly don't want to be guilty of any breeches of forum etiquet. It's too valuable to me to become alienated.<br />I didn't make the last post out of frustration with you in any way,shape or form. I only posted to try to understand a design I was provided previously. It looked promising but complicated. I thought it bore futher scrutiny but needed help getting my head around it. I also really couldn't believe it was usuable for my application and wanted to confirm or reject that belief. It was an educational excercise. <br />Sorry if I offended. Have a good weekend.<br />Bill
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>cpetermann</div>
  <div class='postdate'>Sep 25 2011, 04:45 AM</div>
 </div>
 <div class='postcontent'>
  Bill,<br />i>Questions re Lumber:</i><br />Rough lumber?<br />Pricing -  Board feet?<br />Std Lengths/Widths or Custom?<br />2 x 4 x 8<br />2 x 4 x 10  etc<br /><b>Types of Wood</b><br />Ash<br />Beech<br />Mahogany<br />Maple<br />Oak<br />Pine<br />Teak<br />Walnut<br /><i>Questions re Glass</i>:<br />Std sizes?<br />Do you cut or sub that out?
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 25 2011, 08:24 AM</div>
 </div>
 <div class='postcontent'>
  Yes, it's rough lumber, random width (min 6" max 14" or so).<br />enghts predomantly 12' give or take six inches but occasionally 9 - 10' sometimes 14'<br />I've never tracked rough wood width and length data. <br />I've been using exclusively poplar<br />Wood: I use the following thickness for various window styles...<br />Insulated glass Magnum windows - 8/4 (you prob know about the "quarter"system but to determine thickness, it's just a standard fraction)<br />Standard Monolithic commercial - 8/4<br />Standard Monolithic residential - 6/4<br />Storm Units  5/4 if I can find it<br />Glass: Custom, just like my windows. Remember, I walk into a persons home and measure the height and width of their window jamb. I then build sash (1 if casement, upper and lower if double hung) based on those dimemsions. And as a matter of fact it's mererly a number of additions and subtractions to arrive at glass height and glass width from the jamb dims.... <br />However, I might point out that even in the 1860's most windows were built in millshops and therefore there was a great degree of uniformity in the product. For example, if I measure the jamb width today, and it varies from the next unit by 3/8 or less, I assume that they were initially the same size and something happend over time. I usually try to figure out how to do something to the jamb to bring it back closer to spec. Regardles, I don't make two windows within 3/8" difference. I'll make them the same size (frame and glass) then modify either finished sash or jamb into which it goes.<br />Currently I have a large mfg make the standard IG units for me but, I have and do build some units my-self, particularly geometrics (round tops, arch tops etc). I DO want to begin doing more in-house, but it takes quite a bit of floor space and I don't have it currently.<br />Unless you are REALLY good, I can't imagine how one could design a program that would tell me how to cut the random width 12' board to maximize usage for a dynamic list of window sizes. Too many variables. So I don't even try. That part is all intuitive.<br />Bill
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 25 2011, 08:43 AM</div>
 </div>
 <div class='postcontent'>
  Heading to Church this morn. I'll check for questions at lunch time. Then have play rehearsal in aftenoon but will be home all evening.
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>cpetermann</div>
  <div class='postdate'>Sep 25 2011, 08:48 AM</div>
 </div>
 <div class='postcontent'>
  leaving for church<br />what are the equations you use?
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>bburn</div>
  <div class='postdate'>Sep 25 2011, 12:59 PM</div>
 </div>
 <div class='postcontent'>
  Gosh,<br />There are LOTS! However, the ones below are very typical...<br />One of the first things I do is determine the overall dimensions of the finished sash. If it's an IG_Hung to find the width I subtract the units width allowance + an allowance for a jambliner from the jamb width; and for the heigth the unit's height allowance plus the jamb liner allowance from the jamb height. So, using the field names we've selected the expression would be thus<br />OASashWide = [All_JambWidth] - (2*[IG_HungW_Allow] + [IG_HungJL_Allow]) ... Default of IG_HungW_Allow = .09375, Default of IG_HungJL_Allow = .0625 (I have jambliners on each side and top but none on the bottom)<br />OASashHigh = [All_JambHeight] - ([IG_HungH_Allow] + [IG_HungJL_Allow)... Default of IG_HungH_Allow = .25<br />Another calc that includes the expression name above follows. It's to determine the glass width. We first have to determine the sight line (the viewable area of the glass) then add to that the IG spacer thickness (the metal that keeps the two pieces of glass seperated). I typically use many expression results as the value of expressions later in the calcs. In this example, IG_AllIG_SL is the value of the thickness of the IG spacers and does not change regardless of the Style of IG unit, hence it's name.<br />SightLineW = [OASashWide] - (2* [IG_HungStyle_Wide])<br />GlassWide = [SightLine] + (2*[IG_AllIG_SL])<br />I'll also need to determine the length I'm to cut the rails. But I have to consider that I'm adding .25" of weather-stripping to each side so I'd have...<br />RailLen = [OASashWide] - (2*[IG_HungWS_Allow])<br />Othen need to determin how much distance is between the shoulders of the tenons, the length of the tenons, and how far apart the mortises are going to be from each other. All these are only a few of many calcs I have to do for each unit size. Of course that's only field row in the query grid but many columns. <br />I don't expect you to put in all these calcs for me. For that matter I really don't EXPECT you to do any of this work but you seem to have graciously taken the lead in this. I'll put the calc in my self since I need to go through them one by one to ensure they are now correct since we've changed table names and field names. <br /> Again, thanks so much for the help <br />b
 </div>
</div><div class='postwrapper'>
 <div class='posttopbar'>
  <div class='postname'>BruceM</div>
  <div class='postdate'>Sep 26 2011, 07:00 AM</div>
 </div>
 <div class='postcontent'>
  I'm going to describe an example of a Defaults table.<br />blDefaults<br />  DefaultID  (primary key, or PK)<br />  Descr (text, for general descriptions things such as "Double Hung Jamb Width"<br />  D_Value  (number, probably Double, for default value)<br />Sample data:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->DefaultID   Descr           D_Value<br />    1      DH Width Allow   0.09375<br />    2       DH JL Allow     0.0625<br />   etc.<!--c2--></div><!--ec2--><br />Then you can make procedures.  Assuming the calculations will be needed in various forms and reports, in the VBA editor click Insert >> Module.  Go back to the databse window and give it a name such as basParam ("bas" is a standard prefix for modules, but I have to admit I can't recall why - use whatever suits you).  Also, there is a way to rename a module in the VBA editor, but all I can remember is that it is somewhat obscure).  The main thing is that the module needs to be a different name than any of the proedures.<br />Back to the new code module, go to it in the VBA editor, and click Insert >> Procedure.  Choose function and public, and give it a name such as DHWA (for double-hung width allow, but choose a name that is easy for you to remember.  I think it helps if the name is concise.<br />You will end up with something like this after adding code and other information to the new procedure:<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->Public Function DHWA As Double()<br />  DHWA = DLookup("[D_Value]","[tblDeaults]","[DefaultID] = 1")<br />End Function<!--c2--></div><!--ec2--><br />To test, go to the Immediate code window (press Ctrl + G if it is not showing), and type:<br />?DHWA()<br />and press Enter.<br />Then, in a procedure (the form's Current event, for instance, or the After Update event for the control where you select the type of window):<br /><!--c1--><div class='codetop'>CODE</div><div class='codemain'><!--ec1-->If Me.NewRecord Then<br />  Me.txtDHJWA.DefaultValue = DHWA<br />End if<!--c2--></div><!--ec2--><br />You can also use the function in an expression in a query or text box, if needed.<br />The point is that you can retrieve recurring values easily.  A further point is that you should retrieve them only as needed, which is why I suggest testing for a new record before calling the stored procedure.<br />This is all very general.  Cynthia is providing lots of good information.  I'm just trying to fill in a piece of the puzzle.<br />Repeat for other values for height and width allowances, and other values that are part of your calculations.  You can change the Allowance values if you need something other than the standard, but the point is that the record contains the values you are using, and you can perform calculations based on those stored values.<br />One more thing is that you need to be careful not to use reserved words such as DefaultValue for names, which is why I used D_Value in my example.  For more:<br /><a href=http://www.allenbrowne.com/AppIssueBadWord.html
bburn
Bruce,
That's what I've been wanting to hear and you expressed it well. The evidence of that is the fact that I understood it. Let me regurgitate what I think you said, so you can to check and be sure.
Create a table with the names of the attributes in which I'm interested as records in a single field with another field of the same record being the default value. I can then call into use procedures which I have appropriately named that merely provide the default values when and where I need them. I could also do the calcs in proceedures and likely I could call them directly into the reports I need, rather than using a query of all the calcs needed in a report as the record soucre of the report.
Thanks,
Bill
BruceM
Not to parse this too finely, but one table for all of your default measurements makes good sense. If there are lookups unrelated to default measurements, that may be another table.
The sample procedure I provided is the Access equivalent of looking up a value on a printed list. If you did not have Access, and hired somebody to help with the things you have described in this post, one of the things you would probably do is provide a list of default measurements that the helper could plug into equations. Instead of the paper list you have an Access table, and the DLookup table to take the place of scanning the list by eye. I showed a way of looking up the default value based on the PK value, but you could also use the text value. The syntax is a little more complicated, and the expressions are less concise, but it is certainly a valid option.
The sample DLookup expression is saying "find the record in tblDefaults where DefaultID equals a specified value, and retrieve the D_Value field from that record".
Having said this, and at the risk of muddying the waters, another option is to provide constants. I described adding a standard module that I called basParam. Another option for constant values is to use constants, so at the top of the standard module, below Option Compare Database (and Option Explicit), you could have something like:
Public Const DblH_JambW As Double = 0.125
Oprefer not hard-coding values in that way for this type of situation. I think it is much easier to manage with values stored in a table and accessed via a form. However, it may come up in your reading, so I'll mention it.
Back to Option Explicit. If your code modules do not include it at the top of the window, directly below Option Compare Database, you should add it. To assure it is included in new modules, in the VBA editor click Tools >> Options. On the General tab, check Require Variable Declaration. If you do not use Option Explicit, if Access finds something it doesn't recognize (a typo, for instance), it will assume it is a variable, and will probably use Null when it can't find instructions to assign it a specific value. This, of course, can lead to results other than what you intend.
cpetermann
Bill,
Try the attached.
There are still some forms that need to be added.
Perhaps even more tables.
Created an Estimate form--
There are tabs on this form.
Oput forms on three tabs.
To put forms on the remaining tabs,
copy one of the other tab forms
Then RecordSource qry change the criteria for StyleID to match the tab.
Let me know.
PS You too, Bruce <
bburn
WOW!
That a lot of work. I just opened the db and I'm overwhelmed by all the objects you've created. I also looked at the relationships and it appears they are very similar to what Tina designed. With two of you suggesting the same thing I should assume this is indeed the way to go.
HAs I go through the list of tables, I see some redundancy. Did you make some new ones and also use the old ones? Are there any I should del to simplify matters? Are these your developer notes or Tina's? I also see some tables wherein I may have miscommunicated the purpose or essence of the data.
I'd be happy to go into the tables myself and make corrections but I don't want to mess anything up. It's pretty simple really but I don't think I made it clear and you tried to sort it out for me. Here's a systematic breakdown of types, styles etc and I've added examples of the data they would contain.
tblOpeningType
1) Window
2) Door
tblFrameType
1) Storm (1.25 frame thickness)
2) Residential (1.375 frame thickness)
3) LtCommercial (1.75 frame thickness)
4) Magnum (2.00 frame thickness)
5) HvyCommercial (2.25 frame thickness)

tblGlazingType
1) Monolithic (single pane)
2) IG (insulated glass)
3) Storm (monolithic/screen)
tblUnitStyle
1) Double-Hung
2) Casement (hinge on side)
3) Fixed (picture or transom)
3) Awning (hinge at top)
4) Hopper (hinge at bottom)
5) Mulled (any of the above styles built together in one opening)
6) SideLite
I've just added these last four since I'm now thinking "tables"
tblStyleQty (the number of frames in a single jamb, this only applies to casements and doors)
1) 1 (one sash/door in jamb)
2) 2 (two sash/door in jamb)
3) 3 (three sash/door in jamb)
4) 4
5) 5
6) 6
Previously I had combined all these tables in only one or two tables, but it seems you've tried to break them apart a bit. I just didn't give you enough information so I'm illustrating how they should really be considered.
Please don't go to any trouble working with these things, you've done WAY more than I could ever have hoped. The fact is that 95% of my work will be with MagnumIGs, hung and casement or st
cpetermann
Bill,
This was Tina's db, made a few additions
but didn't relate any of the new tables in relationships view,
so will need to make those relationship changes.
Her forms are all still in place.
If it's alright with you, I would like to "play" a bit more.
Want to study what you just posted & see if this data could fit in an existing table.
While I do that, if you would "play" with the Estimate to see if data enters the way you need it.
And what else you would need in the underlying RecordSources to meet your needs.
If that works, you should be close to creating the queries <
The forms need to "feel" correct for YOU as the user.
They need to be intuitive and also easy to use if you add office staff.
Let me know.
bburn
Ok,
Since you asked...(and you've really got me thinking)
I've attached a db that only has one form. I'd really like my main UNIT input form to look similar to this (although I understand it may have to be broken up into subs). It has all the data I collect on site concerning ONE unit and this is EXACTLY what I need to input for any given unit with the option to open other forms to modify the specs from the defaults. Pay no attention to the record sources or anything but the data layout. I just threw some random fields on the form and gave them the lables that I want to see.
Here's the best and ideal situation for me....
Oopen the contract form and it has as continuous records a form almost identical to the one I'm showing you but without Room, RoomUnit, Height and Width. This is where I set the defaults of all the same styles of window in the contract (we use the defaults from the UnitSpecs as defaults for the default form since I try to keep things as uniform as possible). So I'd set the defaults for all the Double-hungs, Casements or Doors for the whole contract here. This makes sense since there is almost no variation of the frame specs from window to window within the same style in any give structure.
Style (Hung)
Frame (Magnum)
Lugs (Y/N) popup Luglength form
Muntin Y/N popup Muntin form for default muntin specs
Again, these should be defaults I can select for the WHOLE contract. I don't want to have to select them for every record.
Then we move to the Unit records form, also continious forms with a practically identical form but it includes those specs that will necessaraly change per unit like Room, RoomUnit, JambWidth, Jamb Height.
Then, on any particular unit record I would see the style and frame defaults with the option to change them - then input Room, RoomUnit#, JambWidth, JambHeight and that's all. But I need to be able to pop up any other exceptions which would include...
frmSpecException, All the frame member specs like...StyleWide, BottomRailWide, MeetingRailWide, TopRailWide and a few other items that I was calling General Specs and usually don't but might possibly vary from unit to unit
frmMuntinConfig, number of H & V muntins and if they are for top or bottom sash
frmGlass, to specify if the glass is to be tempered or maybe obscure
frmOperation, to specify if the windows are not to operate
...and maybe some others as I think about it but these are manditory.
C, please take a look at this form in the attached db. This is what I was trying to accomplish when I originally posted (how and when to use popup forms). Any more or different fields in the main unit input form will be unnecessary and take up valuable space on the line.
What I really wanted to know was how to link parent and child in these popup forms. I got a lot more than I bargained and I've very grateful to you. The db is much more robust and flexible than what I could have ever designed and it's nice to see how pros design forms. But I'm still convinced that the minimized record (one line) with popups is the best way to collect the pertinent data, since there are so many POSSIBLE but unlikely variations, while still allowing the option of adding other unusual details on the fly.
A notes popup for each unit would be good too.
Thanks so much,
B
bburn
C,
've attached a better representation of how I'd like to see the input forms. Same thing I just said but more detailed. Again, "pay no attention to the man behind the curtain" (Frank Baum, Wizard of OZ), these are just a bunch of fields I added to show you the kind of thing I'd like to see (but I know you can make it prettier).
B
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.