Solved How to check duplicate on a form on two fields

[amjadinsaudia]

Hi there,

A) I have table document control which has two fields

DocumentNo
Rev

I want to check duplicate on both of fields and prevent user to enter record.

For information Rev field may have blanks

B) A combo box has two columns code, description when user select a record in this combo I want to display description in a text box in front of this combo, how?

 

[arnelgp]

to show the description on your combobox,
set the Bound column to 1.
set the Column Count to 2
set ColumnWidths: 0;1"

for duplicate, you may simple add Index in design view of your table.
add DocumentNo + Rev to your index (without duplicate).

 

[ADIGA88]

Hi Arnelgp,

As I understood from Amjad I think he wants the Doc. No. and Rev. combination to be unique, he needs to make both Doc. No. and Rev a primary key. this will prevent users from entering duplicate Doc. No. and Rev. combination.

 

[amjadinsaudia]

for displaying the description in combo, I did the same procedure as you advised but
want to show the description in a text box separately based on the record selected in the combo.

For duplicate, I can not choose this option as I have existing data which violate the the condition.

I need to control it through form.

 

[arnelgp]

primary key

it can be unique, but not necessary a pk (you can have autonumber as pk or any single field).

 

[arnelgp]

see Before_update of drawing and revisionNo textboxes.

 

[amjadinsaudia]

duplicate drawing with Rev,,,,,,,ITS WORKED FINE. Thank you arnelgp.

now require combo box value to display in a text box. pl refer to attached screen shot.

 

[arnelgp]

you add Code to the "main unit" combobox's AfterUpdate event and Form's current event:

private sub cboMainUnit_AfterUpdate()
me!textbox1 = cboMainUnit.Column(1)
end sub

private sub form_current()
call cboMainUnit_AfterUpdate
end sub

 

[amjadinsaudia]

Thank you for combo box, will try.

when tried duplicate value function it displaying an error.

 

[arnelgp]

i can't exactly tell without knowing your table structure
and the code you have.

 

[Pat Hartman]

For duplicate, I can not choose this option as I have existing data which violate the the condition.

I don't understand why you want to keep bad data. Perhaps it would be better to fix it. Then you can add RI to prevent it again.

If one of the fields can be null, it is more difficult to find the duplicates. Here's one way.

If dCount("*", "yourtable", "PartNum = '" & Me.PartNum & "' AND Nz(Rev, '') = '" & Nz(Me.Rev, "") & "'")

The Nz() is turning a null Rev to a ZLS since comparing null to null will not return true, it will return null so you have to handle fields that can potentially confirm nulls differently.

 

[amjadinsaudia]

you add Code to the "main unit" combobox's AfterUpdate event and Form's current event:

private sub cboMainUnit_AfterUpdate()
me!textbox1 = cboMainUnit.Column(1)
end sub

private sub form_current()
call cboMainUnit_AfterUpdate
end sub

Above code is working fine in order to display combo box values in text box. Thanks arnelgp.

For handling duplicate I have attached error screenshot which I can not handle. further assistance is required please.

@Pat Hartman

The total existing data contains 20K+ records and I don't know why this field was kept blank by user for 9484 records even he can add zero. This data is uploaded from excel. However I will your suggested code. Regards

 

[amjadinsaudia]

Below is the code which I was using to check duplicate on one field [DocumentNo] only and that was working fine. Later one user asked me to check duplicate on two fields.

Later on I could not manage 'how to check duplicate on two fields [DocumentNo] & [Rev] and one field [Rev] could also be null'. If I can be assisted further.

Private Sub DocumentNo_BeforeUpdate(Cancel As Integer)

Dim SID As String
Dim stLinkCriteria As String
Dim rsc As DAO.Recordset

Set rsc = Me.RecordsetClone

If (IsNull(SID = Me.DocumentNo.Value)) Then Me.DocumentNo.Value = 0
stLinkCriteria = "[DocumentNo]=" & "'" & DocumentNo & "'"

'Check StudentDetails table for duplicate StudentNumber
If DCount("DocumentNo", "Documentcontrol", _
stLinkCriteria) > 0 Then
'Undo duplicate entry
Me.Undo
'Message box warning of duplication
MsgBox "Warning!!! Drawing Number " _
& DocumentNo & " has already been entered." _
& vbCr & vbCr & "Kindly use another Drawing no", _
vbInformation, "Duplicate Information"
'Go to record of original Student Number
rsc.FindFirst stLinkCriteria
Me.Bookmark = rsc.Bookmark
End If

Set rsc = Nothing

End Sub

 

[arnelgp]

see my sample db on post#6, it is checking both the drawing (documentNo) and the revision.

 

[amjadinsaudia]

@arnelgp

I tried your provided code but I am facing with an error. I have attached db with tables and forms, If you have a look on it please.
Regards,

 

[arnelgp]

check out your hint form.

 

[amjadinsaudia]

@arnelgp
I checked my form, controls are working fine and smooth, I will appreciate for the attention given to this task. Thank you so much.

 

[amjadinsaudia]

@arnelgp
an error found, when tried to enter a new record and entered unique or new values for drawing and rev and then move to next filed and then return back to rev and change rev value to any existing one. then error appears" item not found in this collection. screenshot is attached.

 

[arnelgp]

you need to define thisID as module-wise variable

Option Compare Database
Option Explicit

Dim thisID As Long

also change the validation to:

Private Function withDup() As Boolean
    Dim retBool As Boolean
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    Set rs = rs.Clone
    thisID = 0
    If rs.RecordCount < 1 Then
        GoTo exit_function
    End If
    If Len(Me!DocumentNo & "") <> 0 And Len(Me!Rev & "") <> 0 Then
        With rs
            .FindFirst "[DocumentNo] = '" & Me![DocumentNo] & "' And " & _
                        "[Rev] = '" & Me![Rev] & "' And " & _
                        "Nz([CPPCategory],'@') = '" & Nz(Me![CPPCategory], "@") & "' And " & _
                        "Nz([MainUnit], '@') = '" & Nz(Me![MainUnit], "@") & "' And " & _
                        "Nz([SECTIONUNIT], '@') = '" & Nz(Me![SectionUnit], "@") & "' And " & _
                        "Nz([SUBSECTIONUNIT], '@') = '" & Nz(Me![SubSectionUnit], "@") & "' And " & _
                        "Nz([DOCUMENTTYPE], '@') = '" & Nz(Me![DocumentType], "@") & "'"
            If Not .NoMatch Then
                thisID = !ID
                retBool = True
            End If
        End With
    End If
exit_function:
    Set rs = Nothing
    withDup = retBool
End Function

 

[amjadinsaudia]

still facing with same error even after changing the validation and defining thisID moduel wise.

can you please test it at your end?