Results 1 to 3 of 3
  1. #1
    TG_W is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299

    Preventing Duplicates without using Primary Key


    To preface, I cannot use the Primary Key to prevent duplicates. There are already 4 Primary Key fields in the table I am writing to, and 3 of those are transaction information that change with each transaction.
    That said, I have a base form linked to a table ("Docs") where my users select a document and transmit it. After they select all of the documents, they move to the transmit screen for a cleaner view, also linked to "Docs". They transmit the document(s) and the information is saved in a table for reporting, troubleshooting, etc. ("Hist").
    In the transmit screen, the users set the revision of the document, as well as it's status and the transmittal information (Sender, Recipient, Sequence Number). What I am trying to do is check in "Hist" if the revision has been issued before. The revision was linked to "Docs", but I have made it unbound in a new attempt to check, but it is still failing to work properly. It can be easily linked again if a possible solution requires this. I was using some "If Not IsNull(DLookup())" series I found on the web, but it seems to only want to check the table that is linked to the form, not another table in the database. Unfortunately, I have destroyed the code I found, so I cannot post to see if that would work. I'm really stuck, so any help is greatly appreciated.

  2. #2
    TG_W is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2010
    Location
    Manvel, TX
    Posts
    299
    I removed my head from my hind quarters and figured out how to properly use DLookup and created a query to check for the value between the two tables. Works like acharm now. I also set the field to null in the case there is a duplicate. Code is below for anyone else.

    If Not IsNull(DLookup("[RevNo]", "qryRevCheck")) Then
    MsgBox "This revision already exists." & Chr$(10) & Chr$(10) & "Please check the data entry via the History button and re-enter the data.", , "Duplicate Entry"
    Me.RevNo = Null
    End If

  3. #3
    Wombat is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2012
    Posts
    33
    4 Primary key fields in one table? I'm pretty much a newb but that sounds crazy. Glad you got it working though.

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

Similar Threads

  1. Replies: 3
    Last Post: 05-11-2011, 02:32 PM
  2. Preventing other values in combo box
    By cheese9799 in forum Forms
    Replies: 3
    Last Post: 02-14-2011, 03:31 PM
  3. Replies: 15
    Last Post: 12-03-2010, 10:14 AM
  4. Runtime error in preventing duplicates code
    By emilyrogers in forum Access
    Replies: 10
    Last Post: 10-07-2010, 08:14 AM
  5. Preventing multiple checkboxes
    By emerywang in forum Forms
    Replies: 2
    Last Post: 01-26-2010, 01:43 PM

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