Validation Lookup on form

[alperale]

I have a form that my co-workers(users) are using for data entry. (In data entry mode) I'm having a problem with duplicate entries. I don't want duplicates, so my table is indexed and does not allow them. My problem is that my users aren't notified that a record already exists until they've filled out the entire form. The primary key is the first field they enter. I'd like some type of notification after the primary key is entered so we don't waste time and efforts filling out a form for a record that already exists.

I have seen some similar posts, but the solutions don't seem to be working for me. I have NEVER used any type of coding before, (which is probably my problem) but if whomever is willing to help me can be VERY specific on how and where I actually PUT the code, I would greatly appreciate it.

The field in question is called "IssueID" and it is located in table "Issue." Also, my database is split.

I don't need any actions to take place after the error message. The users can erase and move on to the next one.



Thanks in advance for any help!!

[pbaldy]

You could use a DCount(), in the before update event of the textbox where they enter the key. More info on syntax:

DLookup Usage Samples

The code would look like:

If DCount(...) > 0 Then
  Cancel = True
  MsgBox "Already entered"
End If

[alperale]

You could use a DCount(), in the before update event of the textbox where they enter the key. More info on syntax:

DLookup Usage Samples

The code would look like:

If DCount(...) > 0 Then
  Cancel = True
  MsgBox "Already entered"
End If

Doesn't seem to be working. Which Builder should I be using? Macro, Expression, or Code? I used the Code Builder. Here's what I used:

Option Compare Database

Private Sub IssueID_BeforeUpdate(Cancel As Integer)
If DCount("IssueID", "Issue", "Criteria = n") > 0 Then
  Cancel = True
  MsgBox "Already entered"
End If
End Sub

Should I have something besides "Criteria=n"??

[pbaldy]

Yes; you want the version referring to a form control, and the word "Criteria" needs to be the field name you're comparing the value on the form against. Oh, and code builder was correct.

[alperale]

Yes; you want the version referring to a form control, and the word "Criteria" needs to be the field name you're comparing the value on the form against. Oh, and code builder was correct.

Thank you for your help, by the way, I really appreciate it. I changed to the version referring to a form control, however it still is not notifying me of entering a duplicate. Here is what I have:

Option Compare Database

Private Sub IssueID_BeforeUpdate(Cancel As Integer)
If DCount("IssueID", "Issue", "IssueID = " & Forms![Issue]!IssueID) > 0 Then
  Cancel = True
  MsgBox "Already entered"
End If
End Sub

Any suggestions?

[pbaldy]

That looks okay offhand. Can you post the db?

[alperale]

That looks okay offhand. Can you post the db?

What does that entail, exactly? The database has a lot of sensitive info that I can't post. Perhaps I could make a copy and delete the sensitive info? Would that help?

[pbaldy]

You certainly don't want to post anything with sensitive info. If you can just export that form and table into a new db, with a few fake records so we can test the duplicate check, that would help.

[alperale]

Attachment 2617

Attachment 2618

How's this? I split them, front end and back end (_be.accdb), so it would be the same way I have it set up. You may need to update the link between them. Thanks again for the help!!

[pbaldy]

Your IssueID is a text field, not numeric. Try

If DCount("IssueID", "Issue", "IssueID = '" & Forms![Issue]!IssueID & "'") > 0 Then
  Cancel = True
  Me.Undo
  MsgBox "Already entered"
End If

[alperale]

Still no luck. Did that modified code work for you? I also tried converting the field to a number and using the previous code and it didn't work...

[pbaldy]

Yes, that worked for me. How is it not working for you? Because of your enter key behavior setting, you'll need to tab or click out of the field for it to work (the enter key won't).

[alperale]

I type in a number that I already know is in the table and press tab, and it lets me continue without a message.

[pbaldy]

Is the database in a trusted location, or have you explicitly enabled code? Let's make sure it's running at all. If you have a security warning right under the ribbon, code isn't running.

[alperale]

That was it! It works! I did have the security warning just below the ribbon. Would "explicitly enabling code" allow me to bypass this?