PDA

View Full Version : Sleeper: Preventing Duplicate data entry in a userform



thomas.szwed
11-28-2007, 05:19 AM
Hello there,

I have a query regarding preventing duplicate data entry in one of my custom built forms. To keep it short and simple, i basically have built a userform which the user fills in, clicks add, and the data is the recorded onto a sheet. I would now like;

- to prevent duplicate entries on one specific field when the user clicks the add button. (e.g. the User ID field, someone enters 5 as the user ID and clicks the add button, an error message pops up saying "There is already another record with USer ID '5', try another.

Here is my code for adding records, could anyone advise me of some code to add in for preventing duplicates on the 'PersNum' field....?



Private Sub btnAddRecord_Click()
'next empty cell in column A
Set c = Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False 'speed up, hide task
'write userform entries to database
c.Value = Me.txtSurname.Value
c.Offset(0, 1).Value = Me.txtForename.Value
c.Offset(0, 2).Value = Me.ddlAssignee.Value
c.Offset(0, 3).Value = Me.txtPersNum.Value
c.Offset(0, 4).Value = Me.txtStartDate.Value
c.Offset(0, 5).Value = Me.txtEndDate.Value
c.Offset(0, 6).Value = Me.ddlDivision.Value
c.Offset(0, 7).Value = Me.ddlLocation.Value
c.Offset(0, 8).Value = Me.txtLineManager.Value
c.Offset(0, 9).Value = Me.txtVCS.Value
c.Offset(0, 10).Value = Me.ddlHealth.Value
c.Offset(0, 11).Value = "C"
'clear the form
With Me
.txtSurname.Value = vbNullString
.txtForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.txtStartDate.Value = vbNullString
.txtEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.txtLineManager.Value = vbNullString
.txtVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
End With
Application.ScreenUpdating = True
End Sub


Any help would be much appreciated....

Thanks, Tom

Bob Phillips
11-28-2007, 05:36 AM
Use something like



If IsError(Application.Match(Me.txtUserId,Worksheets("Data").Columns(1),0)) Then
MsgBox "duplicate"
Else
'update it
End If

thomas.szwed
11-28-2007, 07:15 AM
Hi and first thankyou for your quick response - realy helpful.

The code you suggested fits in well. But it now doesnt seem to let me add a record at all - i cant see what its getting caught up on but it seems to think that whatever i enter in the 'PersNum' txt box on the userform exists in the sheet in the workbook...

Here is the code - My PersNum field is column 4 in my spreadsheet.



Private Sub btnAddRecord_Click()
'next empty cell in column A
Set c = Range("a65536").End(xlUp).Offset(1, 0)
Application.ScreenUpdating = False 'speed up, hide task
If IsError(Application.Match(Me.txtPersNum, Sheets("Master Data").Columns(4), 0)) Then
MsgBox "That Personel Number is already assigned - try another"
Else
'update it
'write userform entries to database
c.Value = Me.txtSurname.Value
c.Offset(0, 1).Value = Me.txtForename.Value
c.Offset(0, 2).Value = Me.ddlAssignee.Value
c.Offset(0, 3).Value = Me.txtPersNum.Value
c.Offset(0, 4).Value = Me.txtStartDate.Value
c.Offset(0, 5).Value = Me.txtEndDate.Value
c.Offset(0, 6).Value = Me.ddlDivision.Value
c.Offset(0, 7).Value = Me.ddlLocation.Value
c.Offset(0, 8).Value = Me.txtLineManager.Value
c.Offset(0, 9).Value = Me.txtVCS.Value
c.Offset(0, 10).Value = Me.ddlHealth.Value
c.Offset(0, 11).Value = "C"
'clear the form
With Me
.txtSurname.Value = vbNullString
.txtForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.txtStartDate.Value = vbNullString
.txtEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.txtLineManager.Value = vbNullString
.txtVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
End With
End If
Application.ScreenUpdating = True
End Sub



Would you like me to send my workbook?

Many Thanks

thomas.szwed
11-28-2007, 08:59 AM
Does anyone have an answer to this????

Bob Phillips
11-28-2007, 10:22 AM
Sorry, wrong way around, it should be



If IsError(Application.Match(Me.txtPersNum, Sheets("Master Data").Columns(4), 0)) Then

thomas.szwed
11-29-2007, 04:04 AM
Hi there again,

Pls looked at my workbooked attached. The code still doesnt seem to be working completely - it always thinks there are duplicates now. Open the sheet and click on the pencil on my custom toolbar at the top (called 'controls' if you cant see it - View > Toolbars). You are confronted with the userform. Simply change the PersNum field to something unique and try adding a record.....to see for yourself....

I really appreciate any help on sorting out this problem.

Thanks.

Bob Phillips
12-01-2007, 06:54 PM
This should do it



If Not IsError(Application.Match(Val(Me.txtPersNum.Text), Sheets("Master Data").Columns(4), 0)) Then

thomas.szwed
12-04-2007, 04:54 AM
Thankyou x million.....works a treat!

thomas.szwed
12-10-2007, 02:53 AM
I have a new problem now as a result of fixing this....On my userform i am using one unique field (PersNum) as my 'primary key' with this 'duplicate checking' code (above) in place. Therefore should the end user add another record with the same PersNum as a current record, a message pops up informing the user to find another unique PersNum. This is exactly what i want it to do. But when a user edits a record - say changes someones surname to Bloggs.....the code for duplicate checking runs again and tells us we need another unique PersNum. But i am only wishing to change their surname but it sees it as adding a new record therefore wants an unique PersNum. Here is the current code below. Can ne1 help???



Private Sub btnEdit_Click()
If Not IsError(Application.Match(Val(Me.txtPersNum.Text), Sheets("Master Data").Columns(4), 0)) Then
MsgBox "That Personel Number is already assigned - try another"
Else
Application.ScreenUpdating = False
Set c = ActiveCell ' c selected by Search
c.Value = Me.txtSurname.Value ' write amendments to database
c.Offset(0, 1).Value = Me.txtForename.Value
c.Offset(0, 2).Value = Me.ddlAssignee.Value
c.Offset(0, 3).Value = Me.txtPersNum.Value
c.Offset(0, 4).Value = Me.txtStartDate.Value
c.Offset(0, 5).Value = Me.txtEndDate.Value
c.Offset(0, 6).Value = Me.ddlDivision.Value
c.Offset(0, 7).Value = Me.ddlLocation.Value
c.Offset(0, 8).Value = Me.txtLineManager.Value
c.Offset(0, 9).Value = Me.txtVCS.Value
c.Offset(0, 10).Value = Me.ddlHealth.Value
'restore Form
With Me
.btnEdit.Enabled = False
.btnDelete.Enabled = False
.btnAddRecord.Enabled = True
.txtSurname.Value = vbNullString
.txtForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.txtStartDate.Value = vbNullString
.txtEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.txtLineManager.Value = vbNullString
.txtVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
End With
Application.ScreenUpdating = True
End If
End Sub


You see i need the If statement in place in case a user was trying to change the PersNum, because we dont want duplicates. But if they are just perhaps correcting a spelling mistake and changing their surname the code still kicks in....

Bob Phillips
12-10-2007, 03:18 AM
Add it to the textbox afterupdate event rather than the button click.

thomas.szwed
12-10-2007, 03:25 AM
Thanks sounds like a sensible solution.....Could you give me some starters help as to how to build in this After Textbox update event?? would it go into the Button_edit lick event tho still?

thanks

Bob Phillips
12-10-2007, 05:39 AM
No, it is a textbox event, which is a separate event



Private Sub txtPersNum_AfterUpdate()
If Not IsError(Application.Match(Val(Me.txtPersNum.Text), Sheets("Master Data").Columns(4), 0)) Then
MsgBox "That Personel Number is already assigned - try another"
Else
Application.ScreenUpdating = False
Set c = ActiveCell ' c selected by Search
c.Value = Me.txtSurname.Value ' write amendments to database
c.Offset(0, 1).Value = Me.txtForename.Value
c.Offset(0, 2).Value = Me.ddlAssignee.Value
c.Offset(0, 3).Value = Me.txtPersNum.Value
c.Offset(0, 4).Value = Me.txtStartDate.Value
c.Offset(0, 5).Value = Me.txtEndDate.Value
c.Offset(0, 6).Value = Me.ddlDivision.Value
c.Offset(0, 7).Value = Me.ddlLocation.Value
c.Offset(0, 8).Value = Me.txtLineManager.Value
c.Offset(0, 9).Value = Me.txtVCS.Value
c.Offset(0, 10).Value = Me.ddlHealth.Value
'restore Form
With Me
.btnEdit.Enabled = False
.btnDelete.Enabled = False
.btnAddRecord.Enabled = True
.txtSurname.Value = vbNullString
.txtForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.txtStartDate.Value = vbNullString
.txtEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.txtLineManager.Value = vbNullString
.txtVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
End With
Application.ScreenUpdating = True
End If
End Sub

thomas.szwed
12-10-2007, 08:11 AM
THanks for the solution.....but if i want to change many details at once including PersNum i find that as soon as i have entered data for the PersNum field it saves the record and all the fields go blank....which is what the code tells it do...but ideally i want to be pressing the Edit button......do you see where im coming from??

Tom

Bob Phillips
12-10-2007, 08:14 AM
In short ... no I don't.

thomas.szwed
12-10-2007, 08:19 AM
Finally something you cant answer!!!

Basically the code you showed me...performs the duplicate check only on that textbox(PersNum)......so if you are editing multiple textboxes, as soon as you click off the PersNum textbox then it saves all changes and resets the form.....which isnt ideal if the user wanted to make multiple changes and has more to do after the PersNum textbox change.....clearer??

thomas.szwed
12-10-2007, 08:45 AM
I have an idea that might work......If i kept the IF statement in the btnEdit command as before but could we put some code into to say.....

only perform this IF statement if this field has been changed....

Is this possible??

Thanks

Bob Phillips
12-10-2007, 09:26 AM
How about this for an idea.

Have a set of boolean variables, one per textbox, which you set via the AfterUpdate event, and check these in the button click.



Private fPersNum as Boolean
Private fAnother as Boolean

Private Sub txtPersNum_AfterUpdate()
fPersNum =True
If Not IsError(Application.Match(Val(Me.txtPersNum.Text), Sheets("Master Data").Columns(4), 0)) Then
MsgBox "That Personel Number is already assigned - try another"
fPersNum = False
End If
End Sub

Private Sub txtAnother_AfterUpdate()
fPersNum =True
If some_other_test on this textbox Then
fPersNum = False
End If
End Sub

Private Sub btnEdit_Click()
If fPersNum And fAnother Then 'and the rest
Application.ScreenUpdating = False
Set c = ActiveCell ' c selected by Search
c.Value = Me.txtSurname.Value ' write amendments to database
c.Offset(0, 1).Value = Me.txtForename.Value
c.Offset(0, 2).Value = Me.ddlAssignee.Value
c.Offset(0, 3).Value = Me.txtPersNum.Value
c.Offset(0, 4).Value = Me.txtStartDate.Value
c.Offset(0, 5).Value = Me.txtEndDate.Value
c.Offset(0, 6).Value = Me.ddlDivision.Value
c.Offset(0, 7).Value = Me.ddlLocation.Value
c.Offset(0, 8).Value = Me.txtLineManager.Value
c.Offset(0, 9).Value = Me.txtVCS.Value
c.Offset(0, 10).Value = Me.ddlHealth.Value
'restore Form
With Me
.btnEdit.Enabled = False
.btnDelete.Enabled = False
.btnAddRecord.Enabled = True
.txtSurname.Value = vbNullString
.txtForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.txtStartDate.Value = vbNullString
.txtEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.txtLineManager.Value = vbNullString
.txtVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
End With
Application.ScreenUpdating = True
End If
End Sub


I am sure there would be a better way, but without knowing the whole app, it is difficult to think of it.

thomas.szwed
12-10-2007, 09:36 AM
Im a little unsure though on exactly what this code does.......I wouldnt know what test to put in another one of my text boxes???

Basically i want to be able to edit every textbox field on my userform but only perform the duplicate check if there has been a change made to the PersNum textbox. Can this be done?

Bob Phillips
12-10-2007, 10:01 AM
Okay, so now you have confused me. What is wrong with the original test in the button click event then?

thomas.szwed
12-11-2007, 03:44 AM
I really appreciate your help and i want to try to make you understand this...

In the btnEdit_Click sub can we have code that

- only checks for duplicate PersNum fields against the current PersNum textbox value IF
- the PersNum textbox has been modified by the user

So therefore i could open up a record in my userform and change the surname.....the duplicate check wouldnt run....

But if i opened up the userform and changed the Surname and PersNum textboxes then YES i want the duplicate check to run because i HAVE modified the PersNum txtbox- and i need to check im not creating duplicate PersNum fields...

Do you understand???

Current code -

[vba]
Private Sub btnEdit_Click()
Application.ScreenUpdating = False
Set c = ActiveCell ' c selected by Search
c.Value = Me.txtSurname.Value ' write amendments to database
c.Offset(0, 1).Value = Me.txtForename.Value
c.Offset(0, 2).Value = Me.ddlAssignee.Value
c.Offset(0, 3).Value = Me.txtPersNum.Value
c.Offset(0, 4).Value = Me.txtStartDate.Value
c.Offset(0, 5).Value = Me.txtEndDate.Value
c.Offset(0, 6).Value = Me.ddlDivision.Value
c.Offset(0, 7).Value = Me.ddlLocation.Value
c.Offset(0, 8).Value = Me.txtLineManager.Value
c.Offset(0, 9).Value = Me.txtVCS.Value
c.Offset(0, 10).Value = Me.ddlHealth.Value
'restore Form
With Me
.btnEdit.Enabled = False
.btnDelete.Enabled = False
.btnAddRecord.Enabled = True
.txtSurname.Value = vbNullString
.txtForename.Value = vbNullString
.ddlAssignee.Value = vbNullString
.txtPersNum.Value = vbNullString
.txtStartDate.Value = vbNullString
.txtEndDate.Value = vbNullString
.ddlDivision.Value = vbNullString
.ddlLocation.Value = vbNullString
.txtLineManager.Value = vbNullString
.txtVCS.Value = vbNullString
.ddlHealth.Value = vbNullString
End With
Application.ScreenUpdating = True
End Sub
[/Code]

Code to check for duplicates -



If Not IsError(Application.Match(Val(Me.txtPersNum.Text), Sheets("Master Data").Columns(4), 0)) Then
MsgBox "That Personnel Number is already assigned - try another"


If so do you know how to combine this to meet my requirements??

Thankssssssss!

thomas.szwed
12-11-2007, 04:16 AM
Perhaps an OnChange event?

Bob Phillips
12-11-2007, 04:25 AM
In the btnEdit_Click sub can we have code that

- only checks for duplicate PersNum fields against the current PersNum textbox value IF
- the PersNum textbox has been modified by the user

So therefore i could open up a record in my userform and change the surname.....the duplicate check wouldnt run....

But if i opened up the userform and changed the Surname and PersNum textboxes then YES i want the duplicate check to run because i HAVE modified the PersNum txtbox- and i need to check im not creating duplicate PersNum fields...

Do you understand???

I do, but I cannot see the problem. Your original code had a match check in the button click event and so ...

If you change the Surname and that code runs, it will create a record if the PersNum is not already in use. If it is in use, it won't create a new record.

It doesn't matter whether the PersNum has changed or not, as it depends upon whether it is already used.

What is the problem with that?

thomas.szwed
12-11-2007, 04:38 AM
I have solved now..........Thanks

Bob Phillips
12-11-2007, 04:40 AM
How?

thomas.szwed
12-11-2007, 04:44 AM
I guess i didnt understand the logic......I based a private sub on the userform that did the duplicate check everytime it was changed....i didnt realise it would even do it when you clicked btnEdit....so solved....thanks for your time