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!
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!