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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
FordraidersAuthor Commented:
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

John TsioumprisSoftware & Systems EngineerCommented:
Are you sure there is an issue ?
I just tried it and worked fine
Public Function StripUnwantedCharacters(InputValue As String)
Dim sSpecialChars As Variant
Dim i As Integer
' strip special characters first NEW way
    sSpecialChars = "!@#$%^&*()_+={}|[]:;'<>?,.~`"
    For i = 1 To Len(sSpecialChars)
        InputValue = Replace$(InputValue, Mid(sSpecialChars, i, 1), "")
StripUnwantedCharacters = InputValue
End Function

Open in new window

Check if you any issues with your references

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get a highly available system for cyber protection

The Acronis SDI Appliance is a new plug-n-play solution with pre-configured Acronis Software-Defined Infrastructure software that gives service providers and enterprises ready access to a fault-tolerant system, which combines universal storage and high-performance virtualization.

Gustav BrockCIOCommented:
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.
FordraidersAuthor Commented:
ok, How about a function just looping through the subform in each field. and stripping the characters that way.
Gustav BrockCIOCommented:
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.
John TsioumprisSoftware & Systems EngineerCommented:
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.
FordraidersAuthor Commented:
Thanks as always...folks !!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.