Results 1 to 7 of 7
  1. #1
    Mclaren is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164

    Turn warnings off

    I would like to suppress the following warning:

    "The text you entered is an item in the list
    Select an item from the list, or enter text that matches one of the items in the list"

    this comes up when i am pasting data from an excel spreadsheet into a data sheet in ms access.

    The field in MS access is set to "limit to list" so that no garbage data can be stored.

    In my code below i have created my own message box for the user to note the details of the failed paste, i only want my message to show.

    I also seem to get my message pop up twice, once with the ATMID and then immediatly afterwards with no ATMID in the message for each failed paste.

    my code is :
    Private Sub ATMID_BeforeUpdate(Cancel As Integer)
    DoCmd.SetWarnings False
    On Error GoTo AtmID_BeforeUpdate_Err


    Me.KnownGLCode = DLookup("BankGlCode", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)
    Me.Van = DLookup("CITVanName", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)

    AtmID_BeforeUpdate_Exit:
    Exit Sub

    AtmID_BeforeUpdate_Err:


    MsgBox "Atm : " & Me.ATMID & " : Does not exist", vbOKOnly, "Error is processing..."
    Me.Undo
    Resume Next


    End Sub
    I also seem to get my message pop up twice, once with the ATMID and then immediatly afterwards with no ATMID in the message for each failed paste.

  2. #2
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    To customize you error message in this case I think you should put your code in the On Not in List Event:
    e.g.

    Private Sub cboDepartment_NotInList(NewData As String,
    Response As Integer)
    MsgBox "Sorry, Invalid Entry." & vbCrLf & _
    "Please choose an item from the list." _
    , vbExclamation, "Spiders Infotech"
    Response = acDataErrContinue
    End Sub

    The line highlighted in Red Prevents the standard Access Error message to be displayed.
    if this solves your problem mark the thread solved.

  3. #3
    Mclaren is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    No luck, this is my adjusted code.

    I am still getting error messages, and also the ATMID does not show in the message, just a "0"

    Private Sub ATMID_BeforeUpdate(Cancel As Integer)
    DoCmd.SetWarnings False
    On Error GoTo AtmID_BeforeUpdate_Err

    Me.KnownGLCode = DLookup("BankGlCode", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)
    Me.Van = DLookup("CITVanName", "qry_Atms_Extended_Full", "[id] = " & Me.ATMID.Value)


    atmID_BeforeUpdate_Exit:
    Exit Sub

    AtmID_BeforeUpdate_Err:

    Me.Undo
    Resume Next


    End Sub
    And

    Private Sub ATMID_NotInList(NewData As String, Response As Integer)

    MsgBox "Sorry, Atm : " & Me.ATMID & " Does not exist." & vbCrLf & _
    "Please choose an item from the list." _
    , vbExclamation, "Error in processing"
    Response = acDataErrContinue

    End Sub

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    have you removed the code from your BeforeUpdate Event. Check that out please. If you haven't then do it.

  5. #5
    Mclaren is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    Quote Originally Posted by maximus View Post
    have you removed the code from your BeforeUpdate Event. Check that out please. If you haven't then do it.
    I have just deleted all the code in the before update event.I now only have your code with a few changes but nothing that affects the running of the code. i still get an error message.

  6. #6
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Now to proove my point I am attaching a sample mdb file for you. Its has a form which contains only one form with Two Comboboxes populated with a Value List. The Values entered are A,B,C.

    Enter Z in the First Combo Box and you will see the standard Access Msgbox being displayed.

    Enter Z in the Second ComboBox and you will see the Customized msgbox of this code being Displayed.

    Private Sub cboDepartment_NotInList(NewData As String,
    Response As Integer)
    MsgBox "Sorry, Invalid Entry." & vbCrLf & _
    "Please choose an item from the list." _
    , vbExclamation, "Spiders Infotech"
    Response = acDataErrContinue
    End Sub

    You will see that in the second case the customized access error msg is not displayed at all.

    Hope this will be of some help. If the problem persist. attach you database in access db in Ms Access 2000 format. i will look in it.

  7. #7
    Mclaren is offline Competent Performer
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    thanks, its a big DB, 4.6 MB so far, if i don't come right i will post you a link so you can download it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Set Warnings Off
    By NMJones in forum Access
    Replies: 2
    Last Post: 04-12-2010, 03:06 PM
  2. Replies: 5
    Last Post: 01-29-2010, 11:09 AM
  3. Turn Off Auto Save Record
    By Syntinal in forum Forms
    Replies: 2
    Last Post: 03-01-2009, 11:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums