Delete tabs and spaces in combo box after pasting text

[zdjbel]

Hello,

I use a combo box on a form to take the user to the desired record. Often they paste text onto the combo box to search for the particular record but they also sometimes past blank spaces and/or tabs. I want to clean the pasted text but the code erases the pasted text and the search doesn't function. The search works normally when I remove the replace and trim limes of code. What am I doing wrong here?

Private Sub cmbGoToPr_AfterUpdate()
On Error GoTo cmbGoToPr_AfterUpdate_Err


'Remove tabs and spaces
cmbGoToPr = Replace(cmbGoToPr, vbTab, " ")
cmbGoToPr = Trim(cmbGoToPr)

'Now search for record
DoCmd.SearchForRecord , "", acFirst, "[PrNumber] = " & "'" & Screen.ActiveControl & "'"


cmbGoToPr_AfterUpdate_Exit:
Exit Sub




cmbGoToPr_AfterUpdate_Err:
MsgBox Error$
Resume cmbGoToPr_AfterUpdate_Exit


End Sub


Many thanks!

[orange]

It looks like your "'" & Screen.ActiveControl & "'" is being treated as a string literal. I suggest removing all the quotes.

Try the following to actually use the ActiveControl

 DoCmd.SearchForRecord , "", acFirst, "[PrNumber] = " &   Screen.ActiveControl

[zdjbel]

Thank you for your response but, no, that didn't solve the issue. What I'm looking to do is e.g. paste "tab space space PR1234567", remove tab space space and then search for PR1234567.

[Welshgasman]

Use the Replace function then.

[CJ_London]

Suggest use a string variable , assign active control to it then do your trim/replace and filler on that variable

also use the before update event and reference the .text property since if the pasted text does not meet the rules of the combo it will fail

[orange]

zdjbel,

I mocked up a form with some of my test data. Table Articles, and a sample form. Txt13 is the textbox for the search active control. I have attached zip with a gif showing the table, the form and copying and pasting a value into search text box. Your syntax works in my mockup. I tried removing the quotes as I suggested earlier and it did not work-- but your syntax does.

Good luck.

Update: I deleted the original message since it didn't attach the zip file. Trying again.


Also, I see CJ has responded he has lots of experience and is an active developer.

[Micron]

If you want to remove both tabs and spaces, why would you replace a tab with a space? Consider posting text examples of what is being pasted. Use code tags (hash button on posting toolbar - #) for more than a couple of lines of code to maintain indentation. Also, I believe you would have to use code tags to maintain 2 or more spaces, otherwise the forum will remove extra spaces between words. Not sure but I think code tags will prevent that but will test right now

three   spaces

EDIT - Yup. Works.

[jojowhite]

maybe when you copy, you copy some Non-printable characters.
you delete the non-printable characters and leave just the printable ones:

Private Sub cmbGoToPr_AfterUpdate()
On Error GoTo cmbGoToPr_AfterUpdate_Err




'Remove tabs and spaces
cmbGoToPr = RemoveNonPrintable(cmbGoToPr)
cmbGoToPr = Trim$(cmbGoToPr)




'Now search for record
DoCmd.SearchForRecord , "", acFirst, "[PrNumber] = " & "'" & Screen.ActiveControl & "'"




cmbGoToPr_AfterUpdate_Exit:
Exit Sub




cmbGoToPr_AfterUpdate_Err:
MsgBox Error$
Resume cmbGoToPr_AfterUpdate_Exit


End Sub


Function RemoveNonPrintable(strInput As String) As String
    Dim regEx As Object
    Set regEx = CreateObject("VBScript.RegExp")
    
    With regEx
        .Global = True
        .IgnoreCase = True
        ' Match all characters except printable ASCII (32–126)
        .Pattern = "[^\x20-\x7E]"
    End With
    
    RemoveNonPrintable = regEx.Replace(strInput, "")
End Function