Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-13-2013, 11:31 AM   #1
sian_hug
Newly Registered User
 
Join Date: Jun 2013
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
sian_hug is on a distinguished road
Setting form value as £0.00 on Lost focus - not working for currency data type

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

sian_hug is offline   Reply With Quote
Old 06-13-2013, 11:42 AM   #2
asteadman
Newly Registered User
 
Join Date: Jun 2013
Posts: 27
Thanks: 4
Thanked 1 Time in 1 Post
asteadman is on a distinguished road
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

Try putting the £sign in:

Private Sub Cash_LostFocus()
If Cash = "" Then Cash = "£0"
End Sub
asteadman is offline   Reply With Quote
Old 06-13-2013, 11:49 AM   #3
sian_hug
Newly Registered User
 
Join Date: Jun 2013
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
sian_hug is on a distinguished road
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

Quote:
Originally Posted by asteadman View Post
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.

sian_hug is offline   Reply With Quote
Old 06-13-2013, 12:03 PM   #4
asteadman
Newly Registered User
 
Join Date: Jun 2013
Posts: 27
Thanks: 4
Thanked 1 Time in 1 Post
asteadman is on a distinguished road
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

Very strange, works fine in unbound controls, see the test db attached.
Attached Files
File Type: accdb test.accdb (384.0 KB, 45 views)
asteadman is offline   Reply With Quote
Old 06-13-2013, 12:45 PM   #5
asteadman
Newly Registered User
 
Join Date: Jun 2013
Posts: 27
Thanks: 4
Thanked 1 Time in 1 Post
asteadman is on a distinguished road
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

Quote:
Originally Posted by sian_hug View Post
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. Attachment 48710
Ahh the reason is that "" is for a text control, it is not recognising that the currency field is = ""
asteadman is offline   Reply With Quote
Old 06-13-2013, 12:47 PM   #6
asteadman
Newly Registered User
 
Join Date: Jun 2013
Posts: 27
Thanks: 4
Thanked 1 Time in 1 Post
asteadman is on a distinguished road
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

The solution:
Private Sub Cash_LostFocus()
If IsNull(Me.Cash) Then Cash = "£0"
End Sub
asteadman is offline   Reply With Quote
The Following User Says Thank You to asteadman For This Useful Post:
sian_hug (06-13-2013)
Old 06-13-2013, 12:50 PM   #7
sian_hug
Newly Registered User
 
Join Date: Jun 2013
Posts: 3
Thanks: 1
Thanked 0 Times in 0 Posts
sian_hug is on a distinguished road
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

Thank you so much, thats perfect

sian_hug is offline   Reply With Quote
Old 06-13-2013, 12:53 PM   #8
asteadman
Newly Registered User
 
Join Date: Jun 2013
Posts: 27
Thanks: 4
Thanked 1 Time in 1 Post
asteadman is on a distinguished road
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

Quote:
Originally Posted by sian_hug View Post
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.
asteadman is offline   Reply With Quote
Old 06-14-2013, 03:41 PM   #9
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,808
Thanks: 13
Thanked 1,510 Times in 1,436 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Setting form value as £0.00 on Lost focus - not working for currency data type

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

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Subform Combo Box data disappears when lost focus Cameron.Turner Modules & VBA 3 11-27-2012 04:51 PM
Question localizing the currency data type saleemMSMS General 1 11-18-2009 10:59 PM
Dim Form on lost focus hudson426 Forms 1 07-06-2009 04:51 AM
Data Type: Format- Currency Dave32 Tables 6 10-14-2008 08:20 AM
[SOLVED] -ve values for currency data type venulogins Forms 0 05-23-2006 06:38 PM




All times are GMT -8. The time now is 02:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World