PDA

View Full Version : Userfrom, field formats



phendrena
12-16-2008, 08:06 AM
Hi,

Is it possible to format a field in a userform, for example to currency?
If so, how?

Thanks,

CreganTur
12-16-2008, 08:15 AM
As you've probably noticed there is no property for a textbox to change the formatting. That said, you can do your formatting via VBA. You can use the FormatCurrency function (see Help) to format your variable's value so it displays correctly in the UserForm textbox.

Bob Phillips
12-16-2008, 08:29 AM
Or just Format which gives you access to any number of formats,

phendrena
12-16-2008, 08:36 AM
As you've probably noticed there is no property for a textbox to change the formatting. That said, you can do your formatting via VBA. You can use the FormatCurrency function (see Help) to format your variable's value so it displays correctly in the UserForm textbox.Hi Randy,

To be honest, i'm not too bothered about it looking right in the textbox, although it would be nice. I'm more concerned about it correctly adding the data into Access....

I've tried this :-

'Recordset("Original_Premium") = txtOPrem.Value = Format("Currency")'

The values show on the Access table as ?0.00 (Access table field is defined as currency).

Help!

Thanks,

Bob Phillips
12-16-2008, 08:41 AM
If you format it correctly in the textbox, and use txtOPrem.Text, as a string it will be correct as passed to Access.

phendrena
12-16-2008, 08:45 AM
If you format it correctly in the textbox, and use txtOPrem.Text, as a string it will be correct as passed to Access.Sorry, i'm having a brain blank moment, can you elaborate a little more please.

Cheers,

CreganTur
12-16-2008, 08:47 AM
I'd use the FormatCurrency function and explicitly declare the number of decimal places:

Recordset("Original_Premium") = txtOPrem.Value = FormatCurrency("Currency",2)'

phendrena
12-16-2008, 09:07 AM
I'd use the FormatCurrency function and explicitly declare the number of decimal places:

Recordset("Original_Premium") = txtOPrem.Value = FormatCurrency("Currency",2)'Hmmm.... I'm getting a compile error with that one, Sub or Function not defined.

Excel '97 problem?

Bob Phillips
12-16-2008, 10:00 AM
Load the textbox and format



txtOPrem.Text = Format(123,"?#,##0.00")


then load the Recordest from that



Recordset("Original_Premium") = txtOPrem.Text

phendrena
12-16-2008, 10:53 AM
Load the textbox and format



txtOPrem.Text = Format(123,"?#,##0.00")

OK, I've added this into the initalise routine, i now shows ?123.00 in txtOPrem. Now, if i enter into this field and overwrite the entered data it looses the format.... am i missing something?

Thanks,

CreganTur
12-16-2008, 12:01 PM
Now, if i enter into this field and overwrite the entered data it looses the format.... am i missing something?
The formatting belongs to the variable's value, not the textbox. The textbox has no formatting of its own. You can add in an AfterUpdate event for the textbox that will reformat data entered into it to FormatCurrency.

phendrena
12-31-2008, 05:34 AM
I'm still a little confused about the afterupdate event....

Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format(123, "?#,##0.00")
End Sub
OR
Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format("?#,##0.00")
End Sub


Both don't give the result i'm looking for.
In the first instance it enters ?#,##0.00 into the field
In the second I get ?123.00.

Any suggestions? (FormatCurrency doesn't appear to work on Excel '97)

Thanks,

Bob Phillips
12-31-2008, 06:00 AM
Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format(txtIPrem.Text, "?#,##0.00")
End Sub

phendrena
12-31-2008, 06:10 AM
Private Sub txtIPrem_AfterUpdate()
txtIPrem.Text = Format(txtIPrem.Text, "?#,##0.00")
End Sub


That works nicely thanks xld.

Once other quick question...

Formatting percentages.
If I use "##%" i get 1000% when the user enters 10 in the field.
How can i get it to show correctly as 10%?

Thanks,

phendrena
12-31-2008, 06:33 AM
Hi Again,

The currency fields now format correctly, however when i try and send the data into Access i'm still getting a 'Type Mismatch Error'.

The fields in Access are set as a Data Type of 'Currency'.
If I change them back to 'Text' I don't get an error.

Any suggestions?

Thanks,

Bob Phillips
12-31-2008, 06:33 AM
Just use 0%

Bob Phillips
12-31-2008, 06:46 AM
Hi Again,

The currency fields now format correctly, however when i try and send the data into Access i'm still getting a 'Type Mismatch Error'.

The fields in Access are set as a Data Type of 'Currency'.
If I change them back to 'Text' I don't get an error.

Any suggestions?

Thanks,

You are probably trying to load a string value, complete with ? and , symbols into a numeric field.

You could strip the garbage before loading it, like so


Function ValidAmount(ByVal amt As String) As Double
Dim RegEx As Object
Set RegEx = CreateObject("VBScript.RegExp")
RegEx.Pattern = "[?,]"
ValidAmount = RegEx.Replace(amt, "")
Set RegEx = Nothing
End Function