Field validation does not kick in

B

BP

Hello,

I have a field(Date type set in table) in a form based on a query, when a
non-date entry is entered, it automatically prompts a popup warning to state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested by one of
the advisors here to prevent multi-user problems), the prompt does not pop up
when I enter a non-date entry into the date field and also does not save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats wrong with
the code below that it affects the normal operation of the field validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
 
D

Dale Fye

BP,

The code you currently have is basically ignoring all errors other than the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write code like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes an
error. In the first case, you generate the new [Job No] value. In the
second, you cancel the update, set the focus to the control where the offense
occurred, and display a message advising the user of what they need to do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.
 
B

BP

THanks!

Dale Fye said:
BP,

The code you currently have is basically ignoring all errors other than the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write code like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes an
error. In the first case, you generate the new [Job No] value. In the
second, you cancel the update, set the focus to the control where the offense
occurred, and display a message advising the user of what they need to do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



BP said:
Hello,

I have a field(Date type set in table) in a form based on a query, when a
non-date entry is entered, it automatically prompts a popup warning to state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested by one of
the advisors here to prevent multi-user problems), the prompt does not pop up
when I enter a non-date entry into the date field and also does not save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats wrong with
the code below that it affects the normal operation of the field validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
Click to expand...
Click to expand...
 
B

BP

btw, can i just use
IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1


Dale Fye said:
BP,

The code you currently have is basically ignoring all errors other than the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write code like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes an
error. In the first case, you generate the new [Job No] value. In the
second, you cancel the update, set the focus to the control where the offense
occurred, and display a message advising the user of what they need to do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



BP said:
Hello,

I have a field(Date type set in table) in a form based on a query, when a
non-date entry is entered, it automatically prompts a popup warning to state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested by one of
the advisors here to prevent multi-user problems), the prompt does not pop up
when I enter a non-date entry into the date field and also does not save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats wrong with
the code below that it affects the normal operation of the field validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
Click to expand...
Click to expand...
 
B

BP

pls ignore the previous post. accidentally clicked post.

Can I just use the below 2 lines only ?
IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1

I only just require to check and increment the [job no] if there is another
user at another terminal created a same job number but updates his form first.

for my case do I need the rest?
Elseif Len(me.txtSomeOtherField & "") = 0 then
cancel = true ....

Dale Fye said:
BP,

The code you currently have is basically ignoring all errors other than the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write code like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes an
error. In the first case, you generate the new [Job No] value. In the
second, you cancel the update, set the focus to the control where the offense
occurred, and display a message advising the user of what they need to do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



BP said:
Hello,

I have a field(Date type set in table) in a form based on a query, when a
non-date entry is entered, it automatically prompts a popup warning to state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested by one of
the advisors here to prevent multi-user problems), the prompt does not pop up
when I enter a non-date entry into the date field and also does not save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats wrong with
the code below that it affects the normal operation of the field validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
Click to expand...
Click to expand...
 
D

Dale Fye

Probably not.

I showed that because I frequently use the BeforeUpdate event to check other
fields for valid information as well. This is where I generally do that, to
ensure that I the user has entered the data that is absolutely required.

For example, you might have the Job_Description in your underlying table set
up with properties Required = Yes, AllowZeroLength = No. With these
settings, if you try to save the record without entering this data, you will
get Microsoft's default message. But if you trap for that error in the
BeforeUpdate event (by checking to see whether the textbox contains text)
then you can display your own message, Cancel the update, and set the focus
back to the control you want to get the focus.
--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



BP said:
pls ignore the previous post. accidentally clicked post.

Can I just use the below 2 lines only ?
IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1

I only just require to check and increment the [job no] if there is another
user at another terminal created a same job number but updates his form first.

for my case do I need the rest?
Elseif Len(me.txtSomeOtherField & "") = 0 then
cancel = true ....

Dale Fye said:
BP,

The code you currently have is basically ignoring all errors other than the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write code like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes an
error. In the first case, you generate the new [Job No] value. In the
second, you cancel the update, set the focus to the control where the offense
occurred, and display a message advising the user of what they need to do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



BP said:
Hello,

I have a field(Date type set in table) in a form based on a query, when a
non-date entry is entered, it automatically prompts a popup warning to state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested by one of
the advisors here to prevent multi-user problems), the prompt does not pop up
when I enter a non-date entry into the date field and also does not save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats wrong with
the code below that it affects the normal operation of the field validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

I don't think you need to check JobNo. In any case it won't do what it
seems you hope it will. Until the user saves the record (by attempting to
navigate to another record, for instance) the value is not written to the
table. Nothing user 2 has done in a new record will affect the length of
txtJobNo in a new record by user 1.

There is a sample database here that shows how to create an incremented
value in a multi-user environment.:
http://www.rogersaccesslibrary.com/...?TID=395&SID=17c4f1df36zz96c1c822bcb3ca37bbzd

Watch for line wrapping in the link. This is the main site. The sample is
called Autonumber Problem.
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

The sample uses the Default Value property of a text box, but note that you
can use DefaultValue in VBA as well. DefaultValue only applies to a new
record, so the value won't be changed when somebody revisits the record.

BP said:
pls ignore the previous post. accidentally clicked post.

Can I just use the below 2 lines only ?
IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1

I only just require to check and increment the [job no] if there is
another
user at another terminal created a same job number but updates his form
first.

for my case do I need the rest?
Elseif Len(me.txtSomeOtherField & "") = 0 then
cancel = true ....

Dale Fye said:
BP,

The code you currently have is basically ignoring all errors other than
the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry
errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write code
like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes an
error. In the first case, you generate the new [Job No] value. In the
second, you cancel the update, set the focus to the control where the
offense
occurred, and display a message advising the user of what they need to
do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



BP said:
Hello,

I have a field(Date type set in table) in a form based on a query, when
a
non-date entry is entered, it automatically prompts a popup warning to
state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested by
one of
the advisors here to prevent multi-user problems), the prompt does not
pop up
when I enter a non-date entry into the date field and also does not
save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats wrong
with
the code below that it affects the normal operation of the field
validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
Click to expand...
Click to expand...
Click to expand...
 
B

BP

Hi Bruce,

Yes thats where my original problem came from(if you read my first post).

I used the default value property for autonumber generation and the sample
code in from that sample data-base for multi-user application and it is
causing all other access default errors to be ignored. Hence my subject for
this thread is "field validation does not kick in".


BruceM said:
I don't think you need to check JobNo. In any case it won't do what it
seems you hope it will. Until the user saves the record (by attempting to
navigate to another record, for instance) the value is not written to the
table. Nothing user 2 has done in a new record will affect the length of
txtJobNo in a new record by user 1.

There is a sample database here that shows how to create an incremented
value in a multi-user environment.:
http://www.rogersaccesslibrary.com/...?TID=395&SID=17c4f1df36zz96c1c822bcb3ca37bbzd

Watch for line wrapping in the link. This is the main site. The sample is
called Autonumber Problem.
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

The sample uses the Default Value property of a text box, but note that you
can use DefaultValue in VBA as well. DefaultValue only applies to a new
record, so the value won't be changed when somebody revisits the record.

BP said:
pls ignore the previous post. accidentally clicked post.

Can I just use the below 2 lines only ?
IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1

I only just require to check and increment the [job no] if there is
another
user at another terminal created a same job number but updates his form
first.

for my case do I need the rest?
Elseif Len(me.txtSomeOtherField & "") = 0 then
cancel = true ....

Dale Fye said:
BP,

The code you currently have is basically ignoring all errors other than
the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry
errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write code
like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes an
error. In the first case, you generate the new [Job No] value. In the
second, you cancel the update, set the focus to the control where the
offense
occurred, and display a message advising the user of what they need to
do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hello,

I have a field(Date type set in table) in a form based on a query, when
a
non-date entry is entered, it automatically prompts a popup warning to
state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested by
one of
the advisors here to prevent multi-user problems), the prompt does not
pop up
when I enter a non-date entry into the date field and also does not
save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats wrong
with
the code below that it affects the normal operation of the field
validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
Click to expand...
Click to expand...
Click to expand...
 
B

BruceM

I did read your first post, and every other post in this thread. You did
not mention the default value property that I can see. DefaultValue and If
Me.NewRecord are different approaches, although they can be used accomplish
similar things. You said only that "it automatically prompts a popup
warning." That could mean several things, including table-level validation
and form-level validation.

You wondered about checking whether it is a new record, and at the same time
whether txtJobNo & "" has greater than 0 length. If JobNo is generated
automatically you can lock txtJobNo. That way it can only have length if it
is an existing record. For new records there will be nothing in the field.

I see now that you used the same duplicate number strategy as in the sample
database. As has been pointed out, any error other than 3022 will be
ignored by the Error event. You can avoid that by adding an Else in the
Error event:

If DataErr = 3022 Then
IncrementField(DataErr)
Else
' Something else
End If


BP said:
Hi Bruce,

Yes thats where my original problem came from(if you read my first post).

I used the default value property for autonumber generation and the sample
code in from that sample data-base for multi-user application and it is
causing all other access default errors to be ignored. Hence my subject
for
this thread is "field validation does not kick in".


BruceM said:
I don't think you need to check JobNo. In any case it won't do what it
seems you hope it will. Until the user saves the record (by attempting
to
navigate to another record, for instance) the value is not written to the
table. Nothing user 2 has done in a new record will affect the length of
txtJobNo in a new record by user 1.

There is a sample database here that shows how to create an incremented
value in a multi-user environment.:
http://www.rogersaccesslibrary.com/...?TID=395&SID=17c4f1df36zz96c1c822bcb3ca37bbzd

Watch for line wrapping in the link. This is the main site. The sample
is
called Autonumber Problem.
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

The sample uses the Default Value property of a text box, but note that
you
can use DefaultValue in VBA as well. DefaultValue only applies to a new
record, so the value won't be changed when somebody revisits the record.

BP said:
pls ignore the previous post. accidentally clicked post.

Can I just use the below 2 lines only ?
IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1

I only just require to check and increment the [job no] if there is
another
user at another terminal created a same job number but updates his form
first.

for my case do I need the rest?
Elseif Len(me.txtSomeOtherField & "") = 0 then
cancel = true ....

:

BP,

The code you currently have is basically ignoring all errors other
than
the
3022 error on the [Job No] field.

I generally use the forms BeforeUpdate event to trap for data entry
errors.
This event fires before the data is actually written, and has a Cancel
parameter that can be used to cancel the update. So you can write
code
like:

Private Sub Form_BeforeUpdate(Cancel as integer)

IF me.NewRecord AND len(me.txt_JobNo & "") = 0 then
me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
Elseif Len(me.txtSomeOtherField & "") = 0 then
Cancel = true
msgbox "Enter a value for SomeOtherField"
me.txtSomeOtherField.setfocus
endif

End sub

With this code, you are handling the error before it actually becomes
an
error. In the first case, you generate the new [Job No] value. In
the
second, you cancel the update, set the focus to the control where the
offense
occurred, and display a message advising the user of what they need to
do.

--
HTH
Dale

email address is invalid
Please reply to newsgroup only.



:

Hello,

I have a field(Date type set in table) in a form based on a query,
when
a
non-date entry is entered, it automatically prompts a popup warning
to
state
that it is not a valid entry.

After I add in some code in the Error event of the form (suggested
by
one of
the advisors here to prevent multi-user problems), the prompt does
not
pop up
when I enter a non-date entry into the date field and also does not
save the
entry as well and the focus is still at the date field.

I require the popup warning though, can someone pls look at whats
wrong
with
the code below that it affects the normal operation of the field
validation?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

On Error GoTo Err_Form_Error

Response = IncrementField(DataErr)

Exit_Form_Error:
Exit Sub

Err_Form_Error:
MsgBox Err.Description
Resume Exit_Form_Error

End Sub

Function IncrementField(DataErr)
If DataErr = 3022 Then
Me![Job No] = DMax("[Job No]", "[QT Job Record]") + 1
IncrementField = acDataErrContinue
End If

End Function


Thanks
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Autonumber (Using Dmax) on Continuous Forms 1
Autonumber for multiusers 0
Autonumber Workaround with SQL Server 2005 BE 0
Sequencial Number 0
Autonumber in multiuser environment 1
On error display 0
VB CODE 1
VBA Code skipping lines?? 0

Top