How to prevent a duplicate record using Userform data entry on a Mac

lintohu

New Member
Joined
Oct 11, 2017
Messages
5
I have created a userform for warehouse with 8 text boxes with the an "Add Record" button to enter record into the database.

To make sure duplicated records are not being entered (e.g. same item being inputted into system on same date), I want to setup Macro to ensure same combination of "Material Name" (Column B) and "Date" (Column D) won't be entered for more than one time.

Option Explicit

'Private variables
Dim cNum As Integer
Dim X As Integer




Private Sub Reg2_Change()


End Sub


Private Sub UserForm_Initialize()


Reg1.List = Sheet4.Range("A2:A10").Value

Reg3.List = Sheet4.Range("L2:L3").Value



End Sub


Private Sub Reg4_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'check date format
With Reg4
If Len(.Text) <> 10 Or _
Mid(.Text, 3, 1) <> "/" Or _
Mid(.Text, 6, 1) <> "/" Then


MsgBox "Date format must be mm/dd/yyyy"
Cancel = True


Exit Sub


End If
End With
If Not IsDate(Reg4) Or Mid(Reg4, 1, 2) > 12 Then
MsgBox "Plese enter a valid date (mm/dd/yyyy)."
Cancel = True
Exit Sub
End If
End Sub


Private Sub Reg1_Change()


Dim index As Integer
index = Reg1.ListIndex


Reg2.Clear


Select Case index
Case Is = 0
With Reg2
Reg2.List = Sheet4.Range("B2:B40").Value
End With
Case Is = 1
With Reg2
Reg2.List = Sheet4.Range("C2:C21").Value
End With
Case Is = 2
With Reg2
Reg2.List = Sheet4.Range("D2:D20").Value
End With
Case Is = 3
With Reg2
Reg2.List = Sheet4.Range("E2:E19").Value
End With
Case Is = 4
With Reg2
Reg2.List = Sheet4.Range("F2:F20").Value
End With
Case Is = 5
With Reg2
Reg2.List = Sheet4.Range("G2:G3").Value
End With
Case Is = 6
With Reg2
Reg2.List = Sheet4.Range("H2:H7").Value
End With
Case Is = 7
With Reg2
Reg2.List = Sheet4.Range("I2:I3").Value
End With
Case Is = 8
With Reg2
Reg2.List = Sheet4.Range("J2:J3").Value
End With
End Select


End Sub


Private Sub Cmdadd_Click()
Dim X As Integer
Dim nextrow As Range
Dim cNum As Integer
Dim M_Item As String
Dim M_Date As Date
Dim LastRow As Long
Dim rw As Integer


M_Date = Me.Controls("Reg" & 4).Value
M_Item = Me.Controls("Reg" & 2).Value
LastRow = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row


Application.ScreenUpdating = False
For rw = LastRow To 2 Step -1
If Cells(rw, "B") = M_Item And Cells(rw, "D") = M_Date Then GoTo Passem
Next rw
GoTo NO_Dups
Passem:
Application.ScreenUpdating = True
MsgBox "This Item " & M_Item & " and Date " & M_Date & " were used previously, try again."
Exit Sub
NO_Dups:


For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
'clear
For X = 1 To cNum
Me.Controls("Reg" & X).Value = ""
Next


On Error GoTo 0
Exit Sub

errHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please notify the administrator"


End Sub

I got great help from Perpa but still couldn't make the form work since it shows there is a type mismatch in the "M_Date = Me.Controls("Reg" & 4).Value" , seems I have missed something in the code and would be great if I can find out what is the reason causing the problem, thank you!
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top