Right Trim Search Box

[vba_novice]

I have a search box that I would like to paste into. I need it to remove trailing spaces after I paste before I hit the Enter key. To get around this, currently I am pasting the data, hit the Del key and then hit the Enter key. I am using the Macro:



SearchForRecord
Object Type
Object Name
Record First
Where Condition = ="[ID] = " & Str(Nz([Screen].[ActiveControl],0))


I have tried to use a RTrim but get an invalid "Where" error. Any suggestions? Thanks in advance.

[ranman256]

why cant you put the fix code in then afterUpdate event before the normal code

'paste data then press enter

sub txtBox_afterupdate()
  FixData
  RunNormalEnterCode
end sub

[vba_novice]

why cant you put the fix code in then afterUpdate event before the normal code

'paste data then press enter

sub txtBox_afterupdate()
  FixData
  RunNormalEnterCode
end sub

Ok, having been trying to make your suggesting work. However after researching, my initial problem is not adding a space at the end. It is actually performing a NewLine or Carriage Return after pasting from an emulator. I need to eliminate the NewLine or Carriage Return after the pasting before hitting Enter to perform the Search. Sorry for taking you do the wrong trail.

[Micron]

It is actually performing a NewLine or Carriage Return after pasting from an emulator.

I take it you mean both? A carriage return leaves you on the same line at the beginning of that line. A new line is just that.
Maybe try the Replace function (you would have to nest it):

Replace(Replace("somestring",chr(10),""),chr(13),"")

[vba_novice]

I take it you mean both? A carriage return leaves you on the same line at the beginning of that line. A new line is just that.
Maybe try the Replace function (you would have to nest it):

Replace(Replace("somestring",chr(10),""),chr(13),"")

I tried this but maybe I am not performing the necessary other steps. Just to reset here's my issue:

I have a combo box for searching. I copy data from an emulator program and paste into that search box. Currently, I have to hit the delete key before hitting Enter and it does it search. I can see the cursor drops down to the next line. Below are what I have in place for that Combo box. An embedded macro for the search and a macro to clear that field.

After Update: Embedded macro
SearchForRecord
Object Type
Object Name
Record First
Where Condition = ="[ID] = " & Str(Nz([Screen].[ActiveControl],0))


On Enter: Macro
SetValue
Item = [Combobox]
Expression = Null

I have tried various Replace and Right codes and have not been able to get it to work. Any help of what and where to put the code would be grateful. I need it in is simplest form or in others words, dumb it down. Thanks!

[Micron]

Currently, I have to hit the delete key before hitting Enter and it does it search.

I don't understand the need for or the implication of that. If you're pasting data, why are you hitting Enter? Maybe you are the one who is inserting the line wrap? You're pasting into where, not the combo surely?
As for macros, I don't use them - embedded or otherwise - save for AutoExec sometimes. I see that you are using Screen.ActiveControl - are you sure the active control is then one you think it is when you've hit Enter? You could be moving to the next record or control. It all depends on the setting for this.

When you try something that doesn't work, post what you tried. Saying it didn't work helps out zero. In your latest post, I see no evidence that you used the Replace function at all, and if you did and that's what you're saying didn't work, posting your attempt(s) might have provided clues, whereas we're left with asking more questions.

FWIW, my suggestion will remove a line wrap (carriage return and line feed) from a string if it has them.

[vba_novice]

I don't understand the need for or the implication of that. If you're pasting data, why are you hitting Enter? Maybe you are the one who is inserting the line wrap? You're pasting into where, not the combo surely?
As for macros, I don't use them - embedded or otherwise - save for AutoExec sometimes. I see that you are using Screen.ActiveControl - are you sure the active control is then one you think it is when you've hit Enter? You could be moving to the next record or control. It all depends on the setting for this.

When you try something that doesn't work, post what you tried. Saying it didn't work helps out zero. In your latest post, I see no evidence that you used the Replace function at all, and if you did and that's what you're saying didn't work, posting your attempt(s) might have provided clues, whereas we're left with asking more questions.

FWIW, my suggestion will remove a line wrap (carriage return and line feed) from a string if it has them.

I am pasting the copied data into the combo box which is performing the search. After the paste, I would like to hit Enter to do the search or some other trigger to perform the search. Currently, after pasting the cursor go to a new line. Therefore, I have to hit the delete key before hitting Enter and then it performs the search. If I don't hit the delete key first it gives me an item not in list message even though it is there.

[Micron]

Then whatever you're pasting must have hidden characters like you said in post 3, but you just need to remove them at the right point. I suspect that's BeforeUpdate, not after. Did you try the replace function in your Where condition? You say you tried it, then posted an expression that doesn't show that:
Where Condition = ="[ID] = " & Str(Nz([Screen].[ActiveControl],0))

You could also post a compacted and zipped copy of your db here so that we might eliminate a ton of questions.