Thread: Detecting / Avoiding Duplicates in a VBA userform.

Hi,

I’m trying to create an Excel VBA userform to manage some parking slots and I would need to verify if, on a specific date, a parking space is already attributed and/or if a specific car plate already has a place.

For that, I need a VBA routine that informs and prevents users from entering duplicate data.

When the user clicks on the insert button, the program should verify column C (date in text format). If the value (date) already exists, it should then verify column D (plate n°) and/or column E (parking n°); should any of these latter values match an existing record, the program should inform the user that the entry already exists and prevent him from inserting it.


id_____name________date_________plate n°___parking n°
1______Joao______02/10/2011______XXX______-1/009
2______Pedro_____03/11/2011______YYY______-1/010
3______Luis______02/10/2011______ZZZ______ -1/009
3______Pedro_____15/10/2011______XXX______-1/012


Thanks for your help!

Do you have a dummy workbook with the userform in question already ?
If so please post the dummy file.
This will save re-invnting that part of the wheel.
Solution you require otherwise should be quite straight forward.

Pl see attached file.

I think there isn't any other if u think u have any other please post them here, because I forgot, this are the ones I follow the responses if there is any other I really forgot.

But I think we are missing the point here, and the point is "helping" and as I told you before I’m really sorry if you took that (posting in other forums) as a "capital" offence it wasn't supposed to be like that!

PL see the attached workbook.Verification is made only after the entry in column F.I appreciate your findings.
With regards

Kvsrinivasamurthy,

Thanks for your help, it is much appreciated.

Let me tell you that your idea is very good and the routine you sent just almost does what I need. However it does not return the necessary information to the user (which is that on a specific date either that parking place is already occupied or a given car plate already has a parking space attributed).

In fact, the user only has access to one part of the programme, i.e. “cadastro.xls” which is the main programme; the data in the file “cadastro_dados.xls” is hidden to the user.

Bearing that in mind, I still need help to make the user aware of the existence of a duplicate entry and prevent him from inserting it (when clicking the OK button while inserting or modifying an entry).

Another issue is the question of the date (even though this is not crucial). In my programme the date is inserted as text. I did this to solve two problems that I had while date was formatted as date: 1) if I did a search by, for instance, 3/11/2011, the value returned was 3/11/2011, 13/11/2011 and 23/11/2011; 2) if I did a search for 03/11/2011 it didn’t return any value. Maybe there is a way to avoid this and still use the date format, but I don’t know it :-(

Can you help me further? I’d really appreciate it…

See attached file including solution below
Note: for this to be effective, the data format of the plate and park id must be of the same format (eg with same spaces, same case, with same hyphen/slash format etc)
So if you havent already, include this validation at data entry time to form.

Hopefully this will give you what you need to incorporate into your form etc.

Option Explicit

Public Enum Arr_Const
    HasDupe
    VehicleHasPark
    VehicleHasParkid
    ParkHasVehicle
    ParkHasVehicleid
    [_Last]
End Enum
Function HasDupeTest(Plate As String, Park As String) As Variant

    Dim LastRow As Long, DataRw As Long
    Dim DataArr As Variant, ResultsArr([_Last] - 1) As Variant
    Dim DataSht As Worksheet
    
    Const IDCol = 1
    Const DateCol = 3
    Const PlateCol = 4
    Const ParkCol = 6
    Const DataShtNm = "Sheet1"
    
    Set DataSht = ThisWorkbook.Sheets(DataShtNm)
    
    With DataSht
        LastRow = .Cells(.Rows.Count, DateCol).End(xlUp).Row
        If LastRow > 0 Then DataArr = .Cells(1, 1).Resize(LastRow, ParkCol)
    End With
    
    ' defaults
    ResultsArr(HasDupe) = False
    ResultsArr(VehicleHasPark) = False
    ResultsArr(VehicleHasParkid) = "N/A"
    ResultsArr(ParkHasVehicle) = False
    ResultsArr(ParkHasVehicleid) = "N/A"
                
    For DataRw = 2 To LastRow
        If CDate(DataArr(DataRw, DateCol)) = Date Then
            ' check plate, park and date match
            If DataArr(DataRw, PlateCol) = Plate And DataArr(DataRw, ParkCol) = Park Then
                ResultsArr(HasDupe) = True
                ResultsArr(VehicleHasPark) = True
                ResultsArr(VehicleHasParkid) = DataArr(DataRw, ParkCol)
                ResultsArr(ParkHasVehicle) = True
                ResultsArr(ParkHasVehicleid) = DataArr(DataRw, PlateCol)
                Exit For
            End If
            ' check if plate assigned
            If DataArr(DataRw, PlateCol) = Plate Then
                ResultsArr(VehicleHasPark) = True
                ResultsArr(VehicleHasParkid) = DataArr(DataRw, ParkCol)
            End If
            ' check if park assigned
            If DataArr(DataRw, ParkCol) = Park Then
                ResultsArr(ParkHasVehicle) = True
                ResultsArr(ParkHasVehicleid) = DataArr(DataRw, PlateCol)
            End If
        End If
    Next
    HasDupeTest = ResultsArr
    
    Erase DataArr
    Erase ResultsArr
    Set DataSht = Nothing
    
End Function

Sub TestForDuplicate()
    ' run this test when "Insert" is clicked
    ' msgbox result or insert new record as required
    Dim Results As Variant
    Results = HasDupeTest("XTT 425", "-1/009")
    
    Debug.Print "Duplicate exists ?: " & Results(HasDupe)
    Debug.Print "Vehicle has a park assigned: " & Results(VehicleHasPark)
    Debug.Print "Vehicle assigned to park: " & Results(VehicleHasParkid)
    Debug.Print "Park has a vehicle assigned: " & Results(ParkHasVehicle)
    Debug.Print "Park is assigned to vehicle: " & Results(ParkHasVehicleid)
    
    Erase Results

End Sub

Hi Kvsrinivasamurthy,

I have added this code to the program but it comes always with this error (please see attachment photo)

I tried several ways but it ends always giving the same error (at this point I'm out of option please advise is it me doing something wrong?

I did like this: added the module1 in the modules and pasted the code from your file sheet1 in the second book at sheet (Parking) my DB and a Call function in “frmCadastro” (main program) under the new adding routine.

The error comes up once I add new info and click in the OK button the error go to this line in the module1 «Set DataSht = ThisWorkbook.Sheets(DataShtNm)"

Can you please integrate your code in the "real program" and inform me how have you done it?
One more thing I work with excel 2003 and you with excel 2007 as far as I understood, have this some negative impact in the final result?

Thank you for your help and patience with me.

Is it possible to attach file after changes made by you along with your programme.I will try to find the problem.

Originally Posted by kvsrinivasamurthy

Is it possible to attach file after changes made by you along with your programme.I will try to find the problem.

Hi kvsrinivasamurthy

Of course it is possible!!! In attachment I added the program with the routine added (just don't know if it is in the right place...)

Best regards,