Solved

Restricting Certain Characters/spaces, etc on a control

Posted on 2015-01-13
22
147 Views
Last Modified: 2015-01-18
Hello,

I am trying to figure out how to use this in the afterupdate on a control on my form but can't figure out what I am missing here.



Dim strSearchFor As String
 
  strSearchFor = "<>?,./~!@#$%^&*()_`-{}[]:;'" & Chr$(34) & " "
 
 
      If strSearchFor > 0 Then
        MsgBox "Punctuation or Spaces are not allowed in the License Number"
        Beep
        LicenseNumber.SetFocus
        Cancel = True
        Exit Sub
             End If
0
Comment
Question by:Ernest Grogg
  • 9
  • 9
  • 4
22 Comments
 
LVL 57
ID: 40547078
Do a for/next loop on the text, one character at a time using Mid() to look at a single character, and InStr() to compare that to your punctuation string.

Jim.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40547109
AfterUpdate doesn't have a Cancel option.
BeforeUpdate does
strSearchFor = "<>?,./~!@#$%^&*()_`-{}[]:;'" & Chr$(34) & " "
You have equals in there and not like and I doubt a user ever typed in that :)
A Select Case True is much better

Here's mine for illegal (for my app) characters
Pass in the string, and it passes out True/False and msgbox's when true.
Adapt it as required
Public Function IllegalUsed(PossibleNewName As String) As Boolean
If Nz(PossibleNewName, "") = "" Then Exit Function
Select Case True
    Case PossibleNewName Like "*\*"
        IllegalUsed = True
    Case PossibleNewName Like "*/*"
        IllegalUsed = True
    Case PossibleNewName Like "*:*"
        IllegalUsed = True
    Case PossibleNewName Like "*" & Chr(13) & "*"
        IllegalUsed = True
    Case PossibleNewName Like "*" & Chr(34) & "*"
        IllegalUsed = True
    Case PossibleNewName Like "*>*"
        IllegalUsed = True
    Case PossibleNewName Like "*<*"
        IllegalUsed = True
    Case Else
        IllegalUsed = False
        
End Select
       
If IllegalUsed = True Then
    MsgBox "You have used an illegal character (\/:*?" & Chr(34) & "<>) in your new name." & vbCrLf & "This will cause the PICTURE routine to FAIL when you attach pictures to this client's jobs" & vbCrLf & "Edit the name to avoid the illegal keystroke.", vbCritical + vbOKOnly + vbMsgBoxSetForeground, "bad file name"
Else
    Exit Function
End If


End Function

Open in new window

0
 
LVL 57
ID: 40547125
That would probably be a more efficient approach, but it would depend on the length of the strings being checked and the number of illegal characters being searched for.....maybe a hybrid function?

Jim.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Ernest Grogg
ID: 40547135
Hello Jim,

I actually read this from a previous question from you and tried to modify it because I kept getting a runtime error

2108  You must save the field before you execute the GoToControl action....  (this is in the BeforeUpdate)

I tired originally in the BeforeUpdate event (where I think this should be) but changed to AfterUpdate and it bypasses the field after clicking OK and goes to the next field.

I need the user to correct the error before moving on...

Here was what I got from you and changed it to this:  I put this originally in the BeforeUpdate but got the Runtime error above and then moved to AfterUpdate but it moves to the next field after the msgbox shows...not setting the focus on the field to correct the error...



Dim strSearchFor As String
  Dim intK As Integer
 
  strSearchFor = "<>?,./~!@#$%^&*()_`-{}[]:;'" & Chr$(34) & " "
 
  For intK = 1 To Len(Me![LicenseNumber])
       If InStr(1, strSearchFor, Mid(Me![LicenseNumber], intK, 1)) > 0 Then
        MsgBox "Punctuation or Spaces are not allowed in the License Number"
        Beep
        Cancel = True
        intK = Len(Me![LicenseNumber])
        LicenseNumber.SetFocus
       
        Exit Sub
        End If
  Next intK
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40547138
@Jim
I like it for legibility.  I can see each one I am testing for much more easily
Straight up efficiency is definitely RegEx, but I've never needed to be efficient enough to have to code that.

@Earnest
I never have joy of BeforeUpdate either.
For my purposes -- and perhaps yours, too -- the AfterUpdate, throw focus back routine suffices.  Sometimes you need a LostFocus, too, because the AfterUpdate means the data is in, and unless change is made, it won't fire again.

Because I do main data entry through unbound controls and code, rather than bound controls.  I can definitely trap the user before they can commit junk data to a table in the code that writes the record.  So, I don't need or use validation.  You may wish to go that route.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 40547153
In the Before Update:


  Dim strSearchFor As String
  Dim intK As Integer
 
  strSearchFor = "<>?,./~!@#$%^&*()_`-{}[]:;'" & Chr$(34) & " "
 
  For intK = 1 To Len(Me![LicenseNumber].Text)
       If InStr(1, strSearchFor, Mid(Me![LicenseNumber].Text, intK, 1)) > 0 Then
        MsgBox "Punctuation or Spaces are not allowed in the License Number"
        Beep
        Cancel = True
        intK = Len(Me![LicenseNumber].Text)
      End If
  Next intK

 Jim.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40547166
Not disagreeing with @Jim
When you hit AfterUpdate, the control value has been updated with what is in the buffer, so no .Text needed there
.Text is only ever valid when a control has the focus.
It represents the value of the control that the user has entered/changed/left alone since the control GotFocus.
.Value represents what the value of the control was before it GotFocus and what it is AfterUpdate.

In UI terms, .Text is what will be put back in the control when you press ESC.
Knowing that it is only ever valid when the control has the focus is the key to avoiding .textaches
0
 
LVL 57
ID: 40547178
<<Not disagreeing with @Jim
When you hit AfterUpdate, the control value has been updated with what is in the buffer, so no .Text needed there>>

 You should <g>  I changed my comment after I posted (I'm listening to a MVA webcast - can't seem to really multi-task anymore<g>); I was off a event....I was thinking of OnChange vs BeforeUpdate/AfterUpdate

 .Text is only needed when your working in the OnChange event.

 So .text can be dropped in the code I posted.

Jim.
0
 

Author Comment

by:Ernest Grogg
ID: 40547194
Tried it...

That gives me this in the BeforeUpdate Event

"The Value Violates the validation rule for the field or record."  This is after the MsgBox.
0
 

Author Comment

by:Ernest Grogg
ID: 40547198
So...sorry just read the updates.

so should I change it to .Value?.
0
 
LVL 57
ID: 40547214
<<so should I change it to .Value?. >>

 You can, but it doesn't matter.   .Value is the default property for a control, so if you leave it off, the result is the same.

<<"The Value Violates the validation rule for the field or record."  This is after the MsgBox. >>

  And do you have a validation rule defined for the control?

Jim.
0
 

Author Comment

by:Ernest Grogg
ID: 40547225
no.  as of right now.  I only have validation rule in the BeforeUpdate of the Form, not the individual control.

In the Table, I have it as a required field but that is it.
0
 

Author Comment

by:Ernest Grogg
ID: 40547377
is there a way to clear that message without showing it?

but I do think it may be related to the table it is linked to on the query.
0
 
LVL 57
ID: 40547468
<<no.  as of right now.  I only have validation rule in the BeforeUpdate of the Form, not the individual control.

In the Table, I have it as a required field but that is it. >>

 That's odd ...you should not be getting the message.  It's certainly not coming from the routine you have.   In the routine, put a STOP right after Next intK.

 Execute.  You should get the STOP.   Press F5.   if you get the error message, then you have a rule on the control, on the field in the table, or on the table itself.

Access's built-in error message can be suppressed in the in the OnError event of the form.  The argument DataErr is the error number that occured, and Response you use to tell Access to display a message or not.

Setting it to acDataErrDisplay       will trigger the default Access behavior (the message) and using acDataErrContinue will tell Access to suppress the error display.

Jim.
0
 

Author Comment

by:Ernest Grogg
ID: 40547613
So I created another table and tried....something inside the table....don't know what though....

give me a day....
0
 

Author Comment

by:Ernest Grogg
ID: 40547922
I figured out what is was.

The field on the table is showing required.  I unchecked the field as required and all is well...

Since I am not real good with this.  Maybe there is something in this code of yours that is searching and then trying to skip over the field tables Required?

Sorry don't know the lingo?
0
 
LVL 57
ID: 40548648
It's not trying to skip over it, but it's not allowing you to continue onto the next control.  If that ends up leavin the license number blank, then if you try to save the record, you will get an error.

Maybe it would be helpful if you could post a small sample db, with the table involved, one or two records, and the form involved.

Jim.
0
 

Author Comment

by:Ernest Grogg
ID: 40548777
Here is what I got for ya.

There is 2 tables, 1 Form, 1 Query, 1 Module

Form is set to start in Data Entry, I also have 1 record in the tables.
Sample.accdb
0
 
LVL 57
ID: 40551546
OK what the problem is, is that the license field is a primary key, so when you start typing, and type anything, an insert is triggered and you run afoul of validation rules.

Your having these issues because your table design and form design are off.  What your tables should look like is this:

screen shot
 Not sure if "LicenseNumber" is two separate things of not.   Seems like not, so it should only be in one table.

 Now onto the form.  You should only be working with one table at a time.   For example, you should have a form for entering people, and one for entering badges.

Within the badge form, you might allow them to pick a person from the other table, even possibly add a new one right there (by popping up the person form), but you should not be trying to work with all the fields from both tables in that one form.

You could if you added a subform and have a parent/child setup (or a "one to many", which is what you have one person can have many badges).

So the main form would select and work with the person fields, and the subform would work with the badge fields.

Toss that around and bounce back with questions.

Jim.
0
 

Author Comment

by:Ernest Grogg
ID: 40555690
Sorry I have not responded...I have been down and out for a couple of days now (sick).

Well, I can honestly tell you, I started it with this in mind, and I read somewhere that it didn't matter what the Primary Key was as long as your foreign key could be used as the many.

Well, with that said, I can see your point and what is being created.  So, today I have been working diligently to solve this...well this...you know...mess of sorts.

Guess, I should have followed what I read here on site first, then went from there.

You know, many have ideas of how to properly set things up and everyone seems to be similar in some aspects but different in others.  It seems that one has to sort through to find the best working solution for each project, but with standardized systems solutions.

I do appreciate your looking at this for me and working hard with me and especially being patient.

Many thanks!
0
 
LVL 57
ID: 40556127
<<You know, many have ideas of how to properly set things up and everyone seems to be similar in some aspects but different in others.  It seems that one has to sort through to find the best working solution for each project, but with standardized systems solutions.>>

 Yes, very much so. That's because after you apply the fundamentals, the result depends on what your modeling, and that might be done in different ways.

<<Well, with that said, I can see your point and what is being created.  So, today I have been working diligently to solve this...well this...you know...mess of sorts.>>

 Start of slow and step by step.  First, gather all the facts that pertain to each "thing"; person, badge, or whatever and group them together.   That forms a table.

Then decide how those things are related.   In this case, we have a "people" table and a "badge" table.   One person can have many badges, so that's a "one to many" and means that you will need a copy of the primary key from the people table in the badge table.

Looks like you have "Users" as well, so that will be the same type of setup.  

Start then with the form for people...only work with that one table.   Make sure you can add, deleted, update etc.

Then work on the badge form.  What you want to use for the PersonID field is a combo control.   With it, you will be able to display data from the MGNameAddressPhone to the user (using rowsource, columncount, and columnwidths), but what will get stored in the MGSponserLocationBadge table will be the value of the primary key (BoundColumn).

 As part of the combo, you'd set its LimitToList no, and when a user typed in a new name, it would trigger the NotInList event.   In that event is where you would open the people form, allow them to fill in all the fields, save and return to the badge form, at which point you'd now have someone's name filled in.

 You'd also use a combo for the UserID as well.

 That's only one way to do that.

 The important thing to keep in mind is that for working with data entry, a single form is looking to work with one table at a time, which is what your specifying for it's recordsource.  That can be a query and doesn't need to always be a table, but you should only be trying to update one table

 A main / subform can work with two tables that are a parent / child type setup (one to many), but when you think about it, each form is still only dealing with one table at a time, they just appear together.

Hope that makes sense and makes things a little clearer.

Jim.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40556367
You know, many have ideas of how to properly set things up and everyone seems to be similar in some aspects but different in others.  It seems that one has to sort through to find the best working solution for each project, but with standardized systems solutions

Many questions ago, you were looking at how to Cancel a users input, if they changed their minds.  There are two ways of going about that: trying to use BeforeUpdate events to deep-six the user inputs -- which you ultimately decided to pursue, or providing the user with unbound controls and adding the data only via VBA code.  You are now discovering why I opt for the second approach:  Adding data to multiple tables, and keeping primary keys from being created, and creating primary keys in the correct order on a multi-table form can all be exceedingly tricky tasks to accomplish AND make the UI look the way you want.

Your app is going to last for a long time.  Do not be afraid to look at your efforts so far and say 'You know, I'm going to re-architect this for another approach.'  Data entry forms for complex data can be exceedingly difficult to get right.  Keep at it until you have something you are happy with!

Nick67
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question