How can VBA change text box? 1

[Jackvmae]

I have a text box on my form called NewItem. I have two questions:

1) How can I use VBA to change this text box to a number box, currency box or back to a text box?

2) How can I use VBA to test of the contents of the NewItem box is text or number or currency?

Thanks,
Jack

 

[jyotko]

A textbox on a form is unaware of the type of data being entered therefore it won't know if you are entering text or numeric data (currency is just a variation on numeric data). You have to do data validation to ensure the data type is correct or you may be able to use the following instructions:

What you need to do is look under TOOLS - ADDITIONAL CONTROLS for the Microsoft Masked Edit Control (MSMASK32.OCX). You may or may not this control available depending on what software you have installed. If you have Visual Basic installed you should have this control. I don't know if it comes with the basic Office Installation. If you use this ActiveX Control you may have to distribute the *.OCX file with the application. It should be installed in the \%SYSTEMROOT%\SYSTEM32 directory (typically \windows\system32\).

 

[Edski]

Jack,
1. A text box is the same as a number box, which is also the same as a currency box. They're all the same type of control (a Text Box !). Other types of controls are List Box, Combo Box, Check Box, etc...

To test if the type of control in a form is a text box you can use

If ctlFld.ControlType = acTextBox Then
...
End If

If your NewItem is bound to a field in a table then you can't change the data type in vba. If it is unbound then you may be able to change the Format of the the field quite easily:

Me.NewItem.Format = "Currency"
Me.NewItem.Format = "General Number"
Me.NewItem.Format = ""

2. To test for the contents of a Text Box you can use the following functions:

IsNumeric - tests for numeric values
IsNull - tests for Null values
IsEmpty - tests for Emty values
IsDate - tests for Date values

To test whether the text box contains currency (IsNumeric = True) you can use InStr(1, txtField.Text, "$", vbTextCompare).

The only way I know how to test if a text box contains text is to say: "If it is not a Number, Date or Empty (Null) then it must be text!"

Does that help?

 

[gol4]

You can control the way the data appears

me.newitem.format = "currency" 'or "general Number" or "standard" or ony other type of format

if me.newitem.format = "currrency" 'or "general Number" or "standard"

you can look at the recordsets datatype property and it can be changed but it will change it for all records in that field
to show
dim rst as dao.recordset
set rst= currentdb.openrecordset("tablename&quot
debug.print fields(0).type
rst.close

one other thing to look at is you can use the type conversion functions
ccur() for currency
cstr() for string
clng() to a long number

 

[Jackvmae]

Great answers and I might be able to find my way around with them, but let me explain a bit more...

The NewItem text box is on an unbound form. The contents of the NewItem box will update to any of several different tables, depending upon choices the user made elsewhere in the form. Sometimes it will update to a text field. Other times it will update to a numeric field in a differnt table.

If the NewItem data will update to a field in a table that must be a number instead of text, then before allowing the update I have to make sure that what the user entered is a number.

So, is this correct syntax?:

'I've already checked for Null and ZeroLength.
'Now check if numbers only were entered.

If Not IsNumeric(Me.NewItem) Then
MsgBox "You must enter a number."
Me.NewItem.SetFocus
Exit Sub
End If

Jack

 

[jyotko]

That code should work to check wether the data is numeric or not. Keep in mind that if you are dealing with currency also, you usually won't have fractions of a penny so you may want to check for that also.