Check for duplicate addresses from a data entry form

[rovral]

I have a data entry form that lists the details of building sales. I know a building can sell more than once so I need to allow duplicate addresses. However, occasionally the same sale will get entered more than once. I have code that checks if the address is a duplicate and takes the user to the existing record to verify the information and this works fine. But as I add more sales into the database, the possibility of more than one sale for a particular property being entered. I now find the need to not only check for duplicate sales but there is more than one to compare to. I'm not sure if this is possible or if I should add another criteria such as sales date to find an existing sale. Make sense? the following is the code I have thus far and works for one record:

Private Sub txtAddress_BeforeUpdate(Cancel As Integer)
Dim strMsg1, strMsg2, strMsg3, strMsg4, strMsg5 As String
Dim strFind As Variant
Dim rs As DAO.Recordset
Dim strCriteria, strAddress As String

strMsg1 = "This address already exists."
strMsg2 = "Click OK to be taken to the record to verify." & Chr$(13) & Chr$(10)
strMsg3 = "If the record you are trying to enter already exists, do nothing."
strMsg4 = "Otherwise add a new record and select no to continue."
strMsg5 = strMsg1 & Chr$(13) & Chr$(10) & strMsg2 & Chr$(13) & Chr$(10) & strMsg3 & Chr$(13) & Chr$(10) & strMsg4

Set rs = Me.RecordsetClone
strAddress = Me.[txtAddress].Value
strCriteria = "[Location_Address]=" & "'" & strAddress & "'"

If DCount("[Location_Address]", "tblCom_Buildings", strCriteria) > 0 Then
        If MsgBox(strMsg5, vbInformation + vbYesNo, "Duplicate Address") = vbYes Then
            Me.Undo
            rs.FindFirst strCriteria
            Me.Bookmark = rs.Bookmark
            
            rs.Close
            Set rs = Nothing
        Else
            Exit Sub
        End If
End If

End Sub

[mshmyob]

You are better off re-designing your tables based on normalization. Then you would not have this problem and a simple query based on the PK would solve your problems.

cheers,

[rovral]

My table is normalized not sure what you have in mind basing a query on the primary key. Example?

[mshmyob]

Could you please show us the table design.

cheers,

[rovral]

Table design:

Building_ID (Autonumber, primary key)
Location_Addres s (Memo)
Location (Text)
Province (Text)
Location_Distri ct_ID (Number, foreign key)
Trans_Date (Date/Time)
Selling_Price (Currency)
Selling_Terms (Text)
Land_Use (Text)
Legal_Desc (Memo)
Linc_No (Number)
Site_Size_SF (Number)
Site_Size_Acres (Number)
Site_Size_Condo (Text)
Land_Value_Est (Currency)
Vendor (Memo)
Purchaser (Memo)
Net_Income (Currency)
Gross_Income (Currency)
Operating_Expen ses (Currency)
Comments (Memo)
Building_Type_I D (Number, Foreign Key)
Dev_Desc (Memo)
Dev_Name (Memo)
Tenant_Owner (Text)
Other_SF (Number)
Retail_SF (Number)
Office_SF (Number)
Warehouse_SF (Number)
Storage_SF (Number)
Mezzanine_SF (Number)
Basement_SF (Number)
Residential_SF (Number)
Stories (Number)
Net_Rentable_Ar ea (Number)
Const_Year (Text)
No_Units (Number)
Selected (Yes/No)
Lease_Only (Yes/No)

I have relationships with a lease table, location district table and a building type list table.

[mshmyob]

If you are normalized then your form design (ie: validation is incorrect if you allow duplicates). Why would someone put in an address if it already exists in your table? If you need the same address multiple times then you should probably have a bridge table somewhere.

What dictates more than one of the same address?

cheers,

[rovral]

It is a realty sales database so it is possible for the same building to be sold twice or more, but I don't want duplicate sales, so ideally, the form should check first if the address is a duplicate and then probably check the sales date as well to be sure it is a duplicate. Maybe I need another table that holds the sales information and create a one to many here. That is probably what I will do then run my existing check on the address and add a new sale if it does not exist.

[mshmyob]

If you want to keep track of which sales agent sold properties then you would create a sales agent table and put a bridge table between the sales agent table and the property table. In the bridge table you would have the propertyID, the salesagentID, and a date (the 3 combined would be a composit primary key - or if you are good with validation you could create a single autonumber as the primary key) This way each property can be sold as many times as you wish by as many agents as you wish and you have a history of when the properties were sold and who sold them.

You could also tie in a customer table into this bridge table and find out who bought the properties.



cheers,

[Mihail]

What about to define an index, unique, on fields Location_Addres s and Trans_Date ?
This will not allow records which have the same Location_Addres s and Trans_Date.

[mshmyob]

I am not sure if you can create an index on a memo field. Memo fields are not usually ysed as an address field.

cheers,

[NeoPa]

Surprisingly, the option to index a Memo field appears to exist o.O

[mshmyob]

Learn something new everyday.

cheers,