Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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:
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
Are you sure there is an issue ?
I just tried it and worked fine
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), "")
Next
StripUnwantedCharacters = InputValue
End Function
Trying this but throwing an error:
Private Sub QTY_BeforeUpdate(Cancel As Integer)
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.
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.
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial