Checking for duplicate entries in subform


O

omar.norton

Hi all,

I've been trying to approach this problem several ways for some time
now and to no avail.


I am making a database that generates staff rotas. There is a button
you can click on the main rota form to bring up an "edit staff" form
(frmEditStaff) that includes a subform with a list of holidays that
the staff member has booked off in advance. The subform gets it's
data
from a table called "tblBookedHols" - which contains a unique ID
autonumber (the primary key - which isn't really used), a field
containing the staff member's name ("FullName"), a field for a
description of the holiday ([Description] - which may be left null)
and a field containing the holiday date ("HolDate").


When you enter the holiday date in the subform, the staff member's
name is automatically inserted into a hidden textbox that updates the
database.


I am trying to set it up so that if you type in a duplicate holiday
date for any one staff member then a message box will pop up
explaining this and delete the duplicate entry. I tried doing this
with a DLookup in the AfterUpdate event - which usually came up with
the "you cancelled the previous action error" so I tried using
recordsets instead (which I didn't want to do as I don't really
understand them!). I managed to come up with the following code but
this doesn't seem to do anything - no message boxes, no errors, no
nothing... I've also tried putting the code in the BeforeUpdate,
AfterUpdate, BeforeInsert and AfterInsert events which doesn't seem
to
make any difference.


As well as not getting the message box I am not sure how to program
it
to delete the new duplicate record. The code (which is for the
subform) is typed below:


Option Compare Database


Private Sub txtHolDate_AfterUpdate()


Me.txtFullName = Forms!frmEditStaff![FullName]


Dim rs As ADODB.Recordset
Dim sSQL As String


Set rs = New ADODB.Recordset


sSQL = "SELECT * FROM tblBookedHols WHERE [HolDate] = " &
Me.txtHolDate & _
" AND [FullName] = '" & Me.txtFullName & "'"
rs.Open sSQL, CurrentProject.Connection, adOpenStatic


If rs.RecordCount = 0 Then
'if recordcount = 0 there are no duplicates
Else
'else there are records with same data and an alert is given
MsgBox "This holiday has already been booked for " & Me.txtFullName,
vbOKOnly, "Duplicate Enrty"


'Here is where the code would go to delete the duplicate record


End If
rs.Close
Set rs = Nothing


End Sub


Thank you in advance!
 
Ad

Advertisements

B

benyod79 via AccessMonster.com

In your tblBookedHols, make FullName and HolDate the PrimaryKey. This
combination will not allow the same FullName and HolDate to be entered twice.

To make a multi-field PrimaryKey, hold the shift key down while selecting the
fields, then right-click and choose PrimaryKey.

If this doesn't fit your needs, let me know as there are other ways around
this too.




Hi all,

I've been trying to approach this problem several ways for some time
now and to no avail.

I am making a database that generates staff rotas. There is a button
you can click on the main rota form to bring up an "edit staff" form
(frmEditStaff) that includes a subform with a list of holidays that
the staff member has booked off in advance. The subform gets it's
data
from a table called "tblBookedHols" - which contains a unique ID
autonumber (the primary key - which isn't really used), a field
containing the staff member's name ("FullName"), a field for a
description of the holiday ([Description] - which may be left null)
and a field containing the holiday date ("HolDate").

When you enter the holiday date in the subform, the staff member's
name is automatically inserted into a hidden textbox that updates the
database.

I am trying to set it up so that if you type in a duplicate holiday
date for any one staff member then a message box will pop up
explaining this and delete the duplicate entry. I tried doing this
with a DLookup in the AfterUpdate event - which usually came up with
the "you cancelled the previous action error" so I tried using
recordsets instead (which I didn't want to do as I don't really
understand them!). I managed to come up with the following code but
this doesn't seem to do anything - no message boxes, no errors, no
nothing... I've also tried putting the code in the BeforeUpdate,
AfterUpdate, BeforeInsert and AfterInsert events which doesn't seem
to
make any difference.

As well as not getting the message box I am not sure how to program
it
to delete the new duplicate record. The code (which is for the
subform) is typed below:

Option Compare Database

Private Sub txtHolDate_AfterUpdate()

Me.txtFullName = Forms!frmEditStaff![FullName]

Dim rs As ADODB.Recordset
Dim sSQL As String

Set rs = New ADODB.Recordset

sSQL = "SELECT * FROM tblBookedHols WHERE [HolDate] = " &
Me.txtHolDate & _
" AND [FullName] = '" & Me.txtFullName & "'"
rs.Open sSQL, CurrentProject.Connection, adOpenStatic

If rs.RecordCount = 0 Then
'if recordcount = 0 there are no duplicates
Else
'else there are records with same data and an alert is given
MsgBox "This holiday has already been booked for " & Me.txtFullName,
vbOKOnly, "Duplicate Enrty"

'Here is where the code would go to delete the duplicate record

End If
rs.Close
Set rs = Nothing

End Sub

Thank you in advance!
 
Ad

Advertisements

O

omar.norton

In your tblBookedHols, make FullName and HolDate the PrimaryKey. This
combination will not allow the same FullName and HolDate to be entered twice.

To make a multi-field PrimaryKey, hold the shift key down while selecting the
fields, then right-click and choose PrimaryKey.

If this doesn't fit your needs, let me know as there are other ways around
this too.





omar.no[email protected] said:
I've been trying to approach this problem several ways for some time
now and to no avail.
I am making a database that generates staff rotas. There is a button
you can click on the main rota form to bring up an "edit staff" form
(frmEditStaff) that includes a subform with a list of holidays that
the staff member has booked off in advance. The subform gets it's
data
from a table called "tblBookedHols" - which contains a unique ID
autonumber (the primary key - which isn't really used), a field
containing the staff member's name ("FullName"), a field for a
description of the holiday ([Description] - which may be left null)
and a field containing the holiday date ("HolDate").
When you enter the holiday date in the subform, the staff member's
name is automatically inserted into a hidden textbox that updates the
database.
I am trying to set it up so that if you type in a duplicate holiday
date for any one staff member then a message box will pop up
explaining this and delete the duplicate entry. I tried doing this
with a DLookup in the AfterUpdate event - which usually came up with
the "you cancelled the previous action error" so I tried using
recordsets instead (which I didn't want to do as I don't really
understand them!). I managed to come up with the following code but
this doesn't seem to do anything - no message boxes, no errors, no
nothing... I've also tried putting the code in the BeforeUpdate,
AfterUpdate, BeforeInsert and AfterInsert events which doesn't seem
to
make any difference.
As well as not getting the message box I am not sure how to program
it
to delete the new duplicate record. The code (which is for the
subform) is typed below:
Option Compare Database
Private Sub txtHolDate_AfterUpdate()
Me.txtFullName = Forms!frmEditStaff![FullName]
Dim rs As ADODB.Recordset
Dim sSQL As String
Set rs = New ADODB.Recordset
sSQL = "SELECT * FROM tblBookedHols WHERE [HolDate] = " &
Me.txtHolDate & _
" AND [FullName] = '" & Me.txtFullName & "'"
rs.Open sSQL, CurrentProject.Connection, adOpenStatic
If rs.RecordCount = 0 Then
'if recordcount = 0 there are no duplicates
Else
'else there are records with same data and an alert is given
MsgBox "This holiday has already been booked for " & Me.txtFullName,
vbOKOnly, "Duplicate Enrty"
'Here is where the code would go to delete the duplicate record
End If
rs.Close
Set rs = Nothing
Thank you in advance!

That did it - thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top