Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727

    Check for duplicate value on lost focus event

    Hi,

    I have a split database with a frontend and backend. There is a bound text box called txtCitationNum linked to a table called NODLog_tbl. I already set the backend to not allow duplicates but I wanted something more user friendly so that when the user's cursor leaves the text box (txtCitationNum) it will search to see if that value already exists in the backend table (NODLog_tbl. If the value does already exist I would like a prompt to let the user know immediately that this value is a duplicate. If it does not exist then I would like it to do nothing. I searched online to see what I could find and someone suggested using the BeforeUpdate event on the control. Here is the code:



    If IsNull(DLookup("YourField", "YourTable", "YourField = " & Me.YourField)) Then
    Cancel = True
    Msgbox "error ......", vbokonly
    End If

    However I could not get this to work. The code doesn't do anything in the before update or the on lost focus events. Can someone help me with this?

    Thanks in advance.


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    [Event Procedure] is selection in event property?

    You changed code to use your object names?

    Have you step debugged?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    What do you mean about event procedure? I make the selection as “On Lost Focus” or “Before Update” events then do code builder and paste the VBA there. Yes I edited the code to match my objects and still nothing. I didn’t do the debug compile yet. Should I try that?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Every event has a property where you select the type of code ([Embedded Macro] or [Event Procedure]).

    Yes, should do debug compile. But then set a breakpoint so you can step through code and check values of variables as code progresses.

    Why don't you post your actual code? Or provide db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    In the code I pasted above, it says to enter "YourField" does this mean the field in the table? If so, my code looks like this:

    Private Sub txtCitationNum_BeforeUpdate(Cancel As Integer)

    If IsNull(DLookup("Citation_Num", "NODLog_tbl", "Citation_Num = " & Me.Citation_Num)) Then
    Cancel = True
    MsgBox "error ......", vbOKOnly
    End If

    End Sub


    If they meant the text box then it would look like this:

    Private Sub txtCitationNum_BeforeUpdate(Cancel As Integer)

    If IsNull(DLookup("txtCitationNum", "NODLog_tbl", "txtCitationNum = " & Me.txtCitationNum)) Then
    Cancel = True
    MsgBox "error ......", vbOKOnly
    End If

    End Sub



    When doing either of these methods, I get a syntax error missing operator and it seems to point to "Citation_Num =" or "txtCitationNum =" part of the code.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You are pulling data from a table so need to identify field names as found in table.

    The parameter can be either a field or textbox name from the form. If Citation_Num is a text type field, parameter needs apostrophe delimiters, like:
    Code:
    If IsNull(DLookup("Citation_Num", "NODLog_tbl", "Citation_Num = '" & Me.txtCitationNum & "'")) Then

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Hi June7,

    still not working. I tried in the Before Update event for the control itself and nothing happens until I try to save the record. When I save it then it will have an error message:

    "Run-Time error '3022': The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

    However, I believe this run time error is based on the fact that I changed the index settings to "Yes (No Duplicates)".

    Just for clarification, this is what I have in the before update event:

    Private Sub txtCitationNum_BeforeUpdate(Cancel As Integer)

    If IsNull(DLookup("Citation_Num", "NODLog_tbl", "Citation_Num = '" & Me.txtCitationNum & "'")) Then
    Cancel = True
    MsgBox "error! duplicate found", vbOKOnly
    End If

    End Sub

  8. #8
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Just tried it on the lost focus event and if the text box is blank and my cursor leaves that text box, I get the error! duplicate found message but that is not helpful either. lol

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    BeforeUpdate event works for me.

    Should provide db for analysis.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Are you doing the BeforeUpdate Event Procedure on the control (text box) or the form itself?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    On textbox. Can't remember ever using form BeforeUpdate event.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    The upload attachment won't allow me to upload the frontend and backend files because they are too big. I guess its pretty robust since I have a lot of features. I even emptied out the backend table and deleted all the forms, queries, reports, etc... on the frontend except for the one form I am working on with you. Would you want me to post all the General VBA?

  13. #13
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Ok so I been messing with it and this is what I got in the Before Update for the text box:

    Private Sub txtCitationNum_BeforeUpdate(Cancel As Integer)

    If IsNull(DLookup("Citation_Num", "NODLog_tbl", "Citation_Num = '" & Me.txtCitationNum & "'")) Then
    Cancel = True
    MsgBox "error! duplicate found", vbOKOnly
    End If

    End Sub


    If the text box is blank and I tab over to another text box then nothing happens of course, but once I type something into the txtCitationNum text box then I get the MSGBOX error. Like no matter what I type, regardless if its a duplicate or not, I will get the MSGBOX error message. Does that give you any other clues as to what is happening?

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Not really.

    No, don't need all the VBA.

    Be sure to run Compact & Repair and zip the file with Windows Compression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    data808 is offline Noob
    Windows 8 Access 2007
    Join Date
    Aug 2012
    Posts
    727
    Got this from another forum. This worked perfectly:

    Dim CheckDUP As Variant
    CheckDUP = DLookup("[Citation_Num]", "NODLog_tbl", "[Citation_Num] = '" & Me.txtCitationNum & "'")
    If Not IsNull(CheckDUP) Then
    MsgBox "NOD Already Sent." & vbCrLf & "Please Enter A Different Citation or" & vbCrLf & "Update The Existing Record.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate Record Found"

    Cancel = True
    Me.txtCitationNum.Undo

    Else:
    End If

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-01-2018, 12:37 PM
  2. Keep textbox in same place when lost focus
    By aytee111 in forum Forms
    Replies: 2
    Last Post: 12-13-2016, 03:59 PM
  3. Combo Box Values to Go Blank on Lost Focus
    By McArthurGDM in forum Forms
    Replies: 6
    Last Post: 02-20-2015, 01:22 PM
  4. Replies: 3
    Last Post: 01-18-2014, 02:20 PM
  5. hide a list box on lost focus
    By markjkubicki in forum Programming
    Replies: 2
    Last Post: 08-18-2012, 10:32 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