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?
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?