Problem with error routines

  • Thread starter Thread starter David Smith
  • Start date Start date
D

David Smith

Hi All,

Presently I am using Ms-Access and designing a form using simple combo box

control by setting LimitToList property to Yes. and I added a event

procedure on NotInList to display a Proper Message to the user. When I run

the form and enter some dummy text, it displays the message that I wrote in

NotInList event of combo box along with the standard Ms-Access Message

which I don't want to display. Please tell me how should i do it.
 
David Smith said:
Hi All,

Presently I am using Ms-Access and designing a form using simple combo box

control by setting LimitToList property to Yes. and I added a event

procedure on NotInList to display a Proper Message to the user. When I run

the form and enter some dummy text, it displays the message that I wrote in

NotInList event of combo box along with the standard Ms-Access Message

which I don't want to display. Please tell me how should i do it.
Click to expand...

DoCmd.Setwarnings False to turn then off.
Be sure to turn them back on when done.
 
David,

Since you didn't supply the code that you are using, it is
difficult to tell you how to modify it. Here is a nice
example from MVP Wayne Morgan that is a good system for
handling not in list errors. His method will actually enter
it into the lookup table if you like, but you can lose that
part if you
don't want it and just substitute you message.

' *******
1) Limit to List property of combo box set to Yes
2) Use Not In List event of combo box to add the item.

Example:
Private Sub cboNamePrefix_NotInList(NewData As String,
Response As Integer)

On Error GoTo CheckError

Dim msg As String, ctl As Control, Reply As Integer
Dim db As DAO.Database, rst As DAO.Recordset

Set ctl = Me!cboNamePrefix

msg = "You have entered a value that doesn't exist." &
vbCrLf & "Do you want
to add it?"
Reply = MsgBox(msg, vbYesNo + vbQuestion, "Not in List")
If Reply = vbYes Then
Response = acDataErrAdded
Set db = CurrentDb
Set rst = db.OpenRecordset("tblNamePrefix")
With rst
.AddNew
![Name Prefix] = NewData 'Add data.
.Update 'Save changes.
.Close
End With
Else
Response = acDataErrContinue
ctl.Undo
End If

CleanUp:
On Error Resume Next
rst.Close
Set rst = Nothing
Set ctl = Nothing
Set db = Nothing
Exit Sub

CheckError:
msg = "Error # " & Str(Err.Number) & " was generated by " _
& Err.Source & Chr(13) & Err.Description
MsgBox msg, vbOKOnly + vbExclamation, "Error", Err.HelpFile,
Err.HelpContext
GoTo CleanUp

End Sub

' ********
--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Hi David,

you have to set a value to Response:

Response = acDataErrAdded

[]
Luiz Cláudio C. V. Rocha
São Paulo - Brazil
 
Mike Painter said:
DoCmd.Setwarnings False to turn then off.
Be sure to turn them back on when done.
Click to expand...

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise wierd
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top