Solved

Restricting Certain Characters/spaces, etc on a control

Posted on 2015-01-13
22
137 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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:Ernest Grogg
Comment Utility
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
Comment Utility
@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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
Comment Utility
So...sorry just read the updates.

so should I change it to .Value?.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Ernest Grogg
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
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

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now