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

Prevention of duplicate values in a query using a wizard

P: 2
Is it possible to prevent duplicate values in a query using the query wizard?
Jun 7 '12 #1
Share this Question
Share on Google+
4 Replies


P: 18
Expand|Select|Wrap|Line Numbers
  1. Dim SID As String
  2.     Dim stLinkCriteria As String
  3.     Dim rsc As DAO.Recordset
  4.  
  5.     Set rsc = Me.RecordsetClone
  6.  
  7.     SID = Me.strStudentNumber.Value
  8.     stLinkCriteria = "[yourfield]=" & "'" & SID & "'"
  9.  
  10.     'Check your table for duplicate yourfield
  11.     If DCount("yourfield", "tbl.......", _
  12.               stLinkCriteria) > 0 Then
  13.         'Undo duplicate entry
  14.         Me.Undo
  15.         'Message box warning of duplication
  16.         MsgBox "your message " _
  17.              & SID & " has already been entered." _
  18.              & vbCr & vbCr & "You will now been taken to the record.", _
  19.                vbInformation, "Duplicate Information"
  20.         'Go to record of original Student Number
  21.         rsc.FindFirst stLinkCriteria
  22.         Me.Bookmark = rsc.Bookmark
  23.     End If
  24.  
  25.     Set rsc = Nothing
  26. End Sub
Jun 10 '12 #2

zmbd
Expert Mod 5K+
P: 5,287
From what I have seen in the query wizard, no.

You Might consider going back to your table and set the field index property to not allow duplicates. It does create an additional index that might impact performance depending on your database design.

-z
Jun 11 '12 #3

P: 1
It is not possible in the query but you can work around it. If you create a form while the query is highlighted then it will make a copy of it. You can create a datasheet view form as well if needed. Then in the before update event add your code. I used this code to warn of duplicates and offer a choice of continuing or not:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Barcode_AfterUpdate()
  2.     If Nz(DCount("Barcode", "Repairs", "Barcode = '" & Me.Barcode.Text & "'"), 0) > 0 Then
  3.         If MsgBox("The Barcode already exists. Check Warranty! Do you wish to continue?", vbOKCancel, "Duplicate Warning") = vbCancel Then
  4.             Cancel = True
  5.             Me.Undo
  6.             Exit Sub
  7.         End If
  8.     End If
  9. End Sub
If you want a simple prevention then set the field in design view to restrict duplicates
Aug 17 '12 #4

NeoPa
Expert Mod 15k+
P: 31,186
It's possible, but from such a poorly specified question it makes no sense to go any further.

If you want a meaningful answer then you need to post a meaningful question. There is an abundance of help to show how to do that.
Aug 18 '12 #5

Post your reply

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