435,264 Members | 1,056 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,264 IT Pros & Developers. It's quick & easy.

# Question about Access

 P: 75 I have a questin about Access: I have Access form with a combination of text box and combo boxes. I want to calculate some thing in this form. I want to calculate Total_Dose / Total Volume and put the result in the Final_Dose (mind you these r all text boxes). The formula is working, thats not a problem. If i divide 100/50, i will get 2 and it will save in the MainTable. but now If i divide 50/100, i will get 0.5, i want to change this value (0.5) to 50., thats where i am having problem. Is there a way we can fix this? Below is the code for this, please take a look, and help me out please. Private Sub Total_Dose_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Dose_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Jun 13 '07 #1
25 Replies

 P: 75 I have a questin about Access: I have Access form with a combination of text box and combo boxes. I want to calculate some thing in this form. I want to calculate Total_Dose / Total Volume and put the result in the Final_Dose (mind you these r all text boxes). The formula is working, thats not a problem. If i divide 100/50, i will get 2 and it will save in the MainTable. but now If i divide 50/100, i will get 0.5, i want to change this value (0.5) to 50., thats where i am having problem. Is there a way we can fix this? Below is the code for this, please take a look, and help me out please. Private Sub Total_Dose_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Dose_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub please I need help...any body please??? Jun 13 '07 #2

 Expert P: 97 What rules do you have for converting from 0.5 to 50? Do you always want to multiply by 100 when your result is less than 1? Jun 13 '07 #3

 P: 75 What rules do you have for converting from 0.5 to 50? Do you always want to multiply by 100 when your result is less than 1? and how do you do that? is it IF ELSE statements? would u please help me out in this? thanks Jun 13 '07 #4

 Expert P: 97 and how do you do that? is it IF ELSE statements? would u please help me out in this? thanks Sorry, I missed your reply. What I was trying to find out, is what your logic is for the conversion. When do you want to do it? Always, sometimes, only if a value goes below a certain level? Are there any exceptions to that rule? What conversion do you need to perform? Will it be 0.5 to 500 only, or 0.1 to 100, or 0.0125 to 12.5 etc? Are there any units involved? grams, milligrams, litres, etc When we have the logic defined, we can help with the programming. Jun 19 '07 #5

 P: 75 Sorry, I missed your reply. What I was trying to find out, is what your logic is for the conversion. When do you want to do it? Always, sometimes, only if a value goes below a certain level? Are there any exceptions to that rule? What conversion do you need to perform? Will it be 0.5 to 500 only, or 0.1 to 100, or 0.0125 to 12.5 etc? Are there any units involved? grams, milligrams, litres, etc When we have the logic defined, we can help with the programming. Thanks for the response. I am changing Milligram(mg) to Microgram(mcg). for example if i divide 100/50 = 2 but for mcg. 50/100 = 0.5, but I want to change this to 500mcg. i thank you again for your help. Jun 19 '07 #6

 P: 75 Sorry, I missed your reply. What I was trying to find out, is what your logic is for the conversion. When do you want to do it? Always, sometimes, only if a value goes below a certain level? Are there any exceptions to that rule? What conversion do you need to perform? Will it be 0.5 to 500 only, or 0.1 to 100, or 0.0125 to 12.5 etc? Are there any units involved? grams, milligrams, litres, etc When we have the logic defined, we can help with the programming. whenever the value goes below a certain level, it could be 10/100 = 0.1( but ideally i want 10mcg), or 20/100 = 0.2(20mcg). so yes, sometimes. Not always. I hope I explained what you asked for. Jun 19 '07 #7

 P: 75 whenever the value goes below a certain level, it could be 10/100 = 0.1( but ideally i want 10mcg), or 20/100 = 0.2(20mcg). so yes, sometimes. Not always. I hope I explained what you asked for. sorry i am again..what i meant to say is I want 0.1 change to 100mcg or 0.2 change to 200mcg or anyother value. SORRY........ Jun 19 '07 #8

 Expert P: 97 whenever the value goes below a certain level, it could be 10/100 = 0.1( but ideally i want 10mcg), or 20/100 = 0.2(20mcg). so yes, sometimes. Not always. I hope I explained what you asked for. OK Expand|Select|Wrap|Line Numbers If Me.Total_Volume > Me.Total_Dose Then     Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume)*1000 End If This is the basis for your code, but there is no indication of units. The number values will be stored as just numbers. If later you want to compare 5mg with 500mcg the values stored will be 5 and 500. Therefore in this instance 500(mcg) is bigger than 5(mg). What you might want to consider doing is storing all values in mg, for example, and changing how the value is displayed on screen using If...Then statements in the Format property of your text box. If this is what you want, let me know and I will try to help. Jun 19 '07 #9

 P: 75 OK Expand|Select|Wrap|Line Numbers If Me.Total_Volume > Me.Total_Dose Then     Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume)*1000 End If This is the basis for your code, but there is no indication of units. The number values will be stored as just numbers. If later you want to compare 5mg with 500mcg the values stored will be 5 and 500. Therefore in this instance 500(mcg) is bigger than 5(mg). What you might want to consider doing is storing all values in mg, for example, and changing how the value is displayed on screen using If...Then statements in the Format property of your text box. If this is what you want, let me know and I will try to help. thanks...but where exactly shuould I put this under? Is it under this..thats what i had previousely.. Private Sub Total_Dose_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Dose_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Jun 19 '07 #10

 P: 75 thanks...but where exactly shuould I put this under? Is it under this..thats what i had previousely.. Private Sub Total_Dose_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Dose_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_Change() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub Private Sub Total_Volume_LostFocus() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) End Sub I GOT IT THANKS BUDDY.... Jun 19 '07 #11

 P: 75 I GOT IT THANKS BUDDY.... I am again...what you gave me its workign fine, but little problem... I have a combo box for Final concentration units (mg,mcg) can we do something like if the user picks mg it will multiply final dose by 1 and if user picks mcg it will multiply by 1000? and based on the entry It will change the Final concentration. Am i clear to you? if not..I can send you the file. my email address is **e-mail address removed as per site rules** Thanks... Jun 19 '07 #12

 Expert 100+ P: 1,206 You could create a global variable to act as a multiplier and add that to each line where you calculate final dose. Expand|Select|Wrap|Line Numbers 'Global Variable Dim intMultiplier As Integer   Private Sub cboConcentration_BeforeUpdate(Cancel As Integer)     If cboConcentration = "mcg" Then         intMultiplier = 1000     Else         intMultiplier = 1     End If End Sub   Private Sub Total_Volume_Change()      Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier End Sub   You will however need to initialize the variable before any calculations are performed to prevent any errors. I suggest initializing in the forms load event. Jun 19 '07 #13

 Expert P: 97 I am again...what you gave me its workign fine, but little problem... I have a combo box for Final concentration units (mg,mcg) can we do something like if the user picks mg it will multiply final dose by 1 and if user picks mcg it will multiply by 1000? and based on the entry It will change the Final concentration. Am i clear to you? if not..I can send you the file. my email address is zeeshanbabar@gmail.com Thanks... You can store a different value to the one shown in a combobox. Change the number of columns to 2 and bind to the 'hidden' column. For your existing Combobox on the Data tab, set the RowSourceType to ValueList (from what you described it probably is already). Set the RowSource to Expand|Select|Wrap|Line Numbers  1;"mg";1000;"mcg" This will give you two columns, the first is your multiplier, the second your units. Set the BoundColumn to 1 i.e. your multiplier. Set LimitToList = Yes On the Format tab set ColumnWidths to something like 0cm;2.54cm (0 hides the multiplier, the second can be any width you like) Make sure the ColumnCount shows 2 Then we can use the bound value in a calculation like so: Expand|Select|Wrap|Line Numbers final_concentration=final_dose*final_concentration_units  I have guessed at your Control names, but you should get the idea. Hope that helps. Jun 19 '07 #14

 P: 75 You could create a global variable to act as a multiplier and add that to each line where you calculate final dose. Expand|Select|Wrap|Line Numbers 'Global Variable Dim intMultiplier As Integer   Private Sub cboConcentration_BeforeUpdate(Cancel As Integer)     If cboConcentration = "mcg" Then         intMultiplier = 1000     Else         intMultiplier = 1     End If End Sub   Private Sub Total_Volume_Change()      Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier End Sub   You will however need to initialize the variable before any calculations are performed to prevent any errors. I suggest initializing in the forms load event. I follwed the code and did exaclty the same, but I am not getting the answer...I think I am doing some thing wrong. PLease take a look of mine code. For Form Load event Private Sub Form_Load() 'Global Variable Dim intMultiplier As Integer End Sub For Combobox BeforeUpdate Event Private Sub Combo71_BeforeUpdate(Cancel As Integer) If Combo71 = "mcg" Then intMultiplier = 1000 Else intMultiplier = 1 End If End Sub and for the calculations Private Sub Total_Dose_Change() 'Global Variable Dim intMultiplier As Integer Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier End Sub Private Sub Total_Dose_LostFocus() 'Global Variable Dim intMultiplier As Integer Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier End Sub Private Sub Total_Volume_Change() 'Global Variable Dim intMultiplier As Integer Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier End Sub Private Sub Total_Volume_LostFocus() 'Global Variable Dim intMultiplier As Integer Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier End Sub Jun 20 '07 #15

 Expert 100+ P: 1,206 The declaration of the variable must be outside of the sub. The initialization is what must be in the load event. Expand|Select|Wrap|Line Numbers Dim intMultiplier As Integer   Private Sub Form_Load() intMultiplier = 1 End Sub   If you don't initialize the variable and the user selects nothing from the combo box it will generate an error because of the null value. Jun 20 '07 #16

 P: 75 The declaration of the variable must be outside of the sub. The initialization is what must be in the load event. Expand|Select|Wrap|Line Numbers Dim intMultiplier As Integer   Private Sub Form_Load() intMultiplier = 1 End Sub   If you don't initialize the variable and the user selects nothing from the combo box it will generate an error because of the null value. I am really sorry, i am not getting anywhere with it... :( I tried putting out side, but it says only comments comes after END SUB...Not sure what is going on...do you mind if i send you the file and u take a look. please help me out..and you have been a great help already.. Jun 20 '07 #17

 Expert P: 97 Look at post #14 above. (JKing made a good point about initialising the variable, though) To my suggestion in post #14 you will need to set the DefaultValue on the Data tab to 1. Jun 20 '07 #18

 P: 75 Look at post #14 above. (JKing made a good point about initialising the variable, though) To my suggestion in post #14 you will need to set the DefaultValue on the Data tab to 1. Kepston u mind if u take a look of my file? i could send it to you. please I need your expertiese..i have tried everything... :( Jun 20 '07 #19

 Expert 100+ P: 1,206 Won't upset me at all. Both solutions end up with the same product though I personally think yours is the better method to go with. Less code. In either case you have to make sure that a null value isn't being multiplied in the formula. Jun 20 '07 #21

 P: 75 Won't upset me at all. Both solutions end up with the same product though I personally think yours is the better method to go with. Less code. In either case you have to make sure that a null value isn't being multiplied in the formula. Thanks both of you guys...i am sure i will get it right soon.. :) by the way when u say "Bind to the hidden column" what do you mean by that? Jun 20 '07 #22

 Expert P: 97 Won't upset me at all. Both solutions end up with the same product though I personally think yours is the better method to go with. Less code. In either case you have to make sure that a null value isn't being multiplied in the formula. Good point. With the combobox having a default value and two non-null limited options, we shouldn't have to worry about that. But the other fields could be null, I suppose. A default value, and a validation rule, would get around it, but then again, this thread started out with division - so we also have to consider dividing by zero! Let's get the thing basically working before making it robust! Jun 20 '07 #23

 Expert P: 97 Thanks both of you guys...i am sure i will get it right soon.. :) by the way when u say "Bind to the hidden column" what do you mean by that? BoundColumn in Combobox properties =1 ColumnWidth=0; Jun 20 '07 #24

 P: 75 BoundColumn in Combobox properties =1 ColumnWidth=0; hummm...i have this already, but where exactly do you put Expand|Select|Wrap|Line Numbers    final_concentration=final_dose*final_concentration_units is it under the before updates event for combo box? Jun 20 '07 #25

 Expert P: 97 You will need to put it in every event that can effect a change. So, after updating any of your fields [Total Dose], [Total Volume], [Final Concentration Units] etc In essence, you want to recalculate after something changes. For example Expand|Select|Wrap|Line Numbers Private Sub Total_Dose_AfterUpdate() Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) Me.Final_Concentration = Me.Final_Dose * Me.Final_Concentration_Units End Sub Jun 20 '07 #26