Integers in a textbox

[cid]

So basically in this database there are random numbers that are some times 4 characters long and sometimes 5 characters. I tried to make a replace function like this:

Private Sub Form_Load()
On Error GoTo move_first
Do
If GIS.Value = "xxxx" Then
GIS.Value = "R00000" & GIS.Value
ElseIf GIS.Value = "xxxxx" Then
GIS.Value = "R0000" & GIS.Value
Me.Recordset.MoveNext
End If
Loop
Exit Sub
move_first:
Me.Recordset.MoveFirst
End Sub

Anyways, this just hangs the program and forces me to do a ctrl + alt + break

if anyone has any ideas, they are greatly appreciated

[space_monkey]

Would this work for you?

GIS.Value = Format(GIS.Value, "R000000000")

[Hack]

What kind of control is GIS?

[cid]

textbox
And I dont know about that format command, because if the value in gis ='s 4 digits then there has to be "R + 5 zeros" is the gis ='s 5 digits then it has to be "R + 4 zeros"

[Hack]

textbox

If GIS is a textbox, then your first problem is that textbox's don't have a Value property. They have Text property. GIS.Value itself should be throwing an error.

[cid]

this is VBA(Sorry i should have clarified that)

And I believe in vba a textbox has the value property. Correct me if im wrong though.

[Hack]

this is VBA(Sorry i should have clarified that)

And I believe in vba a textbox has the value property. Correct me if im wrong though.

I believe you are correct. Having said that, however, if your post remained in ClassicVB, you would be getting other possible comments or solutions to your problem, with VB6 code, that wouldn't work (like mine) because you are using VBA.

Therefore, I've moved this to Office Development.

[cid]

ok, thanks... any other information on my problem would be helpful.
Thanks guys.

[dglienna]

Try a formula like this:

Option Explicit
 
Private Sub Form_Load()
  Dim a As String, s As String
  a = "124"
  s = "R" & String$(10 - Len(a), "0") & a
  MsgBox s & "  " & Len(s)
End Sub

You might want to adjust the number 10 to suite your needs. This uses 11 digits each time.

EDIT: Just noticed the move. Hope VBA has the String$ command. I believe that it does, though.

[cid]

im a little new but from looking at that code i gather...

A is a set variable that is equal to 124, and s is a string that inserts R and 11 zeros infront of the variable a?

I need to be able to insert "R0000" infront of a 5 digit number(random) and insert "R00000" infront of a 4 digit number. So I am not sure if your code will work with my needs.

[DKenny]

you could add a function that will convert your input value to a string of 11 characters. Then in the _change event of the textbox call that function.

Function Code

Function Cid(Value As String) As String
 
Const Prefix As String = "R0000000000"
 
Cid = Left(Prefix, 11 - Len(Value)) & Value
 
End Function

_Change event code

Private Sub TextBox1_Change()
    TextBox1.Value = Cid(TextBox1.Value)
End Sub

[EricDV]

GIS.Value=Format$(GIS.Value,"R000000000")