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

Duplicate Prevention & Continue Data Entry

P: 71
Hello Friends
I am making a database for Drawings Duplication Check. Fields are given below

DRAWING it is Numeric SheetFrom it is Numeric
SheetTo it is Numeric

Suppose Drawing Number is 500 and Sheet From 1 is already entered...... if I try to enter same data it gives me Duplication Message.

But Drawing Number is 500 and Sheet From is 2 ... it again gives me Duplication error and then stuck the cursor. I want it to continue Data Entry and give me Duplication Message only if Drawing Number and Sheet From is SAME. Code is below
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.     Dim dwgNo, FromPg As String
  3.     Dim stLinkCriteria As String
  5.     dwgNo = Me.Drawing.Value
  6.     FromPg = Me.PAGEFROM.Value
  8.     If IsNull(Me.Drawing) Then
  9.         MsgBox "Please Enter Drawing Number. This field can not be empty" & vbCrLf & _
  10.             " ", _
  11.             vbCritical, _
  12.             "Canceling Update"
  13.         Me.Drawing.SetFocus
  14.         Cancel = True
  15.     End If
  17.     If DCount("[Drawing]", _
  18.         "Drawings", _
  19.         "[Drawing]='" & Me.[Drawing] & "'") > 0 Then
  20.         MsgBox "Alert . *****  This Drawing Number is already Exist" & vbCrLf & _
  21.             " ", _
  22.             vbCritical, _
  23.             "Canceling Update"
  24.       Cancel = True
  25.     End If
  26. End Sub

Thanks in Advance
Mar 20 '19 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 2.5K+
P: 3,282

Since you have been around for a few threads, you should know by now that we require code tags on your posts. Plesae use these in the future.

Second, I'm going to get on my soap box again, because there is an ongoing trend of DB designers using the default MS naming convention for their controls. Just because MS has failed our community terribly and hasn't allowed us to set default control prefixes doesn't mean that we shouldn't use them.

Advice: It is always highly recommended that you rename the controls on your form to something other than "merely" the underlying field name. Yes, this is a default of MS access, but I think MS has failed here. When you have a text box named Drawing that refers to an underlying field with the same name, when you refer to this field/control, this can be ambiguous for the DB. You always want to be explicit, not vague. So, I would recommend you rename these controls to txtDrawing and txtPAGEFROM. You should also rename your table to tblDrawings, so that you know that you are referring to a Table and not some other object. In the long run, over time, you will appreciate this advice.

Additionally, there is almost never a need to refer to a controls .Value property, as it is the default whenever you refer to it.

Also, as I look at your code, you say that your field values are numeric, but you are referring to it as a string in your code.

Also, reather than using the Form's BeforeUpdate Event, use the Control's Beforeupdate Event.

If I were to clean up your code a bit, this is what we have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtDrawing_BeforeUpdate(Cancel As Integer)
  2.     Dim dwgNo       As Integer
  3.     Dim FromPg      As Integer
  4.     Dim stLinkCriteria As String
  6.     dwgNo = Me.txtDrawing
  7.     FromPg = Me.txtPAGEFROM
  9.     If IsNull(Me.txtDrawing) Then
  10.         MsgBox "Please Enter Drawing Number. " & _
  11.             "This field can not be empty", _
  12.             vbCritical, _
  13.             "Canceling Update"
  14.         Me.Undo
  15.     End If
  17.     If DCount("[Drawing]", _
  18.         "Drawings", _
  19.         "[Drawing]=" & dwgNo) > 0 Then
  20.         MsgBox "Alert . *****  " & _
  21.             "This Drawing Number already Exists", _
  22.             vbCritical, _
  23.             "Canceling Update"
  24.         Me.Undo
  25.     End If
  27. End Sub
Also notice that instead of moving back to the control, we have Me.Undo, which removes the bad value and sets the focus back on that control by default.

Hope this hepps!
Mar 20 '19 #2

P: 71
Dear twinnyfo

Your Long Advice is appreciated. It was copied from internet.
But I am still looking for Solution.
Mar 20 '19 #3

Expert Mod 2.5K+
P: 3,282
I think I offered a solution....

Did my recommendatoin work for you?
Mar 20 '19 #4

Post your reply

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