Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

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

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

I have a subform named  my_data_subform

I have fields

When someone copy and pastes data from an excel sheet,
I need to delete

special characters in case they are bring unwanted characters into the fields.

QTY field  nothing but  numeric( with no decimals)
my_money  = numeric and decimals...but not      1..00 or   ..09  

description = any alpha/numeric...except special keyboard characters.

Avatar of PatHartman
Flag of United States of America image

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.
Avatar of Fordraiders


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), "")

Me.QTY = Cd

End Sub

Open in new window

Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Trying this but throwing an error:
Private Sub QTY_BeforeUpdate(Cancel As Integer)

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 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 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 !!