OnDirty goto, but only after validated? 3

[Lunatic]

Goal: In a series of fields on a form, user enters # in fields. If #:
a) meets validation rule, focus is set to the next field.
b) does not meet validation rule, error box appears informing of why (# =/= 1, 2, 3, or 9) and field is reset to null.

What I've tried is to set the validation rules in the properties box for the field. That worked. Created a macro to advance to the next field OnDirty (a goto macro). They work together fine with valid entries, they don't work together fine with invalid entries.

What happens is an error message is returned telling me the macro can't run and asking me to halt the process.

Should I be placing the macro with a different event trigger? I like the on dirty goto because as they are only entering a single digit.

Thoughts or suggestions on what I should be doing are much appriciated!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PaulBricker]

I would use the Before Update event to control this.

Private Sub Text0_BeforeUpdate(Cancel as Integer)
If Text0 <> 1,2,3,9 Then
Cancel = True
MsgBox "Your message here"
Else
End If
DoCmd.GoToControl ctlName
End Sub

You can forget the goto command if the control you are going to is the next in the tab order.


Paul

 

[Lunatic]

Paul,

Thank you for the help. I've gotten closer. However when I use the BeforeUpdate event I can't get it to advance to the next control because it says it needs to save the data.

Here's what it looks like

Private Sub Ctl1_BeforeUpdate(Cancel As Integer)

If 1 <> 1 And txt1 <> 2 And txt1 <> 3 And txt1 <> 4 And txt1 <> 5 And txt1 <> 9 Then
Cancel = True
MsgBox "Only 1, 2, 3, 4, 5, and 9 are valid codes"
Ctl1.SetFocus
Else
End If
DoCmd.GoToControl Ctl2
End Sub

The two issues I can't figure out are:
1) Why it tells me I need to save the data before I can move to a different field

2) Why it doesn't automatically move me to a different field if the validation passes.

Help is appricated. Thanks!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PHV]

Why testing txt1 in a Ctl1's event ?

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

 

[PaulBricker]

First, get rid of the line Ct11.SetFocus. The cancel = True keeps the focus on the current control. Try that.


Paul

 

[Lunatic]

Okay, I reverted back to 1 instead of txt1 (I forgot to change that back as I was trying other things to make it work).

I also removed Ctl1.setfocus

The validation works, in that if I try to change fields it won't let me because the validation hasn't been met.

However if the validation is met, it doesn't automatically take me to the next field. Instead if I try to manually change the field I recieve:

Run-time error '2108':

You must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus method.

I've tried adding the DoCmd.Save right before DoCmd.GoToControl but I can't seem to get it save and go to the next field.

Thanks for putting up with me.

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PaulBricker]

You could always just take out the DoCmd.GoToControl line, and let the tab order handle moving to the next control. Once the validation code is satisfied, the focus should automatically take you to the next control. That is my preference, but if for some reason that's not possible, then I need to find out a few things about the form. First, does it have a RecordSource. You can't use the Save command on an unbound control or form. Also, if you have validation rules for other controls, the record won't save if they are null. Give me a bit of information about the form and we'll see what we can figure out.

Paul

 

[Lunatic]

Okay, I removed the DoCmd.GoToControl and it the error no longer appears when I try to change fields. However the focus still does not move to the second field, even as it is the next tab-stop.

At this point, validation works perfectly (thank you!) but user must still enter/tab/click in next field.

Database info
1) Table with columns named 1 through 30 plus a column PK, Experience Text and Experience Code.
2) Field names I want to move between are 1 through 30
3) RecordSource is tbl_Data (table in #1 - it was selected at during the initial form design phase)
4) Default value for each column in each record is null with the exception of the auto-number column used for PK/Unique identifer purposes

****

With the current code:

Private Sub Ctl1_BeforeUpdate(Cancel As Integer)

If Ctl1 <> 1 And Ctl1 <> 2 And Ctl1 <> 3 And Ctl1 <> 4 And Ctl1 <> 5 And Ctl1 <> 9 Then
Cancel = True
MsgBox "Only 1, 2, 3, 4, 5, and 9 are valid codes"
Else
End If

End Sub

What part is supposed to automatically move focus to Ctl2?

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PaulBricker]

As soon as the code validates the value in the control, the focus should move to the next control automatically. Open the form in design view. Double click on Ctl1 which should open the properties box. On the All tab or the Other tab, you should find properties for Tab Stop and Tab Index. The tab stop should be set to Yes (for all the controls), and the tab index should be set to 0 if it's the first control, 1 if it's the second 2 if it's the third and so on. When you hit the tab button on your keypad, the focus should move from control to control. Does this happen? Let me know.

Paul

 

[Lunatic]

Yes, the tabstops are set up correctly. Tabstop 0 is a combo box which works fine and I've used a macro to send the user forward on an 'ondirty'. All other stops are entry fields created from dragging the column over from the field list, except the last tab stop which is a create new record command button.

Maybe its a version thing. I'm running Access 2002 with SP3 - might this be a feature introduced later or could there be a specific Reference that should be enabled?

Paul, I really appriciate your help on this.



***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PHV]

I don't understand why you use a macro goto ????

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

 

[Lunatic]

Because its quick and simple. And it works very well when there is no need for validation (because the user is restricted to the options in the combo box). That is the only macro I made left in the form.

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PHV]

Provided the TabStop and TabIndex properties are correctly defined I don't see any reason to use such goto macro.
What am I missing ?

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

 

[Lunatic]

Question: Everyone seems to think that as soon as data is entered in a field with a BeforeUpdate validation check that if the validation check passes the focus will go to the next field. Should this occur?

The reason I ask is I've never seen behavior like this. In my expereince, unless you tell it, the default is to stay in the same field until the user tab/enters/clicks to the next field.

It seems like there is something basic that is missing.

***
Tab stop design - one a blank form, if you start with the first field (a combo-box drop down) and hit 'tab' you will travel to each of the 28 other fields (2 are disabled-read only) and then finally the 'create new record' command button. The tab sequence appears correct.

The BeforeUpdate code listed above doesn't trigger any responses and seems to function well.

However if a valid number (1-5 or 9) is entered, the focus doesn't shift from one field to the next.

Again, running Access 2002 SP3... Is this functionality not implimented until a newer version of Access?

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PaulBricker]

It's not likely a version problem or a reference problem. Do all the fields have validation set up on them? That could be part of the problem. The main question in my head is why we need anything to move between controls on the form when normally just hitting the tab button will do that.

Tabstop 0 is a combo box which works fine and I've used a macro to send the user forward on an 'ondirty'.

Does this mean that the user goes to the combo and makes a selection? What happens when they click on their selection? Does the dirty event fire and a macro moves them to a new control? If that's the case, I'm not sure why we need a macro to do what should happen naturally.

It is a bit confusing. I'm sure I'm missing something here, I just can't quite put my finger on it.
Give me as much info as you can, and we'll go from there.


Paul

 

[PaulBricker]

The answer to your first question ("Should it occur"). Yes it should. As soon as the control value has been validated, it should move to the next control.

Paul

 

[Lunatic]

1) I was told to design it so the user doesn't have to hit 'tab'. Heck, no VBA or macros needed if the user is required to hit tab. All that would be needed is the property box's validation. But the powers that be want the user to be able to cycle though the form faster.

2) No, not all of the field have the code you have shown me how to use. But the fields I'm testing do.

3) For whatever reason, the 'natural' event that both of you say should happen doesn't. Any ideas on why?

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PHV]

the user doesn't have to hit 'tab'
And how is the BeforeUpdate event procedure triggered ?

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

 

[Lunatic]

the user doesn't have to hit 'tab'
And how is the BeforeUpdate event procedure triggered ?

Okay, I'm beginning to see why I'm becoming so confused. So the check only runs when another action triggers it on the BeforeUpdate trigger.

My instructions were to design it so the user doesn't need to hit enter or tab or anything like that.

Thats why I focused on the OnDirty trigger. All codes are a single digit so it seemed that the OnDirty trigger would be a logical choice.

***
So the good news, with my increased understanding, the code works exactly like it is supposed to.

The new question... Can a similar validation check be implimented with the Dirty trigger? Or some other way to trigger the verification with a manual instigator like pressing 'tab'?

I've tried the code with Dirty and Change. Dirty doesn't run the verification check, Change returns an error message.

If this isn't possible, at this point, I'm more than happy to hear it

Thank you both for helping!

***************************************
Have a problem with my spelling or grammar? Please refer all complaints to my English teacher:
Ralphy "Me fail English? That's unpossible." Wiggum

 

[PaulBricker]

I think you are not going to accomplish what you want using either of those event procedures. Dirty and Change won't work because they don't detect the value typed in the textbox when it happens. They see the value as "Null" so no validation will take place.
I don't see any way around using the tab/enter keys to move around the form.

Paul