Full Version: User Sees Positive Number, Value Saves As +/-
UtterAccess Forums > Microsoft® Access > Access Forms
Hi, I'm creating a pretty simple datasheet form with bound fields.

The form will help the user record purchase/sales transactions. Transactions can be both "in" (I'm buying) or "out" (I'm selling). One of my fields is called ProductQuantity. If I'm buying, this is recorded as a positive number, if I'm selling, it's recorded as a negative number.

The form can only be used for either buying or selling at one time but not both. The user will indicate what they're going to be doing by switching a field called InOut_ID. ID 1 = buy. ID 2 = sell.

Hopefully this is all clear enough.

My challenge: I don't want the user to have to enter -1 if they're recording a "sale" of one product. I'd like the user to be able to enter 1 and have Access fix the field value to -1 behind the scenes (because Access knows the value of InOut_ID that's associated with this transaction).

Please... if you can offer some guidance to send me in the right direction, it would be most helpful smile.gif

Hi Kenton. If the value of InOut determines the sign for the number, then you don't even need to worry about it. Whenever you need the sign to matter, just use an expression. But during data entry, the user can just keep using positive numbers. This is sort of like looking at your bank statement. All the numbers are positive, just printed on a different column depending on whether it's a deposit or a withdrawal. Just my 2 cents...
tina t
in the AfterUpdate event procedure of control ProductQuantity, try something like

If Me!InOut_ID = 2 Then
Me!ProductQuantity = Me!ProductQuantity - (Me!ProductQuantity * 2)
End If

Thanks theDBGuy and Tina, I appreciate the ideas! Both are good and either could work.

I think I'm leaning to theDBGuy's idea, since the user will never even see negative numbers.

I will need to figure out the expression to do what I need... Had planned on simply summing ProductQuantity numbers to find out when inventory of a particular product was at 0... But it's likely not too difficult.

Thanks again,

Me!ProductQuantity = Me!ProductQuantity - (Me!ProductQuantity * 2)

I've never seen this done like that before. Is there a reason other than personal preference why you didn't suggest

Me!ProductQuantity = Me!ProductQuantity * (-1) ?

You know your users better than I do (obviously), but as a user I would prefer to see all positive number if they were in different columns, but if in the same column (field), then I would find it very hard having to look at two columns to see what's happening.

In this case, where all the records on display are either buys or sells, how about changing the colour of the quantity: - Say black for buys and red for sells: (I'm not 100% sure you can do that in datasheet view)
Yes, you can format the numbers with different colors in datasheet view. Sent from phone...
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.