strip special characters from a subform field during copy and pasting into the subform.

In the BeforeUpdate event of the FORM, you will need to examine the field in question character-by-character and replace any offending characters with null to remove them.  You can use the Replace() function but that requires you to know what you are looking for.  It is probably easier to look for valid characters.

Keep in mind that if a bound field is defined as numeric or an unbound field has a numeric format, Access will only allow valid characters to be pasted so a number like 7..00 would create an error and not paste at all.

Pat,
Trying this but throwing an error:

Private Sub QTY_BeforeUpdate(Cancel As Integer)
Dim sSpecialChars As Variant
Dim Cd As String
Cd = Me.QTY
' strip special characters first NEW way
    sSpecialChars = "!@#$%^&*()_+={}|[]:;'<>?,.~`"
    For i = 1 To Len(sSpecialChars)
        Cd = Replace$(Cd, Mid$(sSpecialChars, i, 1), "")
    Next



Me.QTY = Cd

End Sub

Select all Open in new window

Trying this but throwing an error:

Private Sub QTY_BeforeUpdate(Cancel As Integer)

Select all Open in new window

You can't update a field while it updates.

Use the AfterUpdate event, though that won't help if you try to paste invalid amount expressions into my_money.

I think you need to rethink this concept.

I suggested the BeforeUpdate event of the FORM.  That is why I emphasized the word FORM.  Or, you can use the AfterUpdate event of the CONTROL as Gus suggested.  

I put most validation in the BeforeUpdate event of the FORM so I can have it all together.  Keep in mind that control level events don't fire unless the control gets the focus.  For this purpose, the AfterUpdate event of the control will work because you are checking for an error caused by some invalid entry.  If you were just validating, the value, the BeforeUpdate event of the form would be more appropriate.  You need to use the AfterUpdate event in this case because you want to automatically correct the value and as Gus mentioned, you cannot modify with code the control value while you are in the BeforeUpdate event.  Checking for things like null or a relationship to another control needs to be done in form level events because there might have been no entry in the control so checking for Null in the BeforeUpdate event of a form won't have the desired result.

ok, How about a function just looping through the subform in each field. and stripping the characters that way.

But your problem is, that the paste will fail at once if an attempt to paste 19..5 into a numeric field is done.

The only reliable solution, I can think of, would be to have some scratchpad form bound to a temp table where the pasting could be done into text fields accepting Null values.
On this form you could have a button than ran through all the records validating if they would fit the subform records. If not, focus the field(s) in error, otherwise copy the data to the subform.

Actually there is kind of a possibility to copy the text from Excel to clipboard...manipulate the data in clipboard and do the pasting in the subform....so its viable to place a button on the form that it will take care of the paste.
The workflow would be :
1 Copy from the Excel (Right click Copy or Ctrl+C)
2 Click the button on the form ...it will process the data on the clipboard and paste them
I know it works because i have implemented some thing like that some time ago.

Thanks as always...folks !!