Duplicate Prevention & Continue Data Entry

[irsmalik]

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
*****************************

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim dwgNo, FromPg As String
    Dim stLinkCriteria As String
 
    dwgNo = Me.Drawing.Value
    FromPg = Me.PAGEFROM.Value
 
    If IsNull(Me.Drawing) Then
        MsgBox "Please Enter Drawing Number. This field can not be empty" & vbCrLf & _
            " ", _
            vbCritical, _
            "Canceling Update"
        Me.Drawing.SetFocus
        Cancel = True
    End If
 
    If DCount("[Drawing]", _
        "Drawings", _
        "[Drawing]='" & Me.[Drawing] & "'") > 0 Then
        MsgBox "Alert . *****  This Drawing Number is already Exist" & vbCrLf & _
            " ", _
            vbCritical, _
            "Canceling Update"
      Cancel = True
    End If
End Sub

******************

Thanks in Advance
irsmalik

[twinnyfo]

irsmalik,

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:

Private Sub txtDrawing_BeforeUpdate(Cancel As Integer)
    Dim dwgNo       As Integer
    Dim FromPg      As Integer
    Dim stLinkCriteria As String
 
    dwgNo = Me.txtDrawing
    FromPg = Me.txtPAGEFROM
 
    If IsNull(Me.txtDrawing) Then
        MsgBox "Please Enter Drawing Number. " & _
            "This field can not be empty", _
            vbCritical, _
            "Canceling Update"
        Me.Undo
    End If
 
    If DCount("[Drawing]", _
        "Drawings", _
        "[Drawing]=" & dwgNo) > 0 Then
        MsgBox "Alert . *****  " & _
            "This Drawing Number already Exists", _
            vbCritical, _
            "Canceling Update"
        Me.Undo
    End If
 
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!

[irsmalik]

Dear twinnyfo

Your Long Advice is appreciated. It was copied from internet.
But I am still looking for Solution.
irsmalik

[twinnyfo]

I think I offered a solution....

Did my recommendatoin work for you?