AfterUpdate/BeforeUpdate and SetFocus

I have some problems with the SetFocus command in a Userform with an 
AfterUpdate or BeforeUpdate routine.

To make it clear to you, I made a very small Userform to demonstrate it. It 
only contains 8 TextBoxes and this code:

Code:
Private Sub TextBox01_AfterUpdate()
Me.TextBox08.SetFocus
End Sub
-
Private Sub TextBox02_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
TextBox08.SetFocus
End Sub
-
Private Sub TextBox03_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox08.SetFocus
End Sub
-
Private Sub TextBox04_Change()
TextBox08.SetFocus
End Sub

I expected that in all cases, the focus should be set to TextBox08, but this 
only happens with the Change event.

Do you have any suggestions how I can set the focus to TextBox08 in an 
AfterUpdate or BeforeUpdate event?

I use Excel 2007

Kind regards,

Otto. 
0
Utf
5/5/2010 7:31:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
4376 Views

Similar Articles

[PageSpeed] 8

Hi Otto,

I believe that the problem you face is when the event takes place. Other 
than change, the cursor has already moved to the next control before the 
event fires. The change event takes place as soon as any change is made and 
not necessarily after all changes have been made within the control. Example 
change text to pest; change event takes place as soon as you type the p.

The following is one method of achieving your desired result. I am 
interested in other solutions so I'll monitor this thread for any.

On the enter event sets all the tab stops to False and then sets the tab 
stop of the desired next control to True. I have used 8 Text boxes and set 
the tab stop to a different text box on each of the Enter events.

Private Sub TextBox01_Enter()
Call TabStopsFalse
Me.TextBox05.TabStop = True
End Sub

Private Sub TextBox02_Enter()
Call TabStopsFalse
Me.TextBox06.TabStop = True
End Sub

Private Sub TextBox03_Enter()
Call TabStopsFalse
Me.TextBox07.TabStop = True
End Sub

Private Sub TextBox04_Enter()
Call TabStopsFalse
Me.TextBox08.TabStop = True
End Sub

Sub TabStopsFalse()
Dim ctrl As Control
For Each ctrl In Controls
  ctrl.TabStop = False
Next ctrl
End Sub

  
-- 
Regards,

OssieMac


"odekkers" wrote:

> I have some problems with the SetFocus command in a Userform with an 
> AfterUpdate or BeforeUpdate routine.
> 
> To make it clear to you, I made a very small Userform to demonstrate it. It 
> only contains 8 TextBoxes and this code:
> 
> Code:
> Private Sub TextBox01_AfterUpdate()
> Me.TextBox08.SetFocus
> End Sub
> -
> Private Sub TextBox02_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
> TextBox08.SetFocus
> End Sub
> -
> Private Sub TextBox03_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> TextBox08.SetFocus
> End Sub
> -
> Private Sub TextBox04_Change()
> TextBox08.SetFocus
> End Sub
> 
> I expected that in all cases, the focus should be set to TextBox08, but this 
> only happens with the Change event.
> 
> Do you have any suggestions how I can set the focus to TextBox08 in an 
> AfterUpdate or BeforeUpdate event?
> 
> I use Excel 2007
> 
> Kind regards,
> 
> Otto. 
1
Utf
5/5/2010 8:14:01 PM
Reply:

Similar Artilces:

AfterUpdate/BeforeUpdate and SetFocus
I have some problems with the SetFocus command in a Userform with an AfterUpdate or BeforeUpdate routine. To make it clear to you, I made a very small Userform to demonstrate it. It only contains 8 TextBoxes and this code: Code: Private Sub TextBox01_AfterUpdate() Me.TextBox08.SetFocus End Sub - Private Sub TextBox02_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) TextBox08.SetFocus End Sub - Private Sub TextBox03_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox08.SetFocus End Sub - Private Sub TextBox04_Change() TextBox08.SetFocus End Sub I expected that in ...

AfterUpdate
Hello Chaps, In the AfterUpdate code I had the detail back colour change if a check box (tbc) = True. However if I tick the box now it turns the back colour of every form irrespective if that record is ticked tbc or not! Any help on solving this would be great. JAMES Private Sub tbc_afterupdate() If Me.tbc = True Then Me.Section(0).BackColor = 65535 Else Me.Section(0).BackColor = 16777215 End If End Sub If you are using a continuous form, that code won't work. The good news is that you can use Conditional Formatting to make it work. In Design View, select the combo box and fro...

AfterUpdate Question!
I want to add this to my code , Ony enter date if [DateCheck] (checkbox) is -1 Any help would be great..............Thanks Bob Private Sub tbAdditionCharge_AfterUpdate() If tbDayNo.value = "" Or IsNull(tbDayNo.value) Then tbDayNo.value = Format(Now, "dd-mmm-yy") End If End Sub Hi Bob, Me.tbDayNo=IIf(Me.DateCheck And IsNull(tbDayNo), _ Format(Date,"dd/mm/yy"),tbDayNo) Regards Jacob "Bob V" <rjvance@ihug.co.nz> wrote in message news:%23xApdzD7HHA.4880@TK2MSFTNGP03.phx.gbl... | | I want to add this to my code , Ony enter date if [D...

SetFocus to a control on a subform
Hi I can use your help in getting the cursor back to a control on a continuous Subform. The sub has two controls, Inv# and Amount. I use the AfterUpdate on the Amount control to test whether the payment amount equals the invoice amount. If the two are not the same, I prompt the user and would like to return the cursor to the Amount control on the last line in this multi-line subform. Thanks Here's the syntax you need in the 2 step process: Me.SubFormControl.SetFocus '-- Set Focus to the SubFormControl 1st Me.SubFormControl.Form!ControlName.SetFocus &#...

Check for Duplicate value BeforeUpdate
Hello all, I have a problem with updating on my form. I have a form, TrainingHistory, that is linked to a table by the same name. There are three fields showing: FullName, ClassName, and DateTaken. For each person, I am only interested in the MOST RECENT date they took any given class. So what I want is to be able to enter a name and class, and have Access check the table for this combination of name and class. If such an entry already does exist, I would like the form to bring up that entry, and that entry alone, for editing of the date. There should also be a message explaining what ju...

BeforeUpdate or AfterUpdate ?
Hi all, I have an unbound form in which I have (amongst others) two comboboxes with mandatory data. Although I present a combobox, limited to the choices to pick out, people manage not to fill in those data and to jump via the TAB-key to the next field. Of course I’ll have NULL value for this combobox, but how can I force the user to look for the correct row ? In the BeforeUpdate or in the AfterUpdate event ? I have some code like : Private Sub CboAfterUpdate() if isnull(me.Cbo) then Beep Msgbox(“You must choose a value”) Me.cboAfter.setfocus endif end sub But this does not se...

Cancel/BeforeUpdate Event Procedure
I have a main form Containers with a subform ContainerSubstances. The latter has a combo box showing the substances in the Substances table. If the desired substance is not in the combo, the user may bring up the Substances form in Add and Dialog mode. The user can press Save or Cancel from the latter form, and both work as designed. If Save is pressed, the combo box on the original subform is requeried to include the new value, and then the new value is selected by the code, and the Substances form closes, and the user returns to the Containers form, with focus set in the ...

Help! Setfocus problem
i ave a dropdown field on the first tab. I use setfocus so that it selects a different field to stop users scrolling down and changing the selection on the load event. Problem When a user changes tab and come back to the origional tab the setfocus doesnt run as the form is already loaded. How do you get around this? This is driving me and users mad at minute...please help before i open another bottle of dark rum! Hi Brian, Check out following URL; how to fire onclick event on tab click. It may help you. http://crmrocks.net/?p=16 -- PLEASE do click on Yes or No button if this post wa...

AfterUpdate Problem
I need to flash an "RE-ORDER ALERT" message after inventory level falls below re-order level. The message only flashes up when I move to a next record and back after a DoCmd.Save. I need the message to flash immediate after a Save. Note. The message is a flashing label on the form. Thank you. Perhaps you could code it to actually flash before DoCmd.Save is processed. Once you're into that code string, it will be run. Or, maybe consider a Message Box. I like this idea because it forces the user to interact with the reorder message. No one can say "I didn'...

AfterUpdate VB If statement
I am having a problem with this code: Private Sub Cart_Shed_Number_AfterUpdate() If (Me.Cart_Shed_Number) = "NA" And (Me.Cart_Type) = "NA" Then Me.Cart_Shed_Fee = "0" ElseIf (Me.Cart_Shed_Number) >= "A" And (Me.Cart_Type) = "Gas" Then Me.Cart_Shed_Fee = "10" ElseIf (Me.Cart_Shed_Number) >= "A" And (Me.Cart_Type) = "Electric" Then Me.Cart_Shed_Fee = "20" End If End Sub I want it to auto populate a "cart shed fee" field on a form depending on the answers to the &q...

SetFocus is ignored
I have textboxes in the Form Header of the Main Form ; and have a SubForm in the Detail Section. On the last box in the header area I put in the OnExit event: Me.SubForm.Form!ABCtextbox.SetFocus ....and it is ignored...no error or anything...the tab returns to the first box in the Form Header.... If I put my cursor in the subForm's ABCtextbox, everything is fine and it does not have the tab stop turned off or anything... Any suggestions? -- NTC Is there anything else happening at the time? For example, moving record, loading the form, applying a filter/sort, or saving the recor...

Re: A2K format
"NoodNutt" <mclind1@bigpond.com> wrote in message news:... > G'day ppl > > Have a form which after the user has completed all data input can invoice > job out. > > I have a command button: > > Private Sub InvoiceBtn_Click() > Dim InvoiceResp As Integer > InvoiceResp = MsgBox("Are you sure you want to mark this record for > Invoicing ?????", vbYesNo) > If InvoiceResp = vbYes Then > Me.Invoice = 1 > Me.WeekNo = Me.DateDel > Else > DoCmd.CancelEvent > End If > End Sub > > What I wo...

Unbound combobox AfterUpdate
Hi On some form I have several unbound combobox controls. Whenever some of them is changed, I want: 1. reset the values for other combos on same form; 2. reset Filter and FilterOn properties for subform on same form; 3. reset Rowsource for a record locating combobox on this subform. Currently I use combo's AfterUpdate's events for this - like here: Private Sub Combo1_AfterUpdate() Me.Combo2 = 0 Me.Combo3 = 0 If Nz(Me.Combo1, 0) = 0 Then Me.MySubform.Form.Filter = "" Me.MySubform.Form.FilterOn = False ...

blinking form afterupdate
Hi, When i use this command: DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 The form blinks with every update. Is there a way to prevent the form from blinking ? I hope to hear from you ! Best regards and thanks in advance, Pierre This code requeries the form and displays any changes to the data. Where are you using that code - is it on a button or some other event? Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia "Pierkes" <info@advisearch.nl> wrote in message news:4bf61674$0$31931$703f8584@news.kpn.nl... > Hi...

AfterUpdate Requery
I'm having a problem with Requery. I have 7 combo boxes that are cascading. One gets it's data from the previous one. The first three requery and blank out the combo boxes if the item that was selected isn't in the new list. Although the last four do requery and get a new list, they keep the previous selected text even if it isn't in the new list. I set the Limit To List option but that didn't have an effect on the last four combo boxes. I posted the different ways I've tried requerying below. The only one that seems to remove the data is DoCmd.Requery but on...

Not triggering "BeforeUpdate" in text box on UserForm
OK, so I need an education here (and what better place to find one...) In Excel 97 I have a UserForm with a text box that has VBA code in the '__BeforeUpdate()' event. The form also has a command button. It appears that if the user types stuff in the textbox, then immediately mouse-clicks the command button, the _beforeUpdate() event of the text box is not triggered. It also appears that the ControlSource range of the text box is not updated in this circumstance. The _Change() event is not a particularly good option here, since I still have to know when the user has fully completed the...

AfterUpdate Form Event Fires Repeatedly
I've been having this problem for a few months with a db I'm working on. I know there must be something simple I'm overlooking, so I'd greatly appreciate any help in identifying what the problem is. I'm using the following to create a timestamp: Me.dbLastUpdateUser = Forms!frmUserInfoHIDDEN!txtUserName Me.dbLastUpdate = Now() frmuserInfoHIDDEN refers to a form that is hidden on startup that stores the name the user logged in with. This feature works fine. The AfterUpdate timestamp used to work as well. However, now it repeatedly fires and forces the user to stay on ...

Triggering AfterUpdate Event
I have a data entry form using forms and multipages with numerous dat fields for the user to populate. I have also written error handlers t restrict the input of the user using the AfterUpdate event (e.g. I they enter an invalid entry for a date field and move off, th AfterUpdate triggers an error message, which pops up next to th field). The general usage of this is that someone will open a new form, ente data, and write to a worksheet. Since I do not allow invalid data to b written, I can ensure that the data is correct when it is written, s when I open the form, I do not need to validate ...

AfterUpdate event procedure
I have an AfterUpdate procedure that populates values from a subform into fields on my main data entry form. The forms/subforms were created using Access 2000 and worked correctly in Access 2003. When I upgraded to Access 2007, the code no longer works....unless I enter the STUDYID value twice. Any ideas as to why this is occurring? And how do I fix it? Private Sub STUDYID_AfterUpdate() [LNAME] = [frmsubIsol07].Form![Lname2] [MI] = [frmsubIsol07].Form![MI2] [FNAME] = [frmsubIsol07].Form![Fname2] [COUNTY] = [frmsubIsol07].Form![County2] [HOSPID] = [frmsubIsol07].Form![Ho...

Repeat afterupdate code
Hello, I have a form that has about 25 inputs for data. I then have about 15 boxes that I just calculate some numbers so people can see what the effects are as they enter it. So for every data entry point I have the code behind AfterUpdate calling Private Sub Start_number_AfterUpdate() Call Calculations End Sub Private sub Calculations() ..... End sub Is there a way to have the calculation boxes all update after any of the data entry points have without have to put in the afterupdate behind each one? It works fine the way it is right now but I would like to clean it ...

A2K format
G'day ppl Have a form which after the user has completed all data input can invoice job out. I have a command button: Private Sub InvoiceBtn_Click() Dim InvoiceResp As Integer InvoiceResp = MsgBox("Are you sure you want to mark this record for Invoicing ?????", vbYesNo) If InvoiceResp = vbYes Then Me.Invoice = 1 Me.WeekNo = Me.DateDel Else DoCmd.CancelEvent End If End Sub What I would like to have happen is that before it updates Me.Invoice, it checks that the [PackStatus]=6 (6=Pack is Delivered). So if the criteria doesn't match then di...

AfterUpdate Now() to table
I am using: CurrentDb.Execute "UPDATE tbl_Projects SET DateUpdated = Now()" as suggested here, but it is updating ALL records, how do I specify the record that was actully changed? If you are talking about TimeStamping a record that was updated on a form then you want your code in the BeforeUpdate event of the form. In this case you do not need SQL but simply: [DateUpdated] = Now MeSteve wrote: >I am using: >CurrentDb.Execute "UPDATE tbl_Projects SET DateUpdated = Now()" >as suggested here, but it is updating ALL records, how do I specify the >record that w...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

AfterUpdate Event for TextBox
I have this code for the afterupdate event on a text box that has data in it. Me.Text732 = Replace(Abs(Val([Text732])), ".", "") When I past this data 0000272400123456 into the text box, it gets rid of the 0000, becomes 272400123456 But if I past this kind of number 4223697600059898, it turns it into this 42236976000599E+15 If I take the coding away and past the 4223697600059898 number, it stays the same and doesn't get changed to the +15. Any suggestions or changes to code to fix this? Thanks! Curtis if its a text field then it shouldnt be modifying any v...

Checkbox Click (and Afterupdate?)
I have a form with multiple checkboxes in the following format: _X_ Northeast _X_ New England _X_ State1 _X_ State2 ... _X_ Mid Atlantic _X_ State5 _X_ State6 ... Using the following code in the On Click Event I was able to select all the states in each row (i.e. all the states in New England): If (Me."New England") Then Me.State1 = True Me.State2 = True ... Else Me.State1 = False Me.State2 = False ... End If I thought that if I were to apply the same principle to the "Northeast" checkbox...: If (Me."Northeast") Then Me."New Engla...