Question about Access

[pukhton]

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

[pukhton]

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???

[kepston]

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?

[pukhton]

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

[kepston]

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.

[pukhton]

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.

[pukhton]

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.

[pukhton]

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........

[kepston]

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

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.

[pukhton]

OK

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

[pukhton]

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....

[pukhton]

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...

[JKing]

You could create a global variable to act as a multiplier and add that to each line where you calculate final dose.

'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.

[kepston]

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]

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

 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:

final_concentration=final_dose*final_concentration_units 

I have guessed at your Control names, but you should get the idea.
Hope that helps.

[pukhton]

You could create a global variable to act as a multiplier and add that to each line where you calculate final dose.

'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

[JKing]

The declaration of the variable must be outside of the sub. The initialization is what must be in the load event.

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.

[pukhton]

The declaration of the variable must be outside of the sub. The initialization is what must be in the load event.

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..

[kepston]

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.

[pukhton]

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... :(

[kepston]

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

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

[JKing]

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.

[pukhton]

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?

[kepston]

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!

[kepston]

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>

[pukhton]

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

 
 final_concentration=final_dose*final_concentration_units

is it under the before updates event for combo box?

[kepston]

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

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