View original (French)

VBA FIND special characters, not REPLACE

Solved
Ludivine50 Posted messages 118 Status Member -  
Ludivine50 Posted messages 118 Status Member -
Hello everyone,

After a long search on the forums, I found 30 discussions about the Replace function, functions to replace ' with '' to make the search work that doesn't work, but not the answer to my very simple question.

I just want a code like this:

Dim RECHERCHE as string
If RECHERCHE contains a space or a character " ' " Then
MsgBox("Do not enter a space or character ' in the search box!")
Else
Execute my search
End If

And my problem is simply that I can't check if what the person has entered contains a ' or a space. Unfortunately, this little inconvenience creates panic!

Hoping that someone can help me,
Have a nice day
Configuration: ACCESS 2000 - 2003

4 answers

Answer 1 / 4
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello,

try this event macro to place in the concerned "sheet" module of VBE
(in this example A1:C10 represents the area where the action is valid 

Private Sub Worksheet_Change(ByVal Target As Range) Dim reg As Object Dim flag As Boolean If Not Intersect(Target, Range("A1:C10")) Is Nothing Then ' instantiate Set reg = CreateObject("vbscript.regexp") reg.Pattern = "'" flag = reg.Test(target) If flag Then MsgBox "one or more apostrophes have been entered in the cell" End If End If End Sub


but I'm not sure if apostrophe is feminine

--
The forum is based on sharing knowledge: I do not respond to technical questions by private message.
Best regards, Michel
0
Ludivine50 Posted messages 118 Status Member 7
 
Hello Michel and thank you for your reply!

I work in Access and apparently Access VB does not recognize the Intersect function.
But I didn't know what to do with Target, maybe that's where I got it wrong?
0
Answer 2 / 4
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Well yes, it was on Excel, my thing
target represents the active cell
But regular expressions also work with Access
since I don't work with Access often, I might tell you nonsense

maybe by creating a boolean function with your text box as a parameter, and it would return False or True
function machin(tonparam)
Dim reg As Object
Set reg = CreateObject("vbscript.regexp")
reg.Pattern = "'"
machin = reg.Test(tonparam)
end functon

--
The forum is based on knowledge sharing: I do not respond to technical questions by private message.
Best regards, Michel
0
Ludivine50 Posted messages 118 Status Member 7
 
Hello Michel, thank you very much for your response.

I just tested it and it works very well!
I just have one problem: when I open my search form and type something into the text box for the first time that we will search for later, it crashes.
To better describe the issue:
- someone searches and types CHURCH --> the search works
- someone does a second search and types THE CHURCH --> MsgBox("Please enter an expression without an apostrophe")
- someone does a third search and types TOWN HALL --> the search works
- closing the search form
- reopening the search form
- typing any character in the text box (without clicking the button that launches my search procedure) --> Error message: 
Syntax error (missing operator) in the expression '[num] like '*THE CHURCH*' or [address] like '*THE CHURCH*' ....


This only happens with the first input, and then it works.
I don't understand why it keeps "THE CHURCH" in memory when the last input was "TOWN HALL," and why it continues to block on that.
What do you think?
0
Answer 3 / 4
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Hello,
As I mentioned, I rarely work on Access..
Suggestions without guarantee:
Maybe clean up the area if there's an error
Abandon the "function" system and switch to "after update" procedure
--
The forum is based on knowledge sharing: I therefore do not respond to technical questions via private messages.
Best regards, Michel
0
Ludivine50 Posted messages 118 Status Member 7
 
I added a command line to clean the area, but it doesn't change anything.
I don't know the after update procedure. Is it complicated?
0
Answer 4 / 4
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
 
Re

In form creation mode -
- select your control where you test the apostrophe
- property - event
check "after Update"
on the "three dots" button, select "code generator"
here is the code: (replace "s_mel" (the name of my app that checked the syntax of an email) with the name of your control 

Private Sub s_mel_AfterUpdate() Dim reg As Object Dim flag As Boolean If [s_mel] > 0 Then Set reg = CreateObject("vbscript.regexp") reg.Pattern = "'" flag = reg.Test([s_mel]) If flag Then MsgBox "one or more apostrophes have been entered in the cell" End If End If Set reg = Nothing End Sub


well all this, it allowed me to get back into Access, hence the delay!

--
The forum is based on knowledge sharing: I do not answer technical questions by private message.
Best regards, Michel
0
Ludivine50 Posted messages 118 Status Member 7
 
Oops! I wanted to test tonight, but the tables of my database are linked to SQL SERVER, and right now I don't have access to the database...
So, I'll test on Monday.
I'll do it a bit differently, since I coded the entire search in VB. The form is made with Access, but after that, it's all VB. But I think I'll manage to sort it out.
Thank you, I wish you a good weekend, and see you on Monday! ;-)
0
Ludivine50 Posted messages 118 Status Member 7
 
Hello Michel, I just tested it but it didn't change anything: it still displays an error message the first time I open the search form. So, I tried to recreate it completely. It's a bit long but I think I have no other solution, the form seems to be locked. By the way, before it couldn't search if there was a space in the input, now it can!
Thank you for your help Michel.
Have a nice day.
0