Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Location
    Lancaster, CA
    Posts
    17

    Question Unanswered: Immediate No Duplicates Enforcement

    MS ACCESS 2002 - One table, no relationships, one text key field set to no duplicates. I would like to have this field first on the entry form, but would like to have the "no duplicates" validation enforced upon attempting to leave the field. Now the save and enforcement only occurs when I move to another record, save record, etc. Right now I am using a temporary workaround of having the field last on the form.
    Doug Couch

  2. #2
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Question not sure this is what you mean

    What about the lost_focus option

    or am i lost in your question

    if some1 is in your field and jumps 2 another field , field 1 is still empty, NO ????




    verry quick and dirty


    so : lost_focus
    if field1.txt = "" or (((((err.number>0 this in the error handler))))))then
    msgbox (NOT UNIQUE)
    field1.setfocus
    end if

  3. #3
    Join Date
    Aug 2003
    Location
    Lancaster, CA
    Posts
    17
    Hi Marvel,

    Thanks for the feedback. So far, I've been working with LostFocus, BeforeUpdate and AfterUpdate events. In my most recent version (since my initial post), I am using Me.Requery in the AfterUpdate event and the following as an error handler. It has given me at least some response to these errors from Access, but does not work as intended.

    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 3022 Then
    MsgBox "The ID you entered already exists. Add a number to the end of the (9-character maximum) ID to make it unique. If necessary, delete a character to do so.", vbOKOnly, "Duplicate ID Entry"
    Response = acDataErrContinue
    Cancel = True
    ElseIf DataErr = 3314 Then
    MsgBox "The ID field cannot be left blank once any entry has been made on the blank form. If you do not want this record, enter ZZZZZZZZZ in the ID field and select Delete Record.", vbOKOnly, "Null ID Entry"
    Response = acDataErrContinue
    Cancel = True
    Else
    MsgBox "Error Number " & DataErr & " has occurred, check Access Help", vbOKOnly, "Other Error"
    Response = acDataErrContinue
    Cancel = True
    End If
    End Sub

    This setup isn't working right. The errors re Duplicates and Null in the ID (key) field evoke the Debug error dialogs rather than mine. However, if I click End instead of Debug on the dialog in order to continue, when moving to another record and effecting a Save that way, my routines pop up. So far, I have not found the magic formula to have my messages intercept the Debug error...although at least the error checking is occuring as I leave the field rather than when I leave the record...which it was not doing previously. ID is the only index at present and is a required field, and is intentionally a text field rather than autonumber, etc.

    Doug
    Doug Couch

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    How about a little different approach?

    In the field's .BeforeUpdate event:

    Dim strSql as String, rs as recordset
    strSql = "Select * from tblName where fldName = '" & txtField & "''
    Set rs = CurrentDb.Openrecordset (strsql)
    if rs.BOF or rs.EOF then
    'no matching record found
    else
    'the records already exists
    'put your message box here
    Cancel = true
    End if
    rs.close
    set rs = nothing
    exit sub
    Inspiration Through Fermentation

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •