Detect Matching Record

[mjvm]

Hi,

I have been trying to adapt this code for my database. I'm using MS Access 2007, and most front end users are in 2010.

The name of the underlying table is tblStudents, and the record source is qryMainDataEntr y (which links two tables).
The Form is called frmMainDataEntr y. The name of the field holding the data is called StudentName and it has the same name in the table and the form (I know it probably shouldn't).

The code I have tried to apply in the Form is this:

Private Sub StudentName_BeforeUpdate(Cancel As Integer)

If DCount("StudentName]", "tblStudents", "[StudentName]= '" & Me![StudentName] & "'") > 0 Then
  MsgBox "Name Is Already In Database!"

End If
End Sub

I suspect the code is confused between the tables and forms, and have tried to differentiate them with tblStudents_Stu dentName and frmMainDataEntr y_StudentName - but that isn't doing the trick.

The code in this thread seems to apply to data entry in the table, and maybe that is part of the issue?

I would like this code to just return a message that the student's name already exists in the database as soon as the user moves out of that field, not when the whole form has been completed. I need to allow duplicates.

Thank you in anticipation of your help. I hope I have provided enough clear information.

regards,
Marc

(Original hijacked thread - Want Access to notify me if a record already exists.)

[NeoPa]

A very well presented question. These must not be added into existing threads though. I've sorted that for you.

Back to the question, which as I say is well presented and that always makes working on them a lot easier for us.

The code looks fundamentally fine to me. It's missing some logic, but should work assuming all the objects it relates to are correctly named. Can you say what actually happens? Just knowing it doesn't do exactly what you expect is not much to work with. What does it do?

Later, we'll sort out setting the Cancel value depending on the response from the user via the MsgBox() call, but let's get the basics sorted first.

[NeoPa]

Right, I had to do something earlier so didn't quite finish looking into this.

It seems you were actually missing a "[" character from line #3 of your code. I have redone this for you, using DLookup() instead of DCount(), and expanded the MsgBox() part to do what you need. I guess you'll find this easy enough to review, but let me know if you have any questions.

Private Sub StudentName_BeforeUpdate(Cancel As Integer)
    Dim strTmp As String

    With Me.StudentName
        strTmp = Replace("[StudentName]='%N'", "%N", .Value)
        'If record not found then all is fine.  Nothing further to check.
        If IsNull(DLookup(Expr:="[StudentName]" _
                        , Domain:="[tblStudents]" _
                        , Criteria:=strTmp)) Then Exit Sub
        strTmp = "Name (%N) already exists in database%L%LOK to continue anyway?"
        strTmp = Replace(Replace(strTmp, "%N", .Value), "%L", vbNewLine)
        'If the operator wants to continue in spite of the clash then exit.
        If MsgBox(Prompt:=strTmp _
                , Buttons:=vbYesNo Or vbQuestion _
                , Title:=.Name) = vbNo Then Exit Sub
        'Cancel this update.
        Cancel = True
    End With
End Sub

[mjvm]

Hi NeoPa,

Thank you so much for your help. I'm glad my question was clear - I was a bit nervous, it's been a while since I have tried to do any code in my db. Thanks for the tip on starting a new question too.

Thank you for the code - it is picking up if a student name is already entered, so the field names are accurate; and does nothing if a new name is entered.

I would like to change the yes/no buttons to just one "OK" button that acknowledges the message and closes the box. The user then can decide whether to look for the duplicate.

I already have a button on the form labelled 'Check for this student' - which pulls up all records with that name combined with the date of birth so that the user can check for history - but they won't do it every time.

I'm not sure how to modify the code to make this happen?

I think I keep everything up to this point, but when I try to edit the buttons part, I start mucking up your good work :):

strTmp = "Name (%N) already exists in database%L%LOK to continue anyway?"
        strTmp = Replace(Replace(strTmp, "%N", .Value), "%L", vbNewLine)
        'If the operator wants to continue in spite of the clash then exit.
        If MsgBox(Prompt:=strTmp _
                , Buttons:=vbYesNo Or vbQuestion _
                , Title:=.Name) = vbNo Then Exit Sub
        'Cancel this update.
        Cancel = True
    End With

Thank you again for your time - I am most appreciative of your generosity.

[NeoPa]

A couple of small changes then. The return value of the MsgBox() is no longer required and the message doesn't need to prompt the operator to choose. Try this :

Private Sub StudentName_BeforeUpdate(Cancel As Integer)
    Dim strTmp As String

    With Me.StudentName
        strTmp = Replace("[StudentName]='%N'", "%N", .Value)
        'If record not found then all is fine.  Nothing further to check.
        If IsNull(DLookup(Expr:="[StudentName]" _
                        , Domain:="[tblStudents]" _
                        , Criteria:=strTmp)) Then Exit Sub
        strTmp = Replace("Name (%N) already exists in database", "%N", .Value)
        'Notify the operator of the clash.
        Call MsgBox(Prompt:=strTmp _
                  , Buttons:=vbOkOnly Or vbQuestion _
                  , Title:=.Name)
    End With
End Sub

[mjvm]

Thanks NeoPa - that is perfect.

Thanks so much for your help and support. I apologise for the delay in getting back to let you know it worked- I got caught up in so many other things!!!!

All the best,
marcella

[NeoPa]

No worries :-)

I'm very pleased that was a help.

-Adrian.