Warning message for duplicated records

[zhangsh]

This is a MS Access question.

A MS DB(let me call it CONTACT) has a table (called INFORMATION) which has three primary keys: id, name and dob. When enter a new record in a form, I'd like to have a warning message when I enter the same record twice. I'd like to see the message right after I enter the dob instead of finishing all key entry. Access only tells you if it's a duplicates or not at the end of the form, not right after the third key variable entered.

Any help would be appreciated.

Thanks.

 

[formerTexan]

Hello Zhangsh,

You can use DLookup in lieu of the recordset example that follows. However, if your table is very large you should consider DLookup's performance.

You can amend the following code and place it in whatever event you find appropriate, such as the dob textbox's BeforeUpDate.

You will also have to be careful to corral your users and make sure they follow the necessary order in entering data. In other words you don't want them entering dob info first.

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String
    sSQL = "SELECT tblInformation.id, tblInformation.name, tblInformation.dob FROM tblInformation WHERE (((tblInformation.id)= " & [Forms]![Form5]![txtID] & ") AND ((tblInformation.name)='" & [Forms]![Form5]![txtname] & "') AND ((tblInformation.dob)= '" & [Forms]![Form5]![txtdob] & "'));"
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL)
        If Not rs.BOF And Not rs.EOF Then
            MsgBox "You have matching records"
        End If
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing

Cheers, Bill

 

[zhangsh]

Bill, thank you so much for your response. I'll apply your codes to my DB but would like to clarify two things from you:

1. What is the "form5" in your code?
2. what are the "txtid", "txtname" and "txtdob"? They are not my variable names, right? If so, what are they?

Thanks.

 

[formerTexan]

Zhangsh,

Use as an example only. You need to replace with your own form/control/table/field names.

Cheers,
Bill

 

[zhangsh]

Bill, I used your codes but did not work. A run time error showed up saying the jet engine cannot find the record. Maybe I did not use your code correctly. I replaced "form5" with the name of my form (frmContact) and "txtid" with "id", "txtname" with "name" and txtdob with "dob". When I entered a new record, the run time error popped up. I actually need to enter a new record.I want the warning message show up when the second record with same id, name and dob entered.

Does that make sense?

 

[formerTexan]

One possibility is that you haven't correctly replaced the names of forms, fields and/or controls.

The 2nd thing I can think of is that Access is having problems distinguishing between your controls and fields. I'm guessing you created your controls from the Access field list. By default Access names controls after the field they are bound to. If a testbox is created and bound to a field named ID, then the textbox will be named ID. Bad procedure. Rename your controls to something else. ID > txtID.

Cheers, Bill