1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2003: Doing a calculation within a form

Discussion in 'Business Applications' started by classixuk, Feb 15, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. classixuk

    classixuk Thread Starter

    Joined:
    Dec 12, 2006
    Messages:
    11
    Quick Summary of the Problem

    Hi Everyone,

    I've been tearing my hair out tonight. I'm new to Access and have been reading books for 2 days solid.

    I have created a simple database but have run into a problem.

    I have 2 combo boxes at the top of my form for the user to select 2 fields. From these 2 fields I need Access to calculate "SelectionA" minus "SelectionB" to give me a new hidden value that I can use "HiddenNumber3".

    I can get this to work in queries without a problem, but how do I get this to work in a form?


    A bit more indepth info for those who are interested.
    My Access application is to allow a user to select what hair-colour they currently have and then a new colour from a list of hair colour names. The results tell the user what product to put on their hair, mixed with which peroxide and for how long in order to acheive the result.
    It all begins with the difference between the 2 shades. In my table of natural hair colours I have provided a field that gives each shade of natural hair colour a number.
    I have done the same with the artificial hair colour table.
    Via a query I can calculate the difference between the 2 shades fields without a problem. In the form, I am stuck. Not only am I stuck, but I don't know how to store the result to be used throughout the rest of the calculations.

    Another thing that I cannot seem to work out in Access is how to make everything happen on one form. Currently I have 6 queries in place, each returning a different result I need. Those 6 results will be combined with textlabels on a report to look like a set of instructions. All I want the user to do is select the first 2 options, and then see the results. Any tips on this type of thing?

    Thanks very much.

    Chris.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, this sounds like an interesting application of Access, one I haven't come accross before. Is it a School or College Project?

    The calculation in the form is most easily achieved using Visula Basic code, (Macros in Excel).
    In the Design Properties of the Combo Boxes you will find a Tab called "Event", when you click that you get a list of all the different events that can occur for a field, combo, list box or label. The one that you want is the "After Udate" one.
    Click on that one and a drop down arrow appears, Select "[Event Procedure]".
    When you select the "Event Procedure" 3 small dots appear to the right of the drop down arrow. Click on the 3 dots and Access takes you in to the Visual Basic Editor on the Procedure that it has started for your Combo.
    Type the following
    me.HiddenNumbers3 = me.combo1 - me.combo2

    where HiddenNumbers3 is the name of your text field, combo1 is the name of the "SelectionA" Combo and combo2 is the name of the "SelectionB" Combo.
    When you enter the me. the visual basic editor's wizard will offer you a list of all of the "actions" that can happen along with all of the fields, combos, labels and controls on the form.
    This code needs to go in both Combos in case the user goes back and cahnges the first one.

    Your second question is a little harder to answer without actually seing what you have and getting an idea of how you want to display the data, but it sounds like a "Report" would probably work best although it could also be done on a form as well.
    If there is something that "links" the 6 queries then you could build a 7th query that uses the other 6 queries as it's data source thereby providing all of the data elements for your Report or Form.
     
  3. classixuk

    classixuk Thread Starter

    Joined:
    Dec 12, 2006
    Messages:
    11
    Thank you OBP for a very helpful reply.
    This application is hopefully going to be used in my 2 hairdressing salons by my staff for when they need a "virtual me" there to double check a formula.

    I have followed your reply, but have hit a small problem in the execution. (I've attached the database of tables and queries so far incase that helps).
    In Combo Box A and Combo Box B I have a list of the colour names as text (e.g. fldExistingColourName and fldTargetColourName). When using a query to calculate the numerical difference between the 2, I query the number for fldExisitngColourNumber and fldTargetColourNumber from their respective tables, and then use an expression field to calculate the difference. (Excuse me if I have the terminology wrong, as I said, I have only been using Access for 2 days).

    I think I need to be able to do this calculation "behind the scenes", rather than have the query window pop-up.

    I know I have enough data to perform the calculations and give the results required as my 6 queries do that. I'm not sure if I will be able to place them all into one query though because there are 2 possible procedures, depending on the level of lightening required.

    If The difference is <=4 shades lighter, the user will be told to mix a product with a certain strength of peroxide and leave it on the hair for X minutes.

    If the difference is >4 shades lighter, the user is told to lighten the hair to a certain colour before applying the target colour.

    Thanks for reading this rather long-winded reply, and thanks for your advice so far.

    Regards,
    Chris.
     

    Attached Files:

  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, sorry for the delay, I had to finish work on 2 other poster's databases before I could get on to yours.
    I have added a field to your Form that is the Difference between the selections in the Combo.
    I have also changed the combos, I have addded the Base Colour to the first and the Colour Number to the second.
    I would like to assist you further with this, so what do you want to do with the Form's Difference now that you have it?
     

    Attached Files:

  5. classixuk

    classixuk Thread Starter

    Joined:
    Dec 12, 2006
    Messages:
    11
    Hi OBP,
    Thanks once again for your help with this. Going through the changes you made and looking at the VBA window I was able to learn from what you had put into place.
    Your apology for the delay was gratefully received yet quite unnecessary on your part. I understand these are very busy forums, and I am grateful for any help you are able to offer. I think that experienced developers like yourself taking any amount of time out to help interested newbies is an admirable thing.
    I'm also extremely grateful of your offer of further assistance with my little project and the offer is warmly accepted.
    You asked what I would like the form to do next. At the moment (whilst I am learning), I need the application to do the following with the difference value:
    1. Check to see if the difference value is >4. If so, it needs to get the results of the query I have made called "qryPreLighten". From these results, the form will then need to output this: "Before you apply any colour, you will need to do a [fldTechName] until the hair turns [fldUndercoatShade]. In order to do this you will need to [fldTechDescription]. When you have done this, shampoo and cleanse the hair without conditioner, blast it dry and then apply [target colour value from combo box 2] with 3% peroxide and develop for [fldDevelopmentTime]. STOP
    2. If the difference is =<4 then the form needs to get the results of the query I have made called "qryPeroxideChoice". From these results, the form will need to output this: In order to acheive this result, apply [target colour value from combo box 2] with [fldStrength] and develop for [fldDevelopmentTime]. STOP.

    If we can get the form to do that I will be thrilled!

    There are 2 more stages I will have to do after that in order to have a fully working application because if the hair is grey or previously coloured, it makes a difference to the results. Right now though, if we can concentrate on the above it would help my understanding of Access a lot.

    Thanks again OBP.

    Regards,
    Chris.

    P.S. I have re-attached the database as I had to make a small change in your VBA expressions (you had the columns the wrong way around for the calculation so I was getting negative numbered results where I was expecting positive and vice versa).
     

    Attached Files:

  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, I can see from how much you had done in the Database in 2 days and how quickly you have picked up the VBA that you learn quickly. You should get an aweful lot rom this little "project". :)

    I think we can manage to get it to do what you want, I will get back to you when I have the next phase ready for your testing and checking.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Chris, did you receive the email with a copy of the latest version of the database?
     
  8. classixuk

    classixuk Thread Starter

    Joined:
    Dec 12, 2006
    Messages:
    11
    Hi OBP,
    Yes thanks! I've sent you an email today. Sorry for the delay and thanks for your continued help!

    Chris.
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/544221

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice