By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,002 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 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
Share this Question
Share on Google+
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
  1. If Me.Total_Volume > Me.Total_Dose Then
  2.     Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume)*1000
  3. 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
  1. If Me.Total_Volume > Me.Total_Dose Then
  2.     Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume)*1000
  3. 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

JKing
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
  1. 'Global Variable
  2. Dim intMultiplier As Integer
  3.  
  4. Private Sub cboConcentration_BeforeUpdate(Cancel As Integer)
  5.     If cboConcentration = "mcg" Then
  6.         intMultiplier = 1000
  7.     Else
  8.         intMultiplier = 1
  9.     End If
  10. End Sub
  11.  
  12. Private Sub Total_Volume_Change()
  13.      Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier
  14. End Sub
  15.  
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.  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
  1. 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
  1. 'Global Variable
  2. Dim intMultiplier As Integer
  3.  
  4. Private Sub cboConcentration_BeforeUpdate(Cancel As Integer)
  5.     If cboConcentration = "mcg" Then
  6.         intMultiplier = 1000
  7.     Else
  8.         intMultiplier = 1
  9.     End If
  10. End Sub
  11.  
  12. Private Sub Total_Volume_Change()
  13.      Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume) * intMultiplier
  14. End Sub
  15.  
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

JKing
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
  1. Dim intMultiplier As Integer
  2.  
  3. Private Sub Form_Load()
  4. intMultiplier = 1
  5. End Sub
  6.  
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
  1. Dim intMultiplier As Integer
  2.  
  3. Private Sub Form_Load()
  4. intMultiplier = 1
  5. End Sub
  6.  
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
P: 97
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... :(
I would rather not. You will learn more by doing it yourself.

At the risk of upsetting JKing, remove all references to intMultiplier.
This should return your code to where we were before post #14.
Follow the instructions in post #14 to modify your Combobox, with the addition of setting DefaultValue to 1.

Then in each of your Events put
Expand|Select|Wrap|Line Numbers
  1. Me.FinalConcentration=Me.FinalDose * Me.FinalConcentrationUnits
where FinalConcentrationUnits is the name of your Combobox (but substitute your variable/field names)

In future, when you post code, use code tags. It is easier to read.
See http://www.thescripts.com/forum/announcement142.html
Jun 20 '07 #20

JKing
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;<some appropriate width for column2 showing the units>
Jun 20 '07 #24

P: 75
BoundColumn in Combobox properties =1
ColumnWidth=0;<some appropriate width for column2 showing the units>
hummm...i have this already, but where exactly do you put

Expand|Select|Wrap|Line Numbers
  1.  
  2.  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
  1. Private Sub Total_Dose_AfterUpdate()
  2. Me.Final_Dose = (Me.Total_Dose / Me.Total_Volume)
  3. Me.Final_Concentration = Me.Final_Dose * Me.Final_Concentration_Units
  4. End Sub
Jun 20 '07 #26

Post your reply

Sign in to post your reply or Sign up for a free account.