Hello People,
I am a newbie here, and if I make some stupid mistakes please forgive me :)
I have found this site very helpful, and was hoping someone could solve a problem I have.
I have created a module in Access, Office 2003. The module is supposed to extract numeric strings from a field on a form and place entries in a sub form.
The code below is how I have tried to do this...
Option Compare Database
Public Function FindUrns_Click()
Dim dbs As Database
Set dbs = CurrentDb
Dim TPURN As String
Dim URN As String
Dim URNLen As Integer
Dim Lp As Integer
Forms!URNS!URNS.SetFocus
DoCmd.GoToRecord , , acFirst
URN = Forms!URNS!URNS
Lp = Len(URN)
'Start loop for record strings
Do While Lp > 1
Forms!URNS!URNS.SetFocus
URN = Forms!URNS!URNS
URNLen = Len(URN)
If IsNumeric(Left(Forms!URNS!URNS, 1)) Then
Do While IsNumeric(Left(Forms!URNS!URNS, 1))
URNLen = Len(URN)
TPURN = TPURN & (Left(Forms!URNS!URNS, 1))
Forms!URNS!StringsSubform!NumericString = TPURN
Forms!URNS!URNS = Right(Forms!URNS!URNS, URNLen - 1)
Loop
Forms!URNS!StringsSubform.SetFocus
DoCmd.GoToRecord , , acNext
Else
Forms!URNS!URNS = Right(Forms!URNS!URNS, URNLen - 1)
Forms!URNS!StringsSubform!NumericString = Forms!URNS!StringsSubform!NumericString & TPURN
End If
Lp = Len(URN)
Loop
'End Loop
End Function
The problem is when a numeric string is found the second number is repeatedly entered as the do while loop seems to get stuck, causing and error as the maximum field size gets reached as the module doesn't move through the string, I don't understand why this is.
Please help I cannot see where I am going wrong!!
Thanks in advance.