SetFocus troubleshooting needed

Joined
Mar 23, 2004
Messages
241
Hi everyone...

Could someone have a look at the following and tell me why the SetFocus isn't working? I think I've followed the syntax correctly, but I could be wrong...

I want the Date_Completed field to be highlighted once all the other gubbins has run, but it's currently still moving on to the next control...

Code:
Private Sub Date_Completed_AfterUpdate()
If Me.Date_Completed < Me.Last_Updated Then
MsgBox "The date you have entered is before the date the error was created. Please check the date and try again.", 48, "No!"
Me.Date_Completed = Null
Date_Completed.SetFocus
End If
End Sub

Any help would, as always, be greatly appreciated.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,071
Office Version
  1. 365
Platform
  1. Windows
Shot in the dark, but have you tried this?

Me.Date_Completed.SetFocus
 
Joined
Mar 23, 2004
Messages
241
Hi Jmiskey...

Thanks for your reply.

Yeah, I tried that, but to no avail. :confused: Seems a little weird, I could swear it should work. Any other suggestions?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Billy

Is the code actually being run?

Is the If statement working as expected?
 
Joined
Mar 23, 2004
Messages
241

ADVERTISEMENT

Hi Norie. Thanks for your reply.

The code appears to run fine. The If statement runs, the error message appears as desired, and the Date_Completed field is blanked. The only bit that's failing is the highlighted field is moving to the next one, rather than the one called Date_Completed. I don't get any error messages at all.

It's a bit of a headscratcher in all honesty.

It's not going to make or break my program, but I have a nagging suspicion that SetFocus might be used fairly frequently further along my fledgling VBA career, so I want to understand it fully, you know?

Any ideas?
 
Joined
Mar 23, 2004
Messages
241
Well, this has worked. I tried setting the focus to a different field (in this case one called "PEST"), then doing the SetFocus again to the Date_Completed field.

So, I guess I'm sorted now. It's still a bit of a mystery why it didn't work the straightforward way, though. Anyway, hope this will be of use to future people reading this thread.

Many thanks to Norie and jmiskey for taking the time to help out. It's really appreciated. I'm not worthy. :pray:

Code:
Private Sub Date_Completed_AfterUpdate()
If Me.Date_Completed < Me.Last_Updated Then
MsgBox "The date you have entered is before the date the error was created. Please check the date and try again.", 48, "No!"
Me.Date_Completed = Null
Me.PEST.SetFocus
Me.Date_Completed.SetFocus
End If
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Have you tried using the Exit event rather than AfterUpdate?

If you set Cancel to false in there it shouldn't leave the field.

Then again the field mighht not be updated yet and the If might not work.
 
Joined
Mar 23, 2004
Messages
241
Hi again Norie. I'll check it out. How do I change the Cancel setting? I can't find how I actually use it in either the help files or the book I've got.

Sorry for being so stupid, I find the help files probably more confusing than helpful... :confused:
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,318
Office Version
  1. 365
Platform
  1. Windows
Billy

It isn't a setting it's a parameter/variable(?).

Just try

Cancel = True

I know this works with userforms in Excel but forms in Access are slightly different.
 
Joined
Mar 23, 2004
Messages
241
Hi Norie. Thanks for the help on that. I gave it a try, but the way it locks the field would confuse the hell out of the people who use the database, so I'll stick with the original workaround.

Still, I've learned something today, so thanks for the assistance!

Cheers,
CSBBB.
 

Forum statistics

Threads
1,147,686
Messages
5,742,623
Members
423,744
Latest member
bkirtland

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top