Multiplication Expression

[Ceejay64]

OK, I should be able to figure this out, but I'm getting frustrated... I also haven't been able to find the answer via the search feature or MS help, so I'd be overly appreciative if someone could slap some sense in me with an answer to this.

All I am trying to do is write an IF expression in "Text9" that resides in a subform (that feeds a table) that will multiply the value typed in the "MatQuan" text box by a specified amount. (Example below)


Private Sub LocQuan_AfterUpdate()

If Forms![frmEntry]![PartNumber] = "1A4595" And Me.Location = "Vane ID" Then
Me.Text9 = ((LocQuan.Value) * (0.021))
End Sub

I know it's recognizing the specified part number in the master form because I tested it using different instructions. When I type the line that is supposed to do the multiplication as I think it actually should be, I get an error. As seen above, it does nothing at all. I've tried many different variations with either a debug error or lack of results.

Could someone please show me how to properly write this darned expression?
Thanks in advance!
Yours in stupidity,
Ceejay (currently wearing a dunce cap)

 

[Ron_dK]

Private Sub LocQuan_AfterUpdate()

If Forms![frmEntry]![PartNumber] = "1A4595" And Me.Location = "Vane ID" Then
Me.Text9 = ((LocQuan.Value) * (0.021))
End Sub

I'm not sure Ceejay but would this do :

Me.Text9 = ([LocQuan] * (0.021))

assuming that locQuan is a numeric field.

 

[Ceejay64]

Hey Rak,
I gave it a try, but still no dice. It didn't kick off the debugger, but once again, I got no change to the text9 value (stayed at 0).
I do appreciate the suggestion very much, though. Not sure why this is such a stinker. I thought it would be fairly simple, but it's sure kicking my butt.
Thanks.

 

[gemma-the-husky]

just setting the value in some cases may not cause the screen update to refresh. look at help for repaint

popup a msgbox first to make sure the calc was done correctly then look at me.repaint to try, or text9.requery etc, to force the screen to repaint.

 

[Ceejay64]

OK...I'm halfway there...
by unbinding the textbox from the query, the formula works fine as posted by Rak. Now, I just need to pass the value to the table.
Should I pass it to another textbox that is bound to the material quantity field of my table? Whaddya think is the best method?
Thanks a lot for all your assistance!
~C

 

[gemma-the-husky]

I repeast my answer - there probably is nothing wrong with your code at all - its just that not everything causes the screen to repaint.

Look at repaint in Access help - it mentions a few ways of forcing screen updates.

 

[Ron_dK]

OK...I'm halfway there...
by unbinding the textbox from the query, the formula works fine as posted by Rak. Now, I just need to pass the value to the table.

Why would you want to pass it to a table. In fact you should not store a calculated value in a table, but leave it simply to display it in one of your forms.

Hth

 

[Ceejay64]

Oh, man. Am I a goof...
Yes, everything was actually working correctly, I just made a stupid error. I had it set to Long Integer and what I needed was actually Double. It was rounding, and the amounts were actually rounding to zero.
It was working fine most all of the time.

I feel like a total moron.

Thanks for all your help! Now I can go be properly embarassed.
~C