Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43

    Dlookup to prevent duplicates

    I have a form with a text box called txtID. When the user types a value in the text box I want it to check if the value already exists in the field ID in table "Headcount" (a linked table from SharePoint). If it does, then I want a pop up box to display with error. Is this a Dlookup function?

    I've tried DCount but can't get that to work. Any help is appreciated!

    Thanks!
    Ashley

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,131
    I'd use DCount() but either should work (the test changes). Hard to say why you couldn't get it to work without seeing it. Perhaps this will help:

    DLookup Usage Samples
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,863
    I use Dlookup sometimes if it is just one field I am querying. Problem is, domain functions are expensive. This could be even more apparent over a WAN. Not sure how SharePoint works but you might consider loading the values into the form memory if they are not already present in the form's recordset.

  4. #4
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    What I tried at first was going in the form. Clicking on the txtID (text box) and adding the following code in the Before Update event. The value in txtID needs to be checked against the table field ID so I'm not sure if I wrote it correct.

    Private Sub txtID_BeforeUpdate(Cancel As Integer)
    If DCount("ID", "Updated Headcount", "[ID] = '" & Me.txtID& "'") > 0 Then
    Resp = MsgBox("This Value Already Exists! Please Enter New Value")
    Cancel = True

    I'll take a look at your link too. Thanks!
    End If
    End Sub

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,131
    That looks okay offhand, though I'd just have

    MsgBox "This Value Already Exists! Please Enter New Value"

    ID is a text field? What error do you get, or why does it not work?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    Yeah we had text values when we set it up initially. The values now are all numbers though so maybe if we change it to a number field it would work? Not sure if that's the reason but it just doesn't work. After I entered the code in the form I entered an ID in the txtID that I know already exists and nothing happened.

    Do I have the second row of the code right? ID is the field in the table Updated Headcount and txtID is where the user is typing in a value.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,131
    It looks correct, yes. Try putting in a message box before the test, just to make sure the code is running at all. Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    Yeah the code doesn't seem to be running at all. I can't post the database because of the information we have in it but I attached some screenshots that can hopefully help. Thanks so much for responding so quickly, we've been struggling here!

    Click image for larger version. 

Name:	1-9-2014 12-36-54 PM.jpg 
Views:	31 
Size:	116.4 KB 
ID:	14984Click image for larger version. 

Name:	1-9-2014 12-37-35 PM.jpg 
Views:	30 
Size:	200.6 KB 
ID:	14985

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,131
    Is any code running in the db? Since you say this isn't running, the first thing to check is that either the db is in a trusted location or that you've explicitly enabled code to run. If not, typically you'd see an option to enable it right under the ribbon:

    http://www.btabdevelopment.com/ts/de...aspx?PageId=13
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    That fixed it!! I just went into the trust center settings and enabled macros. Ah thank you! Can't tell you how much this helped.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,131
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    I'm actually looking back into this because we have another criteria to add.

    I have the code:

    Private Sub txtWWID_BeforeUpdate(Cancel As Integer)
    If DCount("WWID", "Updated Headcount", "[WWID] = '" & Me.txtWWID & "'") > 0 Then
    MsgBox ("This Value Already Exists! Please Enter New Value")
    Cancel = True
    End If
    End Sub


    What I need is if the value exists in the WWID column AND there is a 1 in the Number column then the message box pops up. Any ideas??

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,131
    How about

    If DCount("WWID", "Updated Headcount", "[WWID] = '" & Me.txtWWID & "' And Number = 1") > 0 Then
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    arothacker is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Jan 2014
    Posts
    43
    Hmm i get this error:

    Syntax error (missing operator) in query expression '[WWID] = '1008378' And CY PBP = 1

    CY PBP is in replace of the Number field from before (figured it was easier to say haha). I'm not sure if I said it right the first time either. If the value typed in the txtWWID textbox is already in the WWID column of the table Updated Headcount AND has a value > 0 in the CY PBP field then message box pops up preventing duplicate. If the value already exists in the WWID column but has no value in the CY PBP field then it will be allowed.
    Attached Thumbnails Attached Thumbnails error.png  

  15. #15
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,131
    Looks like your field name has an inadvisable space, so it has to be bracketed.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 2
    Last Post: 01-04-2013, 09:04 PM
  2. Replies: 9
    Last Post: 06-08-2012, 07:11 PM
  3. How to prevent duplicates in said example
    By raymondbeckham in forum Forms
    Replies: 1
    Last Post: 04-11-2012, 03:29 PM
  4. Replies: 3
    Last Post: 02-10-2012, 11:34 AM
  5. Replies: 15
    Last Post: 12-03-2010, 10:14 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