• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

Duplicates Check

What is the best way to check for duplicates in a form based on a select query

I have a continuous form where users enter information (a list of numbers). When the list is so long that it goes off the page it would be nice if rather than check to see if they already have the next entry in the list that the program gives message "You already have that number listed." I cannot use the tables no duplicates as the table holds data for a number of lists and will need to store duplicates.
0
DatabaseDek
Asked:
DatabaseDek
  • 2
  • 2
3 Solutions
 
mbizupCommented:
You could use a check like this in the After Update event of the textbox where the user is entering data:

Dim rs as dao.recordset
set rs = me.recordsetclone

rs.FindFirst "YourField = " & me.txtNumber
if rs.Nomatch = False then msgbox "This is a duplicate"

rs.close
set rs = Nothing

Open in new window


The above assumes the field we are checking is numeric.  If it is text, rewrite line 4 like this:

rs.FindFirst "YourField = '" & me.txtNumber & "'"

Open in new window

0
 
RyanProject Engineer, ElectricalCommented:
A possibly cool alternative to yours, but I used this trick last week in .NET but it'd work in Access.

You have your table of displayed data. Then you have a set of textboxes for each column. The textboxes set the form filter, using wildcards at the end. So as you fill in the boxes, the data is filtered to what you typed.  

If what you've type isn't there, the list will become empty (so you know it's new).  Then you have a button to click which adds the record using the info in the text boxes.

Just a fancy idea.
0
 
DatabaseDekAuthor Commented:
Hi mbizup

I get error on Me,TextNumber data or method not found line 4 when changed to text mode

MrBullwinkle Very nice and useful tip for alternative.
0
 
mbizupCommented:
You need to replace that with the actual name of the textbox that contains the number the user is entering.

This line is just for example purposes (I don't know the names of the controls on your form or the fields in your table).

>>>> rs.FindFirst "YourField = '" & me.txtNumber & "'"


You need to edit it like this:

rs.FindFirst "WhateverTheNameOfYourFieldIs = '" & me.WhateverTheNameOfYourTextboxIs & "'"
0
 
DatabaseDekAuthor Commented:
Thank you both
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now