Results 1 to 9 of 9
  1. #1
    scoobz1234 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    41

    Transfer data from Unbound Textbox to Bound Textbox

    Good Afternoon!



    I have been searching but am unable to find out how i could do this. i have an unbound text box that calculates values from three bound text boxes. i then need to save that calculated value, so i am trying to transfer the data from the unbound textbox to a bound textbox. can't seem to figure that out. my unbound text box's source is

    Code:
     =Val([txtHip].[Value])+Val([txtWaist].[Value])-Val([txtNeck].[Value])
    also i would like all these text boxes to be not visible and locked, so it would have to update automatically without click or onupdate or anything of that sort.

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    Do not save the calculated value. There is no reason to do so. You can do the calculation at the run time of any query, report or form. Storing calculated values is a very bad idea.

  3. #3
    scoobz1234 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    41
    Useful things.zip

    if i dont save this value, i can't see any way to do what i am ultimately trying to do... i use three values to calculate a circumference value, thats what i posted above. then with that value i need to use the query to cross a persons HEIGHT then their CIRCUMFERENCE value, and then from those values it will show their Body fat %

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    Can you convert your database to 2003 (preferably) or 2007 if you have no other options I can't view 2010+


    What you want to do is still possible strictly with queries.

  5. #5
    scoobz1234 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    41
    im currently using access 2007, my home access is 2010 but i am at work

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    Ok. First I changed your sample data to actually get data that was in the range of your tblMale_Standars (by the way you can put both female and male standards in the same table, just put in an additional field with the gender so and create another link via gender in the queries below to make it work)

    For person 1 I have HIP 15.6, Waist 20, Neck 15
    For person 2 I have HIP 15.8, Waist 21, Neck 18

    First I created this query:


    Code:
    [SELECT tblBCP.BCPID, tblBCP.HEIGHT, tblBCP.HIP, tblBCP.WAIST, tblBCP.NECK, [hip]+[waist]-[neck] AS Circum, Int([hip]+[waist]-[neck]) AS CircumCalc, IIf([circum]-[circumcalc]>0.25 And [circum]-[circumcalc]<0.75,0.5,IIf([circum]-[circumcalc]>=0.75,1,0)) AS Remainder, [circumcalc]+[remainder] AS Final, (Int([hip]+[waist]-[neck]))+(IIf(([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))>0.25 And ([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))<0.75,0.5,IIf(([hip]+[waist]-[neck])-(Int([hip]+[waist]-[neck]))>=0.75,1,0))) AS FinalFullCalc
    FROM tblBCP;
    I left a bunch of calculations in there to help you see how I put the final calculation together for the circumference you use to look up values in your male standards table.

    CALL THIS QUERY qryCircum

    Second, create this query:

    Code:
    SELECT QryCircum.BCPID, QryCircum.HEIGHT, QryCircum.HIP, QryCircum.WAIST, QryCircum.NECK, QryCircum.Circum, QryCircum.CircumCalc, QryCircum.Remainder, QryCircum.Final, QryCircum.FinalFullCalc, tlbMale_Standards.Body_Fat_Percentage
    FROM QryCircum LEFT JOIN tlbMale_Standards ON (QryCircum.FinalFullCalc = tlbMale_Standards.Circumference_Value) AND (QryCircum.HEIGHT = tlbMale_Standards.HEIGHT);
    You can call this query whatever you want.

    you can see that person 1 and 2 now have a looked up BMI value where person 6 does not because there was no matching data in your male standards table.

  7. #7
    scoobz1234 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    41
    Thank you for all your help, i am having an issue with the second Query where it says Type Mismatch in Expression.

    also, you shouldnt of had to change any sample data, that data is from real people who i just changed the pertinant information on, for males you only use neck and waist, so waist-neck=circumference and for females you do waist+hip-neck=circumference.

    for instance MALE person 1 who is 69" tall NECK= 15 WAIST= 32 Circumference = 32-15= 17 then you cross with the table 69" with Circ of 17 then BF%= 14%

    for FEMALE person 2 who is 69" tall NECK= 15 WAIST= 28 HIP=37 circumference = 37+28-15= 50 then you cross with the FEMALE table at 69" with circ of 50 then BF%=19% now unfortunately i dont have the female numbers in there yet because its tasking and takes forever to do.

    also we dont use decimal's for the waist hip and neck, if your 15.5 then your 16 if your 15.4 then your 15. ect. we have to round it up or down.

    Also, whats the reason/difference for the fields CircumCalc and Final and Remainder and FinalFullCalc?

  8. #8
    scoobz1234 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Mar 2012
    Posts
    41
    I got what i needed, thank you for all your help! my issue was that i had to change what the data was stored as IE NUMBER or TEXT. then i just remade the queries, 3 of them and got exactly what i needed, thank you!

  9. #9
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,441
    Sorry I forgot to mention I'd change data types to number (double)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Unbound textbox in bound form
    By Evilferret in forum Forms
    Replies: 5
    Last Post: 08-15-2012, 01:26 PM
  2. Replies: 4
    Last Post: 07-07-2012, 07:14 PM
  3. Replies: 1
    Last Post: 12-08-2011, 08:03 AM
  4. Bound textbox causes problems
    By LAazsx in forum Forms
    Replies: 8
    Last Post: 12-09-2010, 09:25 AM
  5. Report will not print a bound textbox
    By vvrt in forum Reports
    Replies: 0
    Last Post: 03-16-2006, 02:16 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums