By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,107 Members | 1,284 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,107 IT Pros & Developers. It's quick & easy.

Check for duplicate addresses from a data entry form

P: 28
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtAddress_BeforeUpdate(Cancel As Integer)
  2. Dim strMsg1, strMsg2, strMsg3, strMsg4, strMsg5 As String
  3. Dim strFind As Variant
  4. Dim rs As DAO.Recordset
  5. Dim strCriteria, strAddress As String
  6.  
  7. strMsg1 = "This address already exists."
  8. strMsg2 = "Click OK to be taken to the record to verify." & Chr$(13) & Chr$(10)
  9. strMsg3 = "If the record you are trying to enter already exists, do nothing."
  10. strMsg4 = "Otherwise add a new record and select no to continue."
  11. strMsg5 = strMsg1 & Chr$(13) & Chr$(10) & strMsg2 & Chr$(13) & Chr$(10) & strMsg3 & Chr$(13) & Chr$(10) & strMsg4
  12.  
  13. Set rs = Me.RecordsetClone
  14. strAddress = Me.[txtAddress].Value
  15. strCriteria = "[Location_Address]=" & "'" & strAddress & "'"
  16.  
  17. If DCount("[Location_Address]", "tblCom_Buildings", strCriteria) > 0 Then
  18.         If MsgBox(strMsg5, vbInformation + vbYesNo, "Duplicate Address") = vbYes Then
  19.             Me.Undo
  20.             rs.FindFirst strCriteria
  21.             Me.Bookmark = rs.Bookmark
  22.  
  23.             rs.Close
  24.             Set rs = Nothing
  25.         Else
  26.             Exit Sub
  27.         End If
  28. End If
  29.  
  30. End Sub
  31.  
Mar 28 '12 #1
Share this Question
Share on Google+
11 Replies


mshmyob
Expert 100+
P: 903
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,
Mar 28 '12 #2

P: 28
My table is normalized not sure what you have in mind basing a query on the primary key. Example?
Mar 28 '12 #3

mshmyob
Expert 100+
P: 903
Could you please show us the table design.

cheers,
Mar 28 '12 #4

P: 28
Table design:

Building_ID (Autonumber, primary key)
Location_Address (Memo)
Location (Text)
Province (Text)
Location_District_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_Expenses (Currency)
Comments (Memo)
Building_Type_ID (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_Area (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.
Mar 28 '12 #5

mshmyob
Expert 100+
P: 903
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,
Mar 28 '12 #6

P: 28
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.
Mar 28 '12 #7

mshmyob
Expert 100+
P: 903
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,
Mar 28 '12 #8

100+
P: 759
What about to define an index, unique, on fields Location_Address and Trans_Date ?
This will not allow records which have the same Location_Address and Trans_Date.
Mar 29 '12 #9

mshmyob
Expert 100+
P: 903
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,
Mar 29 '12 #10

NeoPa
Expert Mod 15k+
P: 31,419
Surprisingly, the option to index a Memo field appears to exist o.O
Mar 29 '12 #11

mshmyob
Expert 100+
P: 903
Learn something new everyday.

cheers,
Mar 29 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.