Avatar of Fordraiders
Fordraiders
Flag 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
qty
description
my_money

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.

Example:
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.

Thanks
fordraiders
Microsoft AccessVBA

Avatar of undefined
Last Comment
Fordraiders

8/22/2022 - Mon
PatHartman

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

ASKER
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

Open in new window

ASKER CERTIFIED SOLUTION
John Tsioumpris

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Gustav Brock

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PatHartman

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

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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
John Tsioumpris

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

ASKER
Thanks as always...folks !!