setfocus not working correctly

Access 2010  vba
I have a textbox:  CSP_SKU_Minimum_Dollar_Spend

Problem:  SETFOCUS is not going back to my textbox on error

LOOKING FOR A VALUE BETWEEN  1 AND 1000000  ONLY:  
NO DECIMALS OR COMMAS...OR whatever characters

Private Sub CSP_SKU_Minimum_Dollar_Spend_AfterUpdate()


' check length first to see if it meets requirements  >=1 and <=7

If Len(Me.CSP_SKU_Minimum_Dollar_Spend) < 1 Or Len(CSP_SKU_Minimum_Dollar_Spend) > 7 Then
   MsgBox "Please Check the Number For Verification", vbCritical, "Length Check"
   Me.CSP_SKU_Minimum_Dollar_Spend.SetFocus  ' <------------------------------------   This is NOT SETTING THE FOCUS BACK TO THE TEXTBOX
   Exit Sub
End If

 END SUB


Thanks
fordraiders
LVL 3
FordraidersAsked:
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.

Martin LissOlder than dirtCommented:
Does the "Please check..." error message show up?
Do you get an error?
Is the textbox visible at the time?
0
FordraidersAuthor Commented:
Martin:
Does the "Please check..." error message show up? =  yes
Do you get an error?   = no
Is the textbox visible at the time?   = yes

Thanks
fordraiders
0
Martin LissOlder than dirtCommented:
I don't do Access but I notice in your code that apparently you don't need to use "Me" since it not always used and you still get the MsgBox.

If Len(Me.CSP_SKU_Minimum_Dollar_Spend) < 1 Or Len(CSP_SKU_Minimum_Dollar_Spend) > 7 Then
   MsgBox "Please Check the Number For Verification", vbCritical, "Length Check"
   Me.CSP_SKU_Minimum_Dollar_Spend.SetFocus

Try it without the  "Me".

If that doesn't help (and it probably won't) please tell me what event does the code appear in. LostFocus?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Martin LissOlder than dirtCommented:
I believe I know what the problem is. Add line 4.

Private Sub CSP_SKU_Minimum_Dollar_Spend_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(CSP_SKU_Minimum_Dollar_Spend) < 1 Or Len(CSP_SKU_Minimum_Dollar_Spend) > 7 Then
   MsgBox "Please Check the Number For Verification", vbCritical, "Length Check"
   Cancel = True
   CSP_SKU_Minimum_Dollar_Spend.SetFocus
      Exit Sub
End If
End Sub

Open in new window

0
Dale FyeCommented:
No, Martin, you cannot cancel an AfterUpdate event in Access.

FordRaiders,

You might want to consider putting that test in the Form_BeforeUpdate event, which can be cancelled.  That is where I put all of my UI logic code.

The problem is, what if >7 characters is correct?  you might want to consider a message box with options for vbOkCancel, which would allow the user to select an option.  If they select 'Cancel', then you would set the focus back to the control.  If they click OK, the code would proceed to the next check.

Dale
0
PatHartmanCommented:
If you are working with  a numeric field, why are you using Len()?  Why would you not be using a value range?
LOOKING FOR A VALUE BETWEEN  1 AND 1000000  ONLY:  
NO DECIMALS OR COMMAS...OR whatever characters
This is pretty suspicious since numeric values are not strings and so the only character other than numbers that you would need to account for would be the decimal point.  If the field is a string, none of the comparisons will work correctly.
0
John TsioumprisSoftware & Systems EngineerCommented:
Have you checked if you have a OnGotFocus Event on Me.CSP_SKU_Minimum_Dollar_Spend that moves the focus to another control.
0
Gustav BrockCIOCommented:
Have a dummy control (a tiny textbox stowed away) you can move focus to, then move it back:

Me!DummyControl.SetFocus
Me!CSP_SKU_Minimum_Dollar_Spend.SetFocus

Open in new window

0

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
FordraidersAuthor Commented:
Pat, For some reason my dba set the field type up as Text(nvarchar(50)..not numeric..
Linked table to sql server.
0
PatHartmanCommented:
Get him to change it if this is numeric data.  Numeric codes such as SSN and Zip and things like Customernum generated by a different application are codes even though they only contain numeric digits but codes are always fixed length and should be stored as text.

If the DBA won't change the data type (and you can't get him fired), you have to convert the field EVERY TIME you use it.  Use either Val() or CInt()
0
Dale FyeCommented:
Another option, besides converting it every time you use it is to create a query (or view) that already includes that transformation for you, then use that instead of the linked table for most of your work with that table.
0
PatHartmanCommented:
Dale is correct.  Creating a single query and being disciplined and ONLY ever using that query and NEVER using the table directly will work but why use a band aid when you can fix the problem at the source and not have it hanging over your head like the Sword of Damocles.  It is stuff like this that cause serious problems down the line when you are no longer the developer and someone who doesn't know about the problem does something new and mistakenly uses the table directly.

If you end up having to use this work around, I would actually name the table something like:
DoNotUseThisTable_UseQueryName

AND show it to your boss and the DBA!!!
0
Dale FyeCommented:
No disagreement with Pat on this one.  Getting this right, early on, will make your job so much easier in the long run.  I've got a couple of applications I've inherited and maintain where I have to deal with this.  Unfortunately, the client is not willing to pay to have this part of the applications fixed, so we continue to stumble along and paste band-aids all over the application.
0
Gustav BrockCIOCommented:
How about setting focus on the original question?
0
FordraidersAuthor Commented:
Thanks all.
0
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.