Display absolute value but store negative value

[Never Hide]

I have a subform displayed in Datasheet form and I would like to store in a field negative integers. But, for UI and usability purposes, I don't want the user to have to write the "-" for the negative value to be stored. is it possible that the user will write the number i.e. 1 and in the table -1 will be stored?

 

[llkhoutx]

You asked a vague question. Are all numbers always to be stored negative?

If so, display an unbound field to enter data and a bound field to actually store the data, e.g.

Me.BoubdFieldName = - me.UnboundFieldName

 

[burrina]

Perhaps Something Like:

=Abs(FieldName)

 

[gemma-the-husky]

it's slightly tricky - because it's unbound every row of your datasheet will show the same value. Can you not actually store the positive values?

you could just use the beforeupdate event for the control to only accept a positive number, and then use the after update to make it negative

 beforeupdate:
 if thiscontrol<0 then
     msgbox "please enter a positive value"
     cancel = true
     exit sub
 end if
  
 afterupdate
   thiscontrol = -thiscontrol

 

[CJ_London]

My understanding of what you require is the value is always negative and you want to save the poor users from entering a minus sign.

If this is correct then you could store the number as a positive but format it as a negative.

For example if you put

-0[Red]

in the control format property if a user types 44 then -44 will be displayed

Obviously all your calculations would need to take into account that it is actually a positive number

 

[Never Hide]

My understanding of what you require is the value is always negative and you want to save the poor users from entering a minus sign.

You are exactly right on that

If this is correct then you could store the number as a positive but format it as a negative.

What I'd like to do is exactly the opposite of this, store the number as negative but format it as positive. I know I can use custom formatting for that but I don't know if this will affect the data in the table since it's a bound datasheet form

 

[CJ_London]

OK try this:

Set the formatting to ;0[Red]

and put the following in your control got/lostfocus events

Private Sub Text0_GotFocus()

    Text0 = Abs(Text0)

End Sub

Private Sub Text0_LostFocus()

    Text0 = 0 - Abs(Text0)
    
End Sub

 

[Never Hide]

OK try this:

Set the formatting to ;0[Red]

and put the following in your control got/lostfocus events

Private Sub Text0_GotFocus()

    Text0 = Abs(Text0)

End Sub

Private Sub Text0_LostFocus()

    Text0 = 0 - Abs(Text0)
    
End Sub

That did the trick. Thanks a lot