Dlookup to prevent duplicates

[arothacker]

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

[pbaldy]

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

[ItsMe]

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.

[arothacker]

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

[pbaldy]

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?

[arothacker]

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.

[pbaldy]

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?

[arothacker]

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!

[pbaldy]

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

[arothacker]

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.

[pbaldy]

Happy to help!

[arothacker]

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??

[pbaldy]

How about

If DCount("WWID", "Updated Headcount", "[WWID] = '" & Me.txtWWID & "' And Number = 1") > 0 Then

[arothacker]

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.

[pbaldy]

Looks like your field name has an inadvisable space, so it has to be bracketed.