Preventing duplicate records with VBA code 2

[Moss100]

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:

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

 

[SkipVought]

hi,

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

What have YOU tried? What were the results?

 

[Moss100]

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

 

[SkipVought]

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.

 

[Moss100]

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

Thanks Mark

 

[SkipVought]

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

 

[Moss100]

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

 

[SkipVought]

That's your STRUCTURE.

I first asked for an EXAMPLE.

 

[Moss100]

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

 

[1DMF]

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




"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

 

[Moss100]

Thank you