Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 12-07-2004, 08:18 AM   #1
solotoo
Registered User
 
solotoo's Avatar
 
Join Date: Nov 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
solotoo is on a distinguished road
setFocus back to incorrect entry

Help please

I have created a form in Excel with text entry boxes, in the first box the text required is either A,B or C.
If an incorrect letter is entered I want to focus to go back to this cell. But I can t seem to manage this.

The code I have is...

Private Sub txt_ABC_Exit(ByVal Cancel As MSForms.ReturnBoolean)

txt_ABC.Text = UCase(txt_display.Text)

If txt_ABC.Text Like "[A,B,C]" <> False Then
txt_NEXT.SetFocus
Else
txt_ABC.SetFocus
lbl_error.Caption = "Error, incorrect entry"
'txt_ABC.SelStart = 0 ' tried this - didnt work!!
End If
End Sub


I can get focus on any other text box but the one I want!
Any ideas greatfully received.

solotoo is offline   Reply With Quote
Old 12-07-2004, 02:28 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
The exit event is the wrong event to use for any edit code. You need to edit data BEFORE it is saved. If the edit is related to only a single field, the correct place is in that field's BeforeUpdate event. If the edit involves multiple fields or checks for null fields, the edit belongs in the form's BeforeUpdate event.

Code:
Private Sub txt_ABC_BeforeUpdate(Cancel As Integer)
If UCase(Me.txt_ABC) In("A","B","C") Then
Else
    Me.lbl_error.Caption = "Error, incorrect entry"
    Cancel = True
End If
End Sub
Private Sub txt_ABC_AfterUpdate()
    Me.txt_ABC = UCase(Me.txt_ABC) 
End Sub
You can't change the data in the BeforeUpdate event so I added the UCase() to the AfterUpdate event.

I wouldn't do this at all. I would use a combo with the acceptable values set to LimitToList and let Access do my coding for me. If you don't let the user put anything invalid into the field, you don't need to worry about editing it. The valid values should be specified at the table level rather than the form level so that Jet actually does the editing. If the list is longer or you want to allow entries to be added by the user, it is best to use a table to hold the valid values. You can then create a relationship and use Referential Integrity to validate your data.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-07-2004, 11:22 PM   #3
solotoo
Registered User
 
solotoo's Avatar
 
Join Date: Nov 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
solotoo is on a distinguished road
Thanks

I'll try that, unfortunately I can't use combo boxes as Im trying to emulate an existing screen which has been written in MSBASIC/DOS6 or something as ancient!!

solotoo is offline   Reply With Quote
Old 12-08-2004, 10:44 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
But if it is "bad" why recreate it? Aren't you taking "emulate" too literally? I'm sure your form doesn't actually look like a DOS form so why shouldn't you use combos where appropriate?
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-08-2004, 11:03 PM   #5
Oldsoftboss
AWF VIP
 
Oldsoftboss's Avatar
 
Join Date: Oct 2001
Location: in the shed
Posts: 2,504
Thanks: 0
Thanked 57 Times in 49 Posts
Oldsoftboss will become famous soon enough
Quote:
Originally Posted by solotoo
Help please

I have created a form in Excel ....
Also in the wrong forum


.
__________________
Apathy is on the increase, but who cares.

Imagine if there were no hypothetical questions

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Oldsoftboss is offline   Reply With Quote
Old 12-09-2004, 07:06 AM   #6
solotoo
Registered User
 
solotoo's Avatar
 
Join Date: Nov 2004
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
solotoo is on a distinguished road
Quote:
Originally Posted by Oldsoftboss
Also in the wrong forum
.
I thought as this was a VBA question it should go in the VBA thread, not the specific Excel Thread.
__________________
If a tree falls in the woods, and no-one sees, do the other trees laugh at it?
solotoo is offline   Reply With Quote
Old 12-09-2004, 09:42 AM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,133
Thanks: 15
Thanked 1,570 Times in 1,492 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
I seem to have skipped right over that Excel business

solotoo,
Although many people here are multi-lingual, so to speak, the object model in Excel is sufficiently different that people familiar with Access VBA may not be able to help. This question might get a better response in the Excel forum. I'm not going to move it because someone may be able to help and I'm guessing that most members don't even read the Excel forum so if you make a "duplicate" post there, no one will care or comment

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 12-09-2004, 11:23 AM   #8
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 86 Times in 49 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
Quote:
Originally Posted by solotoo
Help please

I have created a form in Excel with text entry boxes, in the first box the text required is either A,B or C.
If an incorrect letter is entered I want to focus to go back to this cell. But I can t seem to manage this.

The code I have is...

Private Sub txt_ABC_Exit(ByVal Cancel As MSForms.ReturnBoolean)

txt_ABC.Text = UCase(txt_display.Text)

If txt_ABC.Text Like "[A,B,C]" <> False Then
txt_NEXT.SetFocus
Else
txt_ABC.SetFocus
lbl_error.Caption = "Error, incorrect entry"
'txt_ABC.SelStart = 0 ' tried this - didnt work!!
End If
End Sub


I can get focus on any other text box but the one I want!
Any ideas greatfully received.
The proper event to use would be the Worksheet_Change() for that Excel worksheet. But I do not recommend that method.

The easiest way to restrict what the users enters would be to use "Validation" for the needed cells. Select the cells you want to validate and click the menu bar options in Excel; Data / Validation... and choose these settings... Allow: = List, Source: = A,B,C, Check the "In-Cell Dropdown" option in the Settins tab. Customize your message in the Input Message and Error Alert tabs. Play around with the settings until you get the desired resilts.

__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 07:19 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World