Can I build a single use, single record form?

[Mimadocken]

I'm building a database for my knitting group that will help them find all those wonderful patterns they have in stacks of magazines, and then adjust them to fit better and recalculate the instructions from centimeters to inches (or visa versa) depending on how they prefer to work.

So, my idea was to have a very simple form based on a super simple table. You fill in your name and then place a check into the checkbox IF you work in inches. Then you click a button to indicate you've finished. Once you do that, the form would disappear and the table wouldn't accept any more information.

On another form, you enter information about a neat pattern you've found INCLUDING checking in a checkbox IF the pattern is written in inches.

Finally, I want to write a formula that says If the Owner form has a check in the checkbox AND the pattern has a check in its checkbox, then no translation is needed. However if the two checkboxes disagree, then If the Owner checkbox says inches and the pattern checkbox says centimeters, then translate the pattern to inches. If the Owner checkbox says Centimeters and the Pattern says Inches, then translate the pattern to centimeters.

My problem seems to be with the Owner form. How do I allow this form to appear, allow someone to fill in the spaces ONCE and then make the whole thing disappear, while allowing the data to be used in formulae later on? OR is there a better way to handle this than I'm thinking of?

Any ideas are appreciated.

 

[NickHa]

Presumably you have a table of user names? Why not add your units field to that table, then show the checkbox on the user's 'home' form only when it hasn't been set? You would need to allow three values in this field - 'not set', 'use Centimetres' and 'use Inches'.
I would consider using radio buttons, rather than a checkbox - so that the user has to make a deliberate choice of units (as opposed to a default setting). The same applies to the pattern units as well - some people will not see the relevance of having the units determinant and will not select it if it's not mandatory. In both situations, you can enforce a selection by validating before saving updates.

Thinking about the units, how do you record measurements in inches? Do you use 1/2 or 0.5 for half-an-inch? What level of precision do you want on the conversion - is 1 inch = 2.54 cm or would you round it to 3cm? And in reverse, is 3 cm 1.18 inches? I think that would be confusing!

You could show both sets of units on every pattern (two columns) and highlight the users' preferred choice. That would allow any conversion discrepancy to be seen. How will users see the output - on a screen or printed?

Do you need help with the translation formula? I'm thinking you would use a query from your two tables as the row source for a form (patterns?) and the units would just show in the users' preferred measurement units? Do you want the form to state the units in its labels? That would involve VBA in the form's current event.

 

[Mimadocken]

Hi Nick,
Thank you for the quick response. I'm sure you can help me with this from your responses, but I need you to change focus with me.

I needed to change my thinking a lot and drag my brain out of the office for this one. This is being done for the home market. (I don't want to encourage people to knit at their desks. The bosses frown.)

So each copy will be a stand-alone database and have ONLY one user who will PREFER only one form of measuring and reading knitting patterns. Once they have set those preferrences, they will remain. Few people decide to think the opposite from the way they always have overnight. I'm thinking Runtime so anybody can run it. (At least I'm hoping it will work that way. Can Apple's run Access with Runtime?)

This database will NOT contain the whole pattern, but simply contain information that will send them to the correct book or magazine. (I'm also not encouraging copyright infringement thereby.)

Since you are male, think woodworking and how a woodworker collects pattern books and woodworking magazines. How does he find the pattern for that perfect chair he saw two years ago and drooled over, but didn't have the time or materials to make then? Same thing, but with people who take string and two sticks and end up with something beautiful to wear.

We knitters have to make gauge swatches (we knit a rectangle with a certain number of stitches and rows and then we measure it) so we know how many rows and stitches we need to knit to make a pattern piece the right size. Therefore the conversion requirement. The original pattern says I need to achieve a gauge of 36 stitches and 42 rows within a 10cm square area of knitting on my swatch. Then I can follow their directions and end up with a garment that is the size I need. Otherwise, if I'm getting 37 stitches by 41 rows in 10 cm, the sweater will be too wide and too short. AND, if I only think in inches and I make my swatches to have a measured area that is whatever I get from an area with 50 rows and 50 stitches, how do I translate that to see if my gauge matches theirs? And how do I get something the same size and shape if I need to use yarn that's a different size than what they've specified because their yarn is no longer produced? (By the way, the same needles and yarn will produce different gauges from different dye lots, so it just keeps getting more challenging for the knitters.)

WHEW! Sorry this is so long winded, but I wanted you to understand what I'm trying to build here.

Actually most of it is done. I can put the pattern information in with the book and page to find it and the specs they gave, along with a scan of what they say it will look like. I can also look at another form and see all of the patterns that fall into a category/sub-category sort, so I can see all of the Men's Sweaters (Jumpers) and not have to dig through the baby outfits, and it tells me what book and page to find it. Now I'm trying to make the Switchboard where the user decides what they want to do next and click on a button to take them to the correct form. I'm also working on the math.

Hope that helps you help me...and thanks again. I know it can happen. I'm just not sure exactly how...YET.

 

[Mimadocken]

I got into one mindset and forgot to say "thank you" for the radio button suggestion. I'll do that right away. Much more positive.

Also, I'm calculating to 2.54 cm per inch, again because we want these things to fit real people. Therefore, I will also be using decimals to 3 decimal places (3.875) for accuracy's sake. (Just because people who use inches end up with 3 decimal places, I will also display a chart with the fraction/decimal conversions.)

Hope that helps, too.

 

[NickHa]

I don't want to encourage people to knit at their desks. The bosses frown.

I would have thought a bit of knitting would help thinking at work - not enough of that goes on!

Since you are male, think woodworking and how a woodworker collects pattern books and woodworking magazines

A very sexist comment, if I may say so? What makes you think that mere males can't knit? As a matter of fact, I did learn to knit at school - more years ago than I care to admit.

Anyway, to business...

Your background information is helpful (in spite of my comment above). I'm not conviced that converting inches to centimetres at three decimal places of accuracy is a good idea, because 1.234 Cm is 12.34 mm and .34 of a millimetre is very hard to judge by eye (especially on something which is not rigid, such as wool). Likewise, converting Cm to In at 3 dp is hard to relate to a real measure. For example, if you have .456 as the decimal result of a calculation, that equates to 29/64 inches (approximately), which agian is hard to judge. it's a long time since I converted from using inches to using centimetres, but I would still want to see the vulgar fraction, probably in eighths of an inch. I would recommend giving your community a measurement such as 3 7/8 (for 10 Cm). It's not a hard algorithm to implement.

On the point about user selection of units, is this something you can incorporate within the switchboard? I presume you will show this form when the DB is opened? You might have an item which says "Units of measure", which when clicked, makes the radio buttons frame visible (on the switchboard, to save having another form). When a button is selected, hide the frame. This gives your users flexibility to change if they so wish. I would avoid the run-time switch, because of the overheads that would impose on you as the distributor.

If you need further help or ideas, please let me know.

 

[Mimadocken]

What makes you think that mere males can't knit?

Not only do I believe males can knit, (there are several in my group on Ravelry and a couple in my guild) but they INVENTED knitting in order to make fish nets.

I'm going to defend my calculations to 3 decimals, just because the difference between multiplying the number of stitches per inch at 3 decimals or one across a 23 inch span can mean the difference between a beautifully draping garment and something that droops. As I said, I will provide a chart to show the fraction/decimal conversions. We knitters are used to that because we need to do this all the time. If you know a way I can display measurements in fractions, I'd love to hear about it. Decimals on computers are all I know.

We ARE thinking alike on the switchboard. That's where I wanted to add my single use, single record SUB-form that will disappear after it has been completed. Which reminds me...I saw somewhere that switchboards are limited to 8 command buttons. Is that true? I'll need at least a dozen.

Thanks for the toggle button suggestion. I now have option groups with toggle buttons for selecting centimeters or inches. Thanks also for simply saying the words "Units of Measure" because I've been racking my brain for the right words and there they are.

I can't tell you how much I appreciate your thoughts on this, despite my sexist comments.

 

[Mimadocken]

Another clarification. I'm more interested in the 3-decimal thing because of the stitches per inch when expanded to wrap around a whole body. It's the cumulative effect of the pieces of a garment being just a bit too large or small.

Thank you so much for hanging in there with me on this.

 

[NickHa]

I'm going to defend my calculations to 3 decimals, just because the difference between multiplying the number of stitches per inch at 3 decimals or one across a 23 inch span can mean the difference between a beautifully draping garment and something that droops.

OK, we'll agree to disagree on this point. Besides, I'd hate to be held responsible for any droopy garments.

By Switchboard, I assume you are referring to the built-in Access Switchboard manager? I'm not aware of any limitations on the number of command buttons, but you may be right. I used to use the SBM until I understood it, then I changed to my own version, which incorprates a number of custom functions. If you think about it, the Switchboard is just a form with a table behind it and some specialist functions thrown in. It's quite easy to produce your own custom version, which sounds like it may be better suited to your needs(?)

In terms of the units of measure, I'm not sure that it warrants a sub-form (unless you will include other parameters). I had in mind something which is on the main form, but is shown or hidden by selecting one of the switchboard items. It's not that important and ultimately, it's what works for your user community which counts.

On the conversion algorithm, I have in mind a public function which can be placed in a module so it can be called from more than one form. It would take parameters to indicate what direction the conversion is to be done and the string value to be converted. It would return a string set to whatever precision you define (see opening comment - LOL). Does this sound right for what you need? I'm assuming strings for the values, given the context - in any case, a vulgar fraction would be presented as a string. I'll work on this and give you the result in a day or two.

As a matter of interest, in what part of the world are you located? I notice you use American spelling for centimetres, where I use the English (i.e. French) spelling. That's because I'm a true Brit - but that's an entirely separate debate. The reason I ask is from curiosity about countries which have both Imperial and Metric unit users. I was living in Australia when it converted to metric, which is where I changed (1973, if I remember correctly?). I've used metric ever since - which has some interesting challenges when dealing with things like fuel consumption - where the UK is thoroughly confused about its units, having defined them to be SI about 50 years ago, but never quite got around to making it happen.

 

[Mimadocken]

Besides, I'd hate to be held responsible for any droopy garments.

And I'd hate to have you appear publicly in any.

OK. I've been outed. I'm from Minnesota in the USA, the only place on earth to have willingly elected a professional wrestler as their govenor. So, now you know what kind of logic you are up against.

I've always used a blank table for a switchboard. I got scared the other day when I ran into some information that implied I would have to use a real switchboard and be limited to 8 controls. I've always found the built-in switchboards ugly.

I would love to have your help with the programming part of this. I've been building Access databases since v1.0, but I've never been able to get the actual code writing into my head. I know what I want to make it do, I just can't get the computer to understand all of it. (I can do simple docmd statements, but that's about it.) Let me know if you need to see some tables or whatever. I'll figure out how to PM them.

Thanks again, and please forgive the fact I live in Minnesota. I'm really kind of nice, despite all that.

 

[NickHa]

the only place on earth to have willingly elected a professional wrestler as their govenor

Not without precedent from a country which elected an actor as its president - and look at California! Czechoslovakia elected a playwright as its president, so you could say there's a parallel there somewhere.

please forgive the fact I live in Minnesota

I'll try - but it takes a lot of effort. Actually, I've been to Minnesota (if Minneapolis counts?) and I didn't get anything other than good hospitality and friendship from my hosts. It was cold in January, though! I'd just arrived from Florida, which was hot by comparison.

So, leaving the watercooler part ...

I've had a look at a number of knitting patterns (e.g. http://www.patonsyarns.com/pattern.php?PID=4521&cps=21191) and I was surprised to see all measurements in whole inches or centimetres to 1 dp. My preconception of '6 7/8 Inches' type of meaures was nowhere to be seen, which means my intended conversion algorithm is not necessary (but an interesting problem, nevertheless). On that basis, a simple function will do perfectly well, along the lines of

Public Function convertUnits(ByVal pFrom As Byte, ByVal pUnits As Single) As Single
Const kFactor = 2.54
Rem determine conversion required from input parameter
Select Case pFrom
Case kInchesToCentimetres
    Rem convert pUnits from inches to centimetres
    convertUnits = pUnits * kFactor
Case kCentimetresToInches
    Rem convert pUnits from centimetres to inches
    convertUnits = pUnits / kFactor
Case Else
    Rem unrecognised conversion - just return the unconverted value
    convertUnits = pUnits
End Select
End Function

This is declared Public in a public module, which gives visibility to all parts of the project. It needs the following public constants at the head of the module, so all callers can use them.

Public Const kInchesToCentimetres = 1, kCentimetresToInches = 2

This is a test subroutine which can be put wherever convenient:

Private Sub test()
Debug.Print Format(convertUnits(kInchesToCentimetres, 10), "0.000")
Debug.Print Format(convertUnits(kCentimetresToInches, 254), "0.000")
End Sub

 

[Mimadocken]

I just came back to see if you had responded to my last note, only to find that it wasnt' there. Don't know what I did wrong.

OK. As it said, you are about to find out what a dunderhead you are dealing with here. I told you I'm not a programmer. It looks brilliant, but I honestly don't know what to do with your code. I'm sure it gets put into the Expression Builder area where I do my simple docmd coding, but that's about it.

The user will input their Unit of Measurement preferrence on the Switchboard (or rather my form that passes for one.) I thought that was the best place, because they will enter it only once. It's a preferrence that won't likely change very often.

There is a form where the user will enter information about the patterns they want to track, and one of the fields will ask for the UofM used by the pattern's author.

From your code, I can see there is something called a kFactor, there's a pUnits, and there's a pFrom. I understand KFactor it's the conversion number for changing inches to cms and visa versa. I'm sure pUnits must refer to one of my UofM fields, either the tOwner.PrefMeas or the tDescrip.OPMeasType and I am assuming that the pFrom might be the other of these.

Is my thinking reasonably clear thus far?

If so, then where do I declare this function? I have a form fMyProject with a subform where the Owner/user will look at the Original Pattern and calculate the different measurements to determine which set of numbers will return a pattern piece with the correct dimensions for the body of the recipient. This is the only place where I thought I'd need to do that calculation. My plan was to have two fields, one for input and one for outcome. My hope was to have the labels for these fields be affected by conditional formatting, so that IF the Owner/user entered inches as their Preferred Measurement Type (PrefMeas) when they filled out the info on the Switchboard AND the Pattern they chose for this project (on fMyProject) had a description (on tDescrip) that included info that the Original Pattern's Measurement Type (OPMeasType) was centimeters, the input label would suddenly say "Centimetres" and the output would be "Inches". The opposite labels would actually be there as well, but would be set to Visible - No. And the math would move things in the right direction.

I appreciate the hard work you did on this. Were we thinking along the same lines? And, can I do what I was thinking with what your wrote? Oh, and then there's the Module. I think I need to put that into something in the VB coding as well, but not onto the Expression Builder main page along with the larger piece of code.

I look forward to you coming back and saying something like "Silly Old Bear" and going into your Christopher Robin routine.

Gratefully...and humbly.

 

[Mimadocken]

I keep failing to make an important (at least in my head) point. Sorry.

I'm sure you are curious where the Owner/user will get all those wonderful "knit one/purl one" instructions. Well, it will have to be kind of a symbiotic relationship between the Original Pattern and another subform on the fMyProject form. On that one, they will enter names for each rectangular section of each pattern piece and the dimensions for it (height & width). There will be a formula in a couple of fields (stitches & rows) that will multiply information taken from their own swatch (which will have been measured by them and so not need any conversion) and will tell them how many stitches and rows they'll need to knit to attain that section of the pattern if they knit it using the same tension and yarn that was used in their swatch.

I hope that clarifies how I had intended to do this. Now you can tell me how far off the mark I am.

And, thanks again.

 

[NickHa]

Aha! I wondered where you'd got to!

I look forward to you coming back and saying something like "Silly Old Bear" and going into your Christopher Robin routine.

Now how do you know I'm a fan of Winnie Ther Pooh? I grew up having AA Milne books read to me - take care not to encourage me, 'cos I'm likely to start quoting James, James, Morrison, Morrison ... at you.

I honestly don't know what to do with your code

What makes you think I do? Try this:

With your DB open, go to the 'Create' tab in the Ribbon (I'm assuming Access 2007 or 2010 here?). Look for Macro (in the 'Other' group) and from the drop-down box, select 'Module'. The code window will open, perhaps with some pre-populated statements, depending on the options you have set). You can copy and paste the code for the function and its public constants into there (the constants at the top). You can put the test sub here as well, if you want to see it working in practice. When you run the test, open the Immediate Window (<ctrl> G on keyboard or from View menu).

To call the function, you will see it listed in the Expression Builder, where you can use it like any other function. It may be better if you give me a concrete example of where you want to use the function so I can be more specific.

From your code, I can see there is something called a kFactor, there's a pUnits, and there's a pFrom. I understand KFactor it's the conversion number for changing inches to cms and visa versa. I'm sure pUnits must refer to one of my UofM fields, either the tOwner.PrefMeas or the tDescrip.OPMeasType and I am assuming that the pFrom might be the other of these

My apologies for lack of clarity.
kFactor is simply a named constant whose value is 2.54. I tend to use named constants where a number is re-used in many places - the advantage is that you only have one place to change if it becomes necessary (but I don't anticipate any change to the number of centimeters in an inch any time soon). The same applies to kInchesToCentimetres and kCentimetresToInches (you may have deduced by now that my naming convention is to use lower-case k prefix to denote a named constant. By the same token, I use lower-case p prefix for parameters to my subs and functions - hence pUnits & pFrom). Going back to kFactor, this is only used within the function, so is declared there and used twice - once as a multiplier and once as a divider - to give the conversions. Similarly, pFrom is the determinant for which conversion is to be done and its value will be either kInchesToCentimeters (=1) or kCentimetresToInches (=2). pUnits refers to the source field (such as the number of inches or centimetres to be converted - not the preferred units, which is what I think your field tOwner.PrefMeas represents).

Is my thinking reasonably clear thus far?

Sure is! Is my explanation ditto?

My plan was to have two fields, one for input and one for outcome.

Yes, that's what I had in mind as well. The input field would be passed to the conversion function and the function's output would be passed to the outcome field.

the input label would suddenly say "Centimetres" and the output would be "Inches". The opposite labels would actually be there as well, but would be set to Visible - No

Why not have one label only for each side and change its .Caption property to the relevant units? I think the conversion function could do this, with some careful design - but that may be a step too far at this stage.

I'm sure you are curious

well spotted, but let's not get personal.

Now you can tell me how far off the mark I am.

I think you're doing fine! What I like is the level of thought you are putting in to this, which is why I'm willing to help.

When you feel ready, perhaps you could post your DB for me to look at and make suggestions based on reality - rather than the theoretical level I'm at now.

Tolstoy will have to look to his laurels (were he alive), based on the volume of correspondence in this thread!

 

[Mimadocken]

Hey Nick!

My head full of stuffing just grasped the whole constants stored in a module thing. I can't thank you enough for hanging in there. I'll be brief for now, but will be back very soon with major successes or more questions.

See you on the flip side (as they said in my youth...when dinosaurs walked and volcanoes spewed, as my daughters used to respond.)

By the way, I am very excited about having grasped even one more concept.

 

[Mimadocken]

There was much excitement in my small brain last night and I put together the following Constants in a Module. But of course it brought up more questions, so here I am.

First, here are my Constants

Option Compare Database
Rem Constants are entered with their Data Type and value
Public Const kMPerYd As Double = 1.093613
Public Const kYdsPerM As Double = 0.9144
Public Const kCmPerM As Integer = 100
Public Const kCmPerIn As Double = 2.54
Public Const kInPerYd As Integer = 36

Public Const kLbPerKG As Double = 0.45359237
Public Const kOzPerLb As Integer = 16
Public Const kOzPerGm As Double = 0.0352739619
Public Const kGmPerOz As Double = 28.3495231


OK, so some of them are pretty silly, but there they are as constant as can be.

Now come the questions. You had constants shown that were

Public Const kInchesToCentimetres = 1, kCentimetresToInches = 2

They have no data type, but I wasn't able to declare a constant without one. I think maybe they are function names???? and I misunderstood. Is that possible. (I don't mean "Is it possible I misunderstood?" because that IS a constant. I mean is it possible that KInchesToCentimetres = 1 is a function name?)

I'm trying now to wrap my head around the Public Function you wrote and the stuffing keeps trying to fall out of my ears.

You requested a copy of what I'm working on. Well, by now it's getting pretty large with the data file over 50Mb and the engine file over 10 Mb. Is there a way (not too public) for me to send this to you. I think you'll enjoy looking at it, and I KNOW you'll be able to help me calculate the right things and correct some wonky bits. (How's that for a "Brit" phrase?)

I believe I'm working on the last 2 subforms right now. Then I have clean-up work on a couple forms that aren't QUITE right and command buttons that need to be added along with docmd instructions that when I open a form from the command button on another, I will often want to close the first form. Finally there will be reports to write and How To documentation. Some people aren't comfortable without paper documents filed with their swatches and other things they keep after finishing a project. (One of my guild members has taken first place in the Minnesota State Fair - second largest in the country - in the machine knitting category for about the last 10 years. Her notes are as meticulous as her knitting. And, she has been generous enough to be willing to test this and make suggestions before I start to distribute it to the others.)

How do you suggest I send this? I've never PM'd anyone here.

 

[NickHa]

I mean is it possible that KInchesToCentimetres = 1 is a function name?

No, that is a constant.

They have no data type, but I wasn't able to declare a constant without one.

What was the error you got? I haven't come across this, but I suspect it's a setting in the VBA editor for your copy of Access. You can't declare Public constants in a form module, with or without the type, so I think you had them in the right place (a stand-alone code module)

I pasted your constants list into a module and removed the type clauses and it worked OK for me. In the VBA Editor window, go to Tools, Options and in the dialog which opens, set all of the checkboxes in the Editor tab. I don't think these will make a difference to this behaviour, but it's worth a try. Maybe someone else will know the answer.

I'm happy for you to send your DB by PM.

 

[Mimadocken]

In the VBA Editor window, go to Tools, Options and in the dialog which opens, set all of the checkboxes in the Editor tab. I don't think these will make a difference to this behaviour, but it's worth a try. Maybe someone else will know the answer.

Hello Again Nick!

Since we chatted last, I have sent you my DB and I went to the VBA Editor window and did what you said above. The amazing part is that when I got there, the only thing that was NOT checked on the General tab was "Require Variable Declarations".

It is checked now, and I don't mind declaring variables, but since I AM a bear of very little brain, maybe you can tell me what declaring variables does for me, and if I should go back in and uncheck that.

Hope you are well. I haven't heard from you for a few days. Did the DB get through to you OK???

 

[NickHa]

maybe you can tell me what declaring variables does for me, and if I should go back in and uncheck that.

This option causes the statement

Option Explicit

to be placed at the beginning of modules.

With this statement present, it means every variable must be named before it is used (athough it doesn't have to be given a data type).
The advantage is to avoid mistakes in typing variable names which can cause logic failures within the code. These can be very hard to find because one's mind reads what was intended, rather than what is keyed (well, mine does, anyway).

I consider it to be good practice to declare variables before using them and I can't think of any good reason not to do so (but the latter is a matter of opinion). Should you uncheck the option? Personally, I'd say leave it checked.