setfocus not working correctly

Fordraiders
Fordraiders used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Does the "Please check..." error message show up?
Do you get an error?
Is the textbox visible at the time?

Author

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
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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?
Ensure you’re charging the right price for your IT

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!

Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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
Distinguished Expert 2017

Commented:
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.
John TsioumprisSoftware & Systems Engineer

Commented:
Have you checked if you have a OnGotFocus Event on Me.CSP_SKU_Minimum_Dollar_Spend that moves the focus to another control.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
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

Author

Commented:
Pat, For some reason my dba set the field type up as Text(nvarchar(50)..not numeric..
Linked table to sql server.
Distinguished Expert 2017
Commented:
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()
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Distinguished Expert 2017

Commented:
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!!!
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
How about setting focus on the original question?

Author

Commented:
Thanks all.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial