preventing duplicates from being entered 2

[nuct]

Hi im trying to set up our HR database so that it prevents users from entering duplicate records. Im reasonably sure that I must use the beforeupdate event the dlookup function in some way to do this. However my code:



Private Sub Form_BeforeUpdate(Cancel As Integer)

' used to find duplicates

Dim SurName, ForeName As String

Me.txtSurname.SetFocus
SurName = Me.txtSurname.Text
Me.txtForename.SetFocus
ForeName = Me.txtForename.Text

If Nz(DLookup("[forename]", "dbo_tblStaff", "[surname] = '" & SurName & "' AND [forename] = '" & ForeName & "' "), 0) <> 0 Then

Cancel = True

End If

End Sub


does not seem to work. The application just freezes on the current record if it is a duplicate.

Has anybody got any ideas?

Simon

 

[Crowley16]

[aside]
you why not just use Me.txtSurname directly?
i.e. ..."[surname] = '" & Me.txtSurname & "' AND ...
[/aside]

I think you are doing this in the wrong way... Why not just make Surname & Forename a combined primary key? That'll stop them being able to have duplicates...

plus I'm sure you can't use Cancel like that, probably why your form is freezing...

 

[nuct]

good point, Jesus I can be thick sometimes. Just out of interest what does the the Cancel do anyway, I just copied it from someone elses code.

Simon.

 

[Crowley16]

programming is like that sometimes...

just be glad you don't have to do it with C++...

 

[RoyVidar]

[tt]Cancel = True[/tt]

Some events, like the before update of both forms and controls, can be cancelled thru setting Cancel = True (then the sub declaration shows it as in your initial post). It's also rather popular in a reports on no data event, where you can cancel the opening of the report if it doesn't contain any data (you'll just need to trap for the 2501 error in the calling form)

For instance, it is rather common to use it in the forms before update event, which triggers whenever a save operation is attempted, where setting cancel=true cancels the save so that the users can amend the record and try again. One would probably also add some code to identify which controls doesn't meet your validation, provide a message box to the user and set focus to the relevant control (or thru the message box allow the user to choose an Undo of the record too)

In your DLookup, you're fetching what I believe is a text field (forename), and you issue a NZ on the result, providing 0 if Null, but you test all returned values against 0, which might provide some anomalities. I think I'd rather return "NF" (Not Found;-)) or something from the NZ if Null, and test for that.

Anyway, I'm not sure what you mean by hung, it should really just not allow the save, and return you to the form as it was before the save operation was cancelled, but disallow any moving between records (which would invoke another save attempt) until the forename and lastname inputted doesn't exist in the query/table.

Roy-Vidar

 

[nuct]

cheers both of you, the reason it was freezing was because i was not using the controls directly in the Dlookup like Crowley16 said.

Simon.