Data Check not checking

[Guest]

I have set up code in my forms to make sure certain fields are filled in
before the user exits because they are mandatory for the record to save. All
of them work except one, the Grant_Type field doesn't check. When I exit,
the record doesn't save because it has no grant_type, but the check doesn't
prompt.

Private Sub Command82_Click()
' This closes the current form and opens the Main form

' First make sure all necessary values are entered
If IsNull(Me.Grant_Type) Then GoTo NoType
If IsNull(Me.Grant_Dept) Then GoTo NoDept
If IsNull(Me.Fund_Amt) Then GoTo NoAmt
If IsNull(Me.Applicant) Then GoTo NoApplicant
If IsNull(Me.County) Then GoTo NoCounty
If IsNull(Me.Project_Name) Then GoTo NoName

' If so, turn the mouse into an hourglass
DoCmd.Hourglass True
' Don't show screen changes
DoCmd.Echo False, ""
' Save the new record
RunCommand acCmdSaveRecord
' The New Projects screen closes
DoCmd.Close acForm, "FrmProjEntry_HB267"
' The Main Form opens
DoCmd.OpenForm "FrmMain", acNormal
DoCmd.Maximize
' Show the screen changes again
DoCmd.Echo True, ""
' Turn the mouse back into a cursor
DoCmd.Hourglass False

ExitNice:
Exit Sub

McrFormNavigation_CloseAndSaveNew_Err:
MsgBox Error$
MsgBox "There was an error...", vbOKCancel, Whoops
GoTo ExitNice

NoCounty:
MsgBox "Please Enter a County", vbOKOnly
Cancel = True
GoTo ExitNice

NoType:
MsgBox "Please Enter a Fund Type", vbOKOnly
Cancel = True
GoTo ExitNice

NoDept:
MsgBox "Please Enter a Grant Department", vbOKOnly
Cancel = True
GoTo ExitNice

NoAmt:
MsgBox "Please Enter a Fund Amount", vbOKOnly
Cancel = True
GoTo ExitNice

NoApplicant:
MsgBox "Please Enter an Applicant", vbOKOnly
Cancel = True
GoTo ExitNice

NoName:
MsgBox "Please Enter a Project Name/Title", vbOKOnly
Cancel = True
GoTo ExitNice

End Sub

 

[ruralguy via AccessMonster.com]

You might try:
If Len(Me.Grant_Type & "") = 0 Then GoTo NoType
Which checks for Nulls AND ZeroLengthStrings (ZLS)

You should really study some code that does *not* use GoTo's.
The only GoTo normally used is the On Error GoTo... phrase.

 

[Guest]

That did not resolve the problem, I have the same problem on another form
with the field [Applicant], which works fine on this form (Grant_Type works
fine on the other form).

 

[ruralguy via AccessMonster.com]

Hi Todd,
It sounds like you may be data sensitive. Try:
If Len(Me.Grant_Type & "") = 0 Then
GoTo NoType
Else
MsgBox "Length of Grant_Type is [" & Len(Me.Grant_Type & "") & "]" &
vbCrLf & _
"...and contains [" & Me.Grant_Type & "]"
End If

That did not resolve the problem, I have the same problem on another form
with the field [Applicant], which works fine on this form (Grant_Type works
fine on the other form).

You might try:
If Len(Me.Grant_Type & "") = 0 Then GoTo NoType

[quoted text clipped - 75 lines]

 

[Guest]

Wow. When I entered the code below, it recognized the missing Grant_Type,
but after entering the grant type and trying to exit, I go the following
errors in succession:

"Row handle referred to a deleted row or a row marked for deletion"

"Another user or application has deleted this record or changed the value of
its primary key"

"Multiple-step operation generated errors. Check each status value"

"Key value for this row was changed or deleted at the data store. The local
row is now deleted."

And suddenly "#Deleted" started showing up in all of my fields.

Hi Todd,
It sounds like you may be data sensitive. Try:
If Len(Me.Grant_Type & "") = 0 Then
GoTo NoType
Else
MsgBox "Length of Grant_Type is [" & Len(Me.Grant_Type & "") & "]" &
vbCrLf & _
"...and contains [" & Me.Grant_Type & "]"
End If

That did not resolve the problem, I have the same problem on another form
with the field [Applicant], which works fine on this form (Grant_Type works
fine on the other form).

You might try:
If Len(Me.Grant_Type & "") = 0 Then GoTo NoType

[quoted text clipped - 75 lines]

End Sub

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Something else is going on! Have you tried importing all of this mdb into a
new empty mdb that has Name AutoCorrect turned off?

Wow. When I entered the code below, it recognized the missing Grant_Type,
but after entering the grant type and trying to exit, I go the following
errors in succession:

"Row handle referred to a deleted row or a row marked for deletion"

"Another user or application has deleted this record or changed the value of
its primary key"

"Multiple-step operation generated errors. Check each status value"

"Key value for this row was changed or deleted at the data store. The local
row is now deleted."

And suddenly "#Deleted" started showing up in all of my fields.

Hi Todd,
It sounds like you may be data sensitive. Try:

[quoted text clipped - 15 lines]

 

[ruralguy via AccessMonster.com]

It only takes a few seconds and can cure a number of possible problems.

Something else is going on! Have you tried importing all of this mdb into a
new empty mdb that has Name AutoCorrect turned off?

Wow. When I entered the code below, it recognized the missing Grant_Type,
but after entering the grant type and trying to exit, I go the following

[quoted text clipped - 17 lines]

 

[Guest]

You just lost me on the "Name Autocorrect". I should probably clarify a few
things:
1) This is an Access front end with a SQL back end
2) This form is set up as a record maintenance form, with some special
coding to change it to data entry when the "new record" button is pushed
(DoCmd.GoToRecord acDataForm, Me.Name, acNewRec)
3) There are subforms set up in this form (I think that is what part of the
problem is)

Something else is going on! Have you tried importing all of this mdb into a
new empty mdb that has Name AutoCorrect turned off?

Wow. When I entered the code below, it recognized the missing Grant_Type,
but after entering the grant type and trying to exit, I go the following
errors in succession:

"Row handle referred to a deleted row or a row marked for deletion"

"Another user or application has deleted this record or changed the value of
its primary key"

"Multiple-step operation generated errors. Check each status value"

"Key value for this row was changed or deleted at the data store. The local
row is now deleted."

And suddenly "#Deleted" started showing up in all of my fields.

Hi Todd,
It sounds like you may be data sensitive. Try:

[quoted text clipped - 15 lines]

End Sub

 

[ruralguy via AccessMonster.com]

Here's a link to explain the Name AutoCorrect issue.

You are probably correct about the SubForms giving you some problems but I
don't know that I can guess well enough to figure out what is going on from
here.

I would still do the New Empty mdb with Name AutoCorrect turned off and
import everything from the old FrontEnd. It might solve some issues and it
only takes a few seconds.

You just lost me on the "Name Autocorrect". I should probably clarify a few
things:
1) This is an Access front end with a SQL back end
2) This form is set up as a record maintenance form, with some special
coding to change it to data entry when the "new record" button is pushed
(DoCmd.GoToRecord acDataForm, Me.Name, acNewRec)
3) There are subforms set up in this form (I think that is what part of the
problem is)

Something else is going on! Have you tried importing all of this mdb into a
new empty mdb that has Name AutoCorrect turned off?

[quoted text clipped - 20 lines]

 

[ruralguy via AccessMonster.com]

I guess it would help if I actually include the link!
http://allenbrowne.com/bug-03.html

Here's a link to explain the Name AutoCorrect issue.

You are probably correct about the SubForms giving you some problems but I
don't know that I can guess well enough to figure out what is going on from
here.

I would still do the New Empty mdb with Name AutoCorrect turned off and
import everything from the old FrontEnd. It might solve some issues and it
only takes a few seconds.

You just lost me on the "Name Autocorrect". I should probably clarify a few
things:

[quoted text clipped - 10 lines]

 

[Guest]

In Access Projects, there are no "Name Autocorrect" options under
Tools>Options>General.

Here's a link to explain the Name AutoCorrect issue.

You are probably correct about the SubForms giving you some problems but I
don't know that I can guess well enough to figure out what is going on from
here.

I would still do the New Empty mdb with Name AutoCorrect turned off and
import everything from the old FrontEnd. It might solve some issues and it
only takes a few seconds.

You just lost me on the "Name Autocorrect". I should probably clarify a few
things:
1) This is an Access front end with a SQL back end
2) This form is set up as a record maintenance form, with some special
coding to change it to data entry when the "new record" button is pushed
(DoCmd.GoToRecord acDataForm, Me.Name, acNewRec)
3) There are subforms set up in this form (I think that is what part of the
problem is)

Something else is going on! Have you tried importing all of this mdb into a
new empty mdb that has Name AutoCorrect turned off?

[quoted text clipped - 20 lines]

End Sub

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

So it is an ADP? What version of Access are you using. I know *very* little
about ADP's and I believe they are being phased out or at least diminished.
I'm pretty sure you can do everything you need to with an MDB.

At any rate, maybe someone with ADP experience will drop by and offer some
assistance. As you discovered, my suggestions were for an MDB. I'll stay
tuned but you may want to start a new thread with ADP in the subject line to
attract some additional attention.

In Access Projects, there are no "Name Autocorrect" options under
Tools>Options>General.

Here's a link to explain the Name AutoCorrect issue.

[quoted text clipped - 20 lines]

 

[Guest]

Access 2003. The SQL server is currently on my computer, it will be
relocated to a real server once building is complete.

So it is an ADP? What version of Access are you using. I know *very* little
about ADP's and I believe they are being phased out or at least diminished.
I'm pretty sure you can do everything you need to with an MDB.

At any rate, maybe someone with ADP experience will drop by and offer some
assistance. As you discovered, my suggestions were for an MDB. I'll stay
tuned but you may want to start a new thread with ADP in the subject line to
attract some additional attention.

In Access Projects, there are no "Name Autocorrect" options under
Tools>Options>General.

Here's a link to explain the Name AutoCorrect issue.

[quoted text clipped - 20 lines]

End Sub

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com

 

[ruralguy via AccessMonster.com]

Thanks Todd and good luck with the problem.

Access 2003. The SQL server is currently on my computer, it will be
relocated to a real server once building is complete.

So it is an ADP? What version of Access are you using. I know *very* little
about ADP's and I believe they are being phased out or at least diminished.

[quoted text clipped - 13 lines]

 

[George Nicholson]

When I exit,

the record doesn't save because it has no grant_type, but the check doesn't
prompt. ......
Private Sub Command82_Click() ........
NoType:
MsgBox "Please Enter a Fund Type", vbOKOnly
Cancel = True
GoTo ExitNice

** WHY ISN"T IT SAVING??** It isn't because of any of the code shown. If the
prompt wasn't working then NoType wasn't getting called so acCmdSaveRecord
*was* being called. (BTW, what does "Cancel=True" do? The Click event has
no Cancel argument? Do you have OptionExplicit at the top of the code
module?). I assume that an automatic attempt to save the record was made
(unless you canceled it somewhere else). That attempt failed. Why? I assume
because of constraints in SQLs (since the constraints here aren't working
correctly afaik).

Then

but after entering the grant type and trying to exit, I go the following
errors in succession:

"Row handle referred to a deleted row or a row marked for deletion"

"Another user or application has deleted this record or changed the value
of
its primary key"

"Multiple-step operation generated errors. Check each status value"

"Key value for this row was changed or deleted at the data store. The
local
row is now deleted."

And suddenly "#Deleted" started showing up in all of my fields.

OK, here's my two cents (a *lot* of guesswork follows). Normally, data
validation occurs in the BeforeUpdate event of a form. This event can be
canceled and prevent the record from *trying* to save the record in an mdb.

Now that you've fixed the NoType problem, I'm thinking that your problem is
that since you aren't using & cancelling that event, Access then tries to
save the record and fails (because of constraints in SQL). (This Save is
being triggered by something *other* than your explicit acCmdSaveRecord. Not
sure what.)
Then, when you "fix" the record, something (the error messages sound more
like SQLsrv than Access...) can't reconcile the new attempt to save with the
previous failure.

I'm a very fuzzy on the mechanics involved (sorry), but it almost sounds
like a record is being inserted by SQLsrv, but not commited. When the
attempt to commit the inserted record fails, SQLsrv deletes the record
(cancels the insertion). When you try to commit a 2nd time, the inserted
record no longer exists (as indicated by #Deleted#).

My suggestion is to move your validation to the BeforeUpdate event of the
Form or find some other method to intercept and cancel/prevent the initial
save that fails, then your 2nd attempt to commit will suceed.

HTH,

Access 2003. The SQL server is currently on my computer, it will be
relocated to a real server once building is complete.

So it is an ADP? What version of Access are you using. I know *very*
little
about ADP's and I believe they are being phased out or at least
diminished.
I'm pretty sure you can do everything you need to with an MDB.

At any rate, maybe someone with ADP experience will drop by and offer
some
assistance. As you discovered, my suggestions were for an MDB. I'll
stay
tuned but you may want to start a new thread with ADP in the subject line
to
attract some additional attention.

In Access Projects, there are no "Name Autocorrect" options under
Tools>Options>General.

Here's a link to explain the Name AutoCorrect issue.

[quoted text clipped - 20 lines]

End Sub

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com