INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Preventing duplicate records with VBA code

Preventing duplicate records with VBA code

(OP)
Hello I am writing a database which records property rooms.

I want to stop duplicate entries and to this end I want to prevent an entry when a record already exists which has both the same PROPERTY_ID and The Same ROOM_TITLE. I do not want two primary keys so would like to do it in code.

The table is called ROOM_TITLES

It contains the field PROPERTY_ID and ROOM_TITLES

I am still trying to learn code. My thoughts are that on clicking my add record button, I can use a Dlookup function to see if the record exists before adding the record.

I would very much appreciate if someone could show me how I would write this code. I think the following is doing a similar type of thing. Many thanks Mark

SAMPLE CODE I HAVE FOUND SO FAR:

CODE -->

Private Function IsDuplicateRecord() As Boolean

    On Error Resume Next
    Dim PreviousRecordID As Long
    IsDuplicateRecord = False

    PreviousRecordID = 0
    PreviousRecordID = DLookup("MyID", "CustomerT", "MyID<>" & MyID & _
        " AND CustomerID=" & CustomerID & " AND PersonID=" & PersonID)
    
    If PreviousRecordID <> 0 Then
        MsgBox "Customer Exists Already"
        IsDuplicateRecord = True
    End If

End Function


Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsDuplicateRecord Then Cancel = 1
    
End Sub 

RE: Preventing duplicate records with VBA code

hi,

I don't see PROPERTY_ID and ROOM_TITLE in your sample code!

What have YOU tried? What were the results?

RE: Preventing duplicate records with VBA code

(OP)
No the sample code is not my code, but came from an example which i found that did something similar.

I'm trying to learn to programme and thought it just might show someone what i'm trying to do.

Thank you Mark

RE: Preventing duplicate records with VBA code

If you are designing a database, why not use the databases features, like enforce referential integrity, which will not allow duplicates to be added to your database.

Alternatively I suppose you could perform a Select query with the key values in your Where clause as parameters. If there is a result, you already have that.

RE: Preventing duplicate records with VBA code

(OP)
In there table there can be duplicate values for RoomTitle, but not for the same property_ID.

Thanks Mark

RE: Preventing duplicate records with VBA code

Please post an example of what you are referring to, including the table structure.

RE: Preventing duplicate records with VBA code

(OP)
OK-

So the table, ROOM_TITLES contains the fields:

ID (autonumber field)
PROPERTY_ID (integer)
ROOM_TITLES (text)

Property_ID can contain duplicates
Room_Titles can contain duplicates

but i want to stop duplicates in Room_Titles for the same Property_ID

Thanks Mark

RE: Preventing duplicate records with VBA code

That's your STRUCTURE.

I first asked for an EXAMPLE.

RE: Preventing duplicate records with VBA code

(OP)
So an example of what i DO NOT want to happen would be

ID.....PROPERTY_ID.......ROOM_TITLES"
1......12.......................Living Room
2......5.........................Kitchen
3......5.........................Living Room
4......12.......................Living Room
5......12........................Bedroom

Thanks Mark

RE: Preventing duplicate records with VBA code

I'm with SkipVought, use DB features and referential integrity to manage relationships.

You need to consider normal form and not duplicating data such as text for room titles, and using the relationship to enforce uniqueness.

Here is a quick CDM I threw together to help you visualise your data and the relationships between the entities (Tables)



(underline represents part of the PK, bolded represents FK)

Hopefully you can see that if Room has a PK made up of PropertyID and TitleID (FK to the other entities), it is impossible for duplication to occur smile


"In complete darkness we are all the same, it is only our knowledge and wisdom that separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"
Free Electronic Dance Music

RE: Preventing duplicate records with VBA code

(OP)
Thank you

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close