AfterUpdate/BeforeUpdate data entry validation problem 3

[Vie]

I can't figure out how to stop Access from moving the focus to the next control on a form after a sub executes.

Basically, I'm trying to validate the value the user has just entered in tbx1 before they can move to tbx2. The values are integers and the textboxes are unbound (they just collect the values in two variables to be used later). If the value in tbx1 has already been used, a message box pops up telling them so and asks them if they'd like to start with the next value, yes or no. In other words, if they entered 4, it asks if they'd like to start with 5, yes or no.

If yes, 5 gets entered in tbx1 and the focus pops over to tbx2. Fine.

If no, I want the 4 to be deleted and the focus to remain in tbx1 so they can enter whatever other number they want, and, again, if they enter a number that already exists, the code runs over again. But what happens is the focus pops over to tbx2 and the user has to manually move back over to tbx1 to enter a corrected value.

I have tried this with the procedure running on AfterUpdate as well as on BeforeUpdate. With AfterUpdate, the focus ALWAYS ends up on tbx2, no matter even if I tell it to SetFocus to tbx1 at the end of the sub, probably because the tab key was pressed in order to trigger the event. With BeforeUpdate, it won't allow me to change the value of tbx1 because "can't save data in the field" because (I guess this is the reason) the BeforeUpdate event is still running. I tried Cancel = True, and SendKeys, etc., but these don't work either. Once the msgbox pops up, the value is committed, not to any recordset, just to the variable) for that textbox, i.e. Value and OldValue are 4.

Does anyone know how can I make this work?

 

[PHV]

In the BeforeUpdate event procedure:
Cancel = True
Me!tbx1.Undo

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Vie]

Thanks for replying. I tried that but I get run-time error -2147325267 which says "The macro or function set to the BeforeUpdate or ValidationRule property of this field is preventing Microsoft Office Access from saving data in the field."

Doesn't cancel = true, undo method only apply to records anyway? In my case, I'm not dealing with any records at this point. I'm not sure if that has anything to do with it but maybe?

 

[PHV]

Can you please post your whole tbx1_BeforeUpdate code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Vie]

Here's the code:

rstFloors.Find "Floor = " & intFloorStart,,adSearchForward
  If Not rstFloors.EOF Then
       Cancel = True
       Me.tbxFloorStart.Undo
     If MsgBox(msg, vbYesNo, "Floor already exists") = vbYes Then
       Me.tbxFloorStart = intNextFloor
     Else 'vbNo
       Me.tbxFloorStart = ""
  End if
End if

 

[PHV]

What about this ?
rstFloors.Find "Floor = " & intFloorStart,,adSearchForward
If Not rstFloors.EOF Then
If MsgBox(msg, vbYesNo, "Floor already exists") = vbYes Then
Me.tbxFloorStart = intNextFloor
Else 'vbNo
Cancel = True
Me.tbxFloorStart.Undo
End if
End if

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Vie]

Well, that's better for vbNo. But it still doesn't work for vbYes. Same error. I tried putting Cancel, Undo, etc. in the vbYes If block and still the same error occurs.

 

[PHV]

Some inconsistency in your posts:
But it still doesn't work for vbYes
versus:
If yes, 5 gets entered in tbx1 and the focus pops over to tbx2. Fine
Where is intNextFloor coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RickSpr]

The error message is coming from trying to call the control's Undo method in the BeforeUpdate event. That isn't allowed. (Yes, you can Undo a control, but not in the BeforeUpdate event, because Access is still waiting for you to decide whether to store the data in the underlying record, which doesn't happen until you exit the procedure. That doesn't apply to unbound fields, of course, but Access doesn't make an exception on this.)

My take on this is that that's a good thing. It's not nice to display a message box telling the user he'd entered bad data, and then erase the data before he has a chance to see what he entered. (It may be that the data is visible even when the message box is being shown, but it may be that it isn't, either. You're not in control of where the user may have moved the window, so the message box may be covering the field.)

If you Cancel the BeforeUpdate event, that should be enough. The cursor will stay in the field, and whatever the user entered will be selected. It isn't blanked out, but as soon as he starts typing again, the selection will be replaced with his typing, so it's not like he would have to erase it first. You can't erase the field in the BeforeUpdate event, but the user's keystroke sequence is the same as if you could.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PHV]

Rick, I disagree.

Private Sub ProductName_BeforeUpdate(Cancel As Integer)
If(Not IsNull(DLookup("[ProductName]", _
"Products", "[ProductName] ='" _
& Me!ProductName & "'"))) Then
MsgBox "Product has already been entered in the database."
Cancel = True
Me!ProductName.Undo
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[RickSpr]

PH: I know I've gotten the same message from trying to Undo a control, and I'm pretty sure it was an unbound control. Do you actually have working code that does this? Could it be something that's changed since Access 2000 came out? (I haven't tried it on later versions.)

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PHV]

Do you actually have working code that does this?
Yes with a bound control in access 2003.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Vie]

Thanks PH and Rick.

Rick: You're right about not needing the Undo. vbNo works okay with the Cancel alone.

PH: What might've been confusing is, in my first msg, vbYes worked fine when the code was in the AfterUpdate. vbNo worked too, in terms of changing the value of tbxFloorStart (tbx1), but it moved the focus over to tbx2. When I put the code on BeforeUpdate, vbNo works but vbYes doesn't, in the way Rick said and what I suspected -- BeforeUpdate is still running.

Rick and PH: Any ideas on how I can reconcile this? Do I have to use both events to tell the user what they entered is bad?

Many thanks,
Vie

 

[PHV]

What is the actual behaviour of "vbYes" and the relevant code in the relevant event ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Vie]

I hope this helps explain better:

AfterUpdate

rstFloors.Find "Floor = " & intFloorStart,,adSearchForward
  If Not rstFloors.EOF Then
       If MsgBox(msg, vbYesNo, "Floor already exists") = vbYes Then
       Me.tbxFloorStart = intNextFloor
     Else 'vbNo
       Me.tbxFloorStart = ""
  End if
End if

In this case, vbYes works as planned. If the user mistakenly entered a repeat floor, floor #4 for example, the msgbox tells them 4 is already there, did they mean to start with floor 5? So when they answer yes, 5 goes into the tbxFloorStart. The problem here is vbNo. tbxFloorStart gets nulled out but the focus pops to the next textbox and leaves no value in tbxFloorStart. If I use Cancel here, I get an error about the arguments not matching the event or something like that. I think Cancel isn't a possible argument for AfterUpdate?

BeforeUpdate (modified as per Rick)

rstFloors.Find "Floor = " & intFloorStart,,adSearchForward
  If Not rstFloors.EOF Then
     If MsgBox(msg, vbYesNo, "Floor already exists") = vbYes Then
       Me.tbxFloorStart = intNextFloor
     Else 'vbNo
       Cancel = True
  End if
End if

In this case, vbNo works fine but the error I mentioned in a previous post occurs on vbYes.

 

[PHV]

So, keep playing only with the AfterUpdate event with something like this for "vbNo":
Else 'vbNo
Me!tbxFloorStart = ""
Me![another control].SetFocus
Me!tbxFloorStart.SetFocus
End if


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Vie]

PH, It worked! Thanks very much. I wouldn't have thought of of trying that. Have a star! -Vie