Replace/Suppress Access Error msgbox

[Hilary Ostrov]

I have a txtfield [SiteID] that is a required field - indexed (No
Duplicates)

The following code (which I found courtesy of John someone's post of
2002) does work, but only if I set Required = No.

Private Sub SiteID_BeforeUpdate(Cancel As Integer)

'If user enters SiteID that is a duplicate

Cancel = (DCount("*", "Jobsites", "[SiteID]=""" & Me.SiteID.Text &
"""") > 0)
If Cancel = True Then

MsgBox "This SiteID already exists. Please choose another one",
vbOKOnly

End If
End Sub

However, if I set Required = Yes, then this code still works, but is
invariably followed by a considerably less than user-friendly MS
Access error msg.

What do I need to do to get rid of the MS Access message?

TIA for any suggestions.


hro

 

[John Vinson]

The following code (which I found courtesy of John someone's post of
2002) does work, but only if I set Required = No.

I'd make a couple of changes to it: try

Private Sub SiteID_BeforeUpdate(Cancel As Integer)

'If user enters SiteID that is a duplicate
If DCount("*", "Jobsites", "[SiteID]=""" & Me.SiteID & """") > 0 Then
Cancel = True
MsgBox "This SiteID already exists. Please choose another one",
vbOKOnly
Me.SiteID.Undo
End If
End Sub


Not sure it's necessary to count all one of the ID's - you might be
able to use

If Not IsNull(DLookUp("[SiteID]", "Jobsites", _
"[SiteID]=""" & Me.SiteID & """") Then

Not sure which would be faster!

John W. Vinson[MVP]

 

[Hilary Ostrov]

On Thu, 23 Nov 2006 21:03:53 -0700, in
<[email protected]>, John Vinson

Many thanks for your fast response, John - I was hoping this would put
me out of my daylong misery. But ...

The following code (which I found courtesy of John someone's post of
2002) does work, but only if I set Required = No.

I'd make a couple of changes to it: try

Private Sub SiteID_BeforeUpdate(Cancel As Integer)

'If user enters SiteID that is a duplicate
If DCount("*", "Jobsites", "[SiteID]=""" & Me.SiteID & """") > 0 Then
Cancel = True
MsgBox "This SiteID already exists. Please choose another one",
vbOKOnly
Me.SiteID.Undo
End If
End Sub

.... (if Required = Yes)

1. Undo doesn't (so to speak, and string gets converted to Uppercase,
because that's what I've set as the format)

2. Focus moves to next field and...

3. I still get:

<quote>
The value in the field or record violates the validation rule for the
record or field.

For example, you may have changed a validation rule without verifying
whether the existing data matches the new validation rule.

Click Undo on the Edit menu to restore the previous value, or enter a
new value that meets the validation rule for the field.
</quote>

[Albeit this is an "example", second paragraph makes little sense to
me, because my experience has been that Access won't let one change a
validation rule if existing data doesn't conform!]

Not sure it's necessary to count all one of the ID's - you might be
able to use

If Not IsNull(DLookUp("[SiteID]", "Jobsites", _
"[SiteID]=""" & Me.SiteID & """") Then

Not sure which would be faster!

This doesn't work quite as well - even when I add second closing ) -
I still get same result as above if Required =Yes

[Strangely enough, it doesn't work when Required = No either (while
the DCount does work, as does the Undo, if Required = No]

My plan B was to set Required=No and then make sure before saving the
record that SiteID does contain data; however, I'm not sure how I
might accomplish this. Can't remember at this point what I've tried
so far, [I think I was using Form_BeforeUpdate] but I do know that
nothing I tried worked

(If you're wondering why I'm going to all this trouble, btw ... user
was playing havoc with Site ID's ... he would change original site ID
so that he could use it for a new jobsite ... fixed that by locking
field ... but I've learned that he invariably tries to take
"shortcuts" when MS Access msgs won't let him do what he wants to -
and as soon as he discovers that by mousing around he can save record
without SiteID, he'll probably do so. And then when stuff doesn't
work, and I tell him why he wants me to make it "foolproof" ... so I
gotta stop him in his tracks!)
hro

 

[John Vinson]

1. Undo doesn't (so to speak, and string gets converted to Uppercase,
because that's what I've set as the format)

2. Focus moves to next field and...

3. I still get:

<quote>
The value in the field or record violates the validation rule for the
record or field.

There's something REALLY wrong here.

If you have the Cancel operand set to True in the BeforeUpdate event
of a textbox, that textbox's value will not be saved, nor will Access
even try to save it.

If you Undo the textbox, it should restore it to the value prior to
the user dirtying the record (probably NULL unless it's an existing
record).

Could you please post the Recordsource query of the form; the Control
Source of the textbox; and the actual code that you're using? Do you
have any Lookup fields in the table? Have you turned off Name
Autocorrupt... ummm... Name Autocorrect in the database's properties
(if not, do so)?

John W. Vinson[MVP]

 

[Hilary Ostrov]

On Fri, 24 Nov 2006 11:16:12 -0700, in
<[email protected]>, John Vinson

Thanks again, John, for helping me out here!

[...]

There's something REALLY wrong here.

That's kinda what I thought, too

If you have the Cancel operand set to True in the BeforeUpdate event
of a textbox, that textbox's value will not be saved, nor will Access
even try to save it.

If you Undo the textbox, it should restore it to the value prior to
the user dirtying the record (probably NULL unless it's an existing
record).

And it did behave as expected if Required=No (This is an Add record
form, so no existing record)

Could you please post the Recordsource query of the form;

SELECT Jobsites.SiteID, Jobsites.[Managed By], Jobsites.[Site Name],
Jobsites.Owner, Jobsites.[Site Contact], Jobsites.[Billing Contact],
Jobsites.[Site Address], Jobsites.[Other Site Addresses],
Jobsites.City, Jobsites.PostalCode, Jobsites.Phone, Jobsites.[Phone2
or Cell], Jobsites.Pager, Jobsites.Fax, Jobsites.Notes,
Jobsites.Inactive
FROM Jobsites;

the Control
Source of the textbox;

Control Source = SiteID
Enabled = Yes
Locked = No
Filter Lookup = Database Default

and the actual code that you're using?

Private Sub SiteID_BeforeUpdate(Cancel As Integer)
'If SiteID is duplicate

If DCount("*", "Jobsites", "[SiteID]=""" & Me.SiteID & """") > 0 Then
Cancel = True
MsgBox "This SiteID already exists. Please choose another one",
vbOKOnly
Me.SiteID.Undo
End If

End Sub

Do you
have any Lookup fields in the table?

Nope. Learned that lesson in my early days

Have you turned off Name
Autocorrupt... ummm... Name Autocorrect in the database's properties
(if not, do so)?

Ahhhh! Didn't realize this was turned on. Turning it off got me as
far as eliminating the MS error msg, and focus returns to SiteID
txtbox with duplicate entry text selected.

But it didn't Undo, and if I tab out of field again without
changing/re-entering, my msgbox doesn't reappear; however if I enter
data in other required fields and continue it won't save record until
I change SiteID - but no further error msgbox appears!

This does serve my purpose, quite well, but I doubt that it's supposed
to behave this way I did a "compact and repair", but behaviour is
still the same.

FWIW, this .mdb was originally created in Access 2000, but I've
imported it into 2003 (in 2000 File Format)


hro

 

[John Vinson]

Glad that it was solvable - yet another score against Name
Autocorrect!!!

But it didn't Undo, and if I tab out of field again without
changing/re-entering, my msgbox doesn't reappear; however if I enter
data in other required fields and continue it won't save record until
I change SiteID - but no further error msgbox appears!

About all I can suggest at this point is that Access might be
confusing the fieldname with the controlname. You might try changing
the name of the textbox bound to SiteID to (say) txtSiteID, and using
Me.txtSiteID.Undo to unambiguously reference the form control.

John W. Vinson[MVP]

 

[Hilary Ostrov]

Glad that it was solvable - yet another score against Name
Autocorrect!!!


About all I can suggest at this point is that Access might be
confusing the fieldname with the controlname. You might try changing
the name of the textbox bound to SiteID to (say) txtSiteID, and using
Me.txtSiteID.Undo to unambiguously reference the form control.

Well, I made the change you suggested, but that just brought back the
MS error msg after my msgbox (( So I reverted back to previous, and
now I can't get rid of MS error msg, again (. I did make a change
on another form (all I did was change a logo image!!), but I can't see
how that would have affected this form!

But thanks for your help ... I'm now going out to drown my sorrows!

hro

 

[John Vinson]

MS error msg after my msgbox (( So I reverted back to previous, and
now I can't get rid of MS error msg, again (. I did make a change
on another form (all I did was change a logo image!!), but I can't see
how that would have affected this form!

When you get back (and sleep it off if your drowning is severe <g>)
post the change you made and the error message. Changing the control
name will require that you also change all references to it!

John W. Vinson[MVP]

 

[Hilary Ostrov]

On Fri, 24 Nov 2006 21:06:54 -0700, in
<[email protected]>, John Vinson

[...]

When you get back (and sleep it off if your drowning is severe <g>)
post the change you made and the error message. Changing the control
name will require that you also change all references to it!

Drowning didn't help ... and this is keeping me awake<g>

Here's the code of the change:

Private Sub txtSiteID_BeforeUpdate(Cancel As Integer)
'If SiteID is duplicate

If DCount("*", "Jobsites", "[SiteID]=""" & Me.txtSiteID & """") > 0
Then
Cancel = True
MsgBox "This SiteID already exists. Please choose another one",
vbOKOnly
Me.txtSiteID.Undo
End If
End Sub

Error message is the same as I was getting previously:

<quote>
The value in the field or record violates the validation rule for the
record or field.

For example, you may have changed a validation rule without verifying
whether the existing data matches the new validation rule.

Click Undo on the Edit menu to restore the previous value, or enter a
new value that meets the validation rule for the field.
</quote>

About the only good news I can report is that at least after the above
msg, even though Undo doesn't, focus is on SiteID (unlike earlier
aberrations when it was on next field!)

I've even tried running it on another machine, creating a new mdb and
importing required table query and form ... and still get the same
result

Any thoughts on how I might get my original Plan B to work?! i.e.
setting Required=No and making sure that SiteID contains data before
record is saved.

Many thanks for persevering on this with me!


hro