Making Database Entries Unique?

[mageeg]

I have a userform in which the user enters data. This data is then sent to a database sheet.

This is done through the following code:

Dim sh As Worksheet
    Dim iRow As Long
 
    Set sh = ThisWorkbook.Sheets("Database")
    
    
    If frmForm.txtRowNumber.Value = "" Then
    
        iRow = [Counta(Database!A:A)] + 1
    Else
    
        iRow = frmForm.txtRowNumber.Value
        
    End If
    
    
    With sh
    
    'adding each row to database
    
        .Cells(iRow, 1) = "=Row()-1" 'Dynamic Serial Number
        
        .Cells(iRow, 2) = frmForm.ModelNo.Value
        
        .Cells(iRow, 3) = frmForm.PartNo.Value
        
        .Cells(iRow, 4) = frmForm.WorksOrderNo.Value
        
        .Cells(iRow, 5) = frmForm.SerialNo.Value
        
        .Cells(iRow, 6) = frmForm.MaterialNo.Value
        
        .Cells(iRow, 7) = frmForm.SerialNumber.Value
        
        .Cells(iRow, 8) = frmForm.txtType.Value
        
        .Cells(iRow, 9) = frmForm.txtSize.Value

How would i make particular entries unique?

For example maybe i would like the ModelNo to be unique. So only one entry in the database should have a particular Model Number.

 

[Joe4]

One way is to use the COUNTIF function (Application.WorksheetFunction.CountIf...) to count how many time that value already exists in a particular column.
If it is more than 0, then it already exists, and you can alert the user and not add the record.

If you have a few fields you want to check like this, it may be good to create a separate User Defined Function called something like "ValidateEntries" that checks each one and returns a True or False, depending on whether or not the entry meets your requirements and the record can be added.

 

[mageeg]

One way is to use the COUNTIF function (Application.WorksheetFunction.CountIf...) to count how many time that value already exists in a particular column.
If it is more than 0, then it already exists, and you can alert the user and not add the record.

If you have a few fields you want to check like this, it may be good to create a separate User Defined Function called something like "ValidateEntries" that checks each one and returns a True or False, depending on whether or not the entry meets your requirements and the record can be added.

That sounds like what i would be wanting to do.

I am quite new to this, but have been progressing well. Would you be able to help me adjust my code to use the COUNTIF function?

 

[Joe4]

Before your "With sh" line, you would have a block of code like this:

    If Application.WorksheetFunction.CountIf(Columns("B:B"), frmForm.ModelNo.Value) > 0 Then
        MsgBox frmForm.ModelNo.Value & " already exists in column B.", vbOKOnly, "PLEASE TRY AGAIN!"
        Exit Sub
    End If

 

[mageeg]

Thanks for getting back to me!

Hmm i can't seem to get this to work. Still allows me to add duplicate values for model number

 

[Joe4]

Try adding the sheet qualifier to your range, i.e. change this:

Columns("B:B")

to this:

sh.Columns("B:B")

If that does not work, please post your amended code for us to see, to make sure that you have implemented it correctly.

 

[mageeg]

Try adding the sheet qualifier to your range, i.e. change this:

Columns("B:B")

to this:

sh.Columns("B:B")

If that does not work, please post your amended code for us to see, to make sure that you have implemented it correctly.

Thanks!

That little change worked

 

[mageeg]

Before your "With sh" line, you would have a block of code like this:

    If Application.WorksheetFunction.CountIf(Columns("B:B"), frmForm.ModelNo.Value) > 0 Then
        MsgBox frmForm.ModelNo.Value & " already exists in column B.", vbOKOnly, "PLEASE TRY AGAIN!"
        Exit Sub
    End If

Whilst this worked great, would it be possible to enhance this further and say,

The PartNo and SerialNo can be used multiple times but is only unique together?

e.g. If SerialNo was 1710, it can be used multiple times, but only once with the PartNo of 2805SC

 

[Joe4]

Whilst this worked great, would it be possible to enhance this further and say,

The PartNo and SerialNo can be used multiple times but is only unique together?

e.g. If SerialNo was 1710, it can be used multiple times, but only once with the PartNo of 2805SC

You can use the COUNTIFS function, which allows you to check multiple fields at once.
So you can check your desired combination of fields, to see if that is unique, in the same manner we did the first one.
See here for the layout of the COUNTIFS function: MS Excel: How to use the COUNTIFS Function (WS)

It will be a good exercise to see how well you understand the first part. See how you do, and if you run into issues, post back here, along with the code you tried.

 

[mageeg]

You can use the COUNTIFS function, which allows you to check multiple fields at once.
So you can check your desired combination of fields, to see if that is unique, in the same manner we did the first one.
See here for the layout of the COUNTIFS function: MS Excel: How to use the COUNTIFS Function (WS)

It will be a good exercise to see how well you understand the first part. See how you do, and if you run into issues, post back here, along with the code you tried.

hmm i just had a try with the below code. however it is throwing the already exists error everytime I save data, after testing i believe this is because it is seeing that one of them is not unique and is throwing the error. Where as for that error to appear i want them to both have been used TOGETHER before.

If Application.WorksheetFunction.CountIf(sh.Columns("C:C"), frmForm.PartNo.Value) + Application.WorksheetFunction.CountIf(sh.Columns("E:E"), frmForm.SerialNo.Value) > 0 Then
        MsgBox frmForm.PartNo.Value & frmForm.SerialNo.Value & " This combination of PartNo and SerialNo already exists", vbOKOnly, "PLEASE TRY AGAIN!"
        Exit Sub
    End If