unbound lookup textbox limits # of digits

[selain]

I have a form with an unbound textbox. I want to be able to enter a sequence of digits and spaces (e.g., 02 950 4187); however, when I paste this into the box, I always have to delete the two spaces until the number is 9 digits long. Then it will accept the number and go look for the prize. How do I get the textbox to accept the number I paste in? Thanks so much! Suzanne
:banghead:

 

[apr pillai]

You may use the following VBA Program in the AfterUpdate() Event Procedure of your Unbound Textbox to scan and remove the space from the pasted data:

Private Sub [B]Text2[/B]_AfterUpdate()
Dim txt2 As String, num2 As String
Dim j As Integer, strK As String

txt2 = Nz(Me![[B]Text2[/B]], "")
If Len(txt2) = 0 Then Exit Sub

num2 = ""
For j = 1 To Len(txt2)
    strK = Mid(txt2, j, 1)
    If strK <> Space(1) Then
        num2 = num2 & strK
    End If
Next
If Len(num2) > 0 Then
  Me![[B]Text2[/B]] = num2
End If

End Sub

Replace the highlighted text ([Text2]) with your own Unbound Textbox Name in the Code.

 

[selain]

Thank-you, Apr Pillai! But, what if I don't know much about VBA? I've seen VBA but I don't dare touch it because I don't want to mess anything up! And, I think I saw the VBA that talks about this specific unbound lookup textbox. Do I just put it under this item?

 

[apr pillai]

1. Open your Form in Design View.
2. Click on the Unbound Textbox to select it.
3. Display it's Property Sheet (press F4).
4. Find the Name Property Value.
5. Try to change the existing Name property value to Text2. If you have another textbox on the form with the same name Text2 then Ms-Access will not allow to do this. In that case change it to myText2.
6. Find the After Update Event Procedure Property, on the Property Sheet.
7. Select [Event Procedure] from the Drop-down list.
8. Click on the build button (. . .) at the right end of the property to open VBA editing window.
   If the Name property value Text2 was accepted by Access then you will find Private Sub Text2_AfterUpdate() .... End Sub, the VBA procedure Start and End lines in the VBA Module.
9. Copy the VBA Code, I have given in my first post above, and Paste them overwriting the above two lines.
10. If myText2 is the name accepted by Access then change the name Text2 to myText2, wherever it is appearing in the Code.
11. Save the Form and try it out.

 

[selain]

Apr Pillai, I followed your directions and it does exactly what you said it was going to do--remove the two extra spaces. But now it doesn't look up the number! Now what?

 

[apr pillai]

If you are using the textbox contents as text type data in the lookup expressin (like =dlookup("fieldname","TableName","Telephone= '" & [Text2] & "'") it should work.

If it is in numerical search (if so, why your number starting with a zero (0) in your example shown in your first post) you should convert the text form number to numerical quantity in the search criteria like:

 =dlookup("fieldname","TableName","Telephone= " & Val([Text2]))