Setting form value as £0.00 on Lost focus - not working for currency data type (1 Viewer)

sian_hug

New member
Local time
Today, 00:07
Joined
Jun 13, 2013
Messages
3
I'm hoping someone can help with this because I have been going around in circles.

I have a bound form (frmRecordEdit) which looks up information on one form based on user entry (frmRecord) and then returns the relevant fields from the table (the fields are called lunches and cash).

I have written some code (very new to this) which clears the field on focus but then if it is blank it puts a "-" back into the field.

Private Sub Meals_GotFocus()
Meals = ""
End Sub

Private Sub Meals_LostFocus()
If Meals = "" Then Meals = "-"
End Sub

This works great as the Meals field is a text field but it wont work on the cash field because the data type is currency. If I change the data type to text it works fine and displays 0 but i really wanted the field to be formatted as currency.

The code for the cash field is:

Private Sub Cash_GotFocus()
Cash = ""
End Sub

Private Sub Cash_LostFocus()
If Cash = "" Then Cash = "0"
End Sub

Any ideas please :)
 

asteadman

Registered User.
Local time
Today, 00:07
Joined
Jun 13, 2013
Messages
27
Try putting the £sign in:

Private Sub Cash_LostFocus()
If Cash = "" Then Cash = "£0"
End Sub
 

sian_hug

New member
Local time
Today, 00:07
Joined
Jun 13, 2013
Messages
3
Try putting the £sign in:

Private Sub Cash_LostFocus()
If Cash = "" Then Cash = "£0"
End Sub

Hi,

I tried that earlier and it doesn't work, the field just stays blank.

As I said, if i change the data type to text in the table and click out of the field I can get it to display a 0 using the above code but I can't get the field to display anything at all if I leave the data type as currency.
 

asteadman

Registered User.
Local time
Today, 00:07
Joined
Jun 13, 2013
Messages
27
Very strange, works fine in unbound controls, see the test db attached.
 

Attachments

  • test.accdb
    384 KB · Views: 72

asteadman

Registered User.
Local time
Today, 00:07
Joined
Jun 13, 2013
Messages
27
That is exactly what I want but it wont work all the time I have the data type set as currency.

I have attached the database I am working on, and the lunches field works but the cash one just wont work. View attachment 48710

Ahh the reason is that "" is for a text control, it is not recognising that the currency field is = ""
 

asteadman

Registered User.
Local time
Today, 00:07
Joined
Jun 13, 2013
Messages
27
The solution:
Private Sub Cash_LostFocus()
If IsNull(Me.Cash) Then Cash = "£0"
End Sub
 

asteadman

Registered User.
Local time
Today, 00:07
Joined
Jun 13, 2013
Messages
27
Thank you so much, thats perfect

No worries, I know what it feels like to be stuck on something so simple, it gets so frustrating. Can't believe it took me so long to work that one out.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:07
Joined
Feb 19, 2002
Messages
42,981
"" = a Zero Length String. Null = Null.
A numeric field can NEVER be a ZLS because numeric fields are not strings!

Unless you have some specific reason for using a ZLS, it is best to use Null to clear controls.

Me.MyControl = Null

Your processing is strange to me. Why do you have to clear a field when the user tabs or clicks into it? Won't he be annoyed if he goes back to a previous field and your code clears the value he just typed?

And finally, if you want a field to default to 0, set it at the table level and you won't need any code.
 

Users who are viewing this thread

Top Bottom