Generating an error message for selective duplicates

[Guest]

Hi; I'm new to Access and this is my first major project, so please bear with
me.

I am creating a database for the school where I work which lets the office
staff enter changes in dismissal for students (e.g., they normally take the
bus but are going home by carpool on a particular day). I have set the date
and the students' names as primary keys, so a child can appear multiple times
as long as the date is different, and that part's working fine. When a user
finishes entering the data, they click on a button that automatically opens a
blank record so that accidentally overwriting the last one is avoided (these
are users with fairly basic skills so I need to make this as user-friendly as
possible).

However, if a parent calls and changes the instructions and the user
attempts to make the change by adding a new record, the duplicate error
message is displayed. I would like to have Access display a message when they
bring up the child's name in a new record, saying "There is already an entry
for Janie Doe for today; would you like to change it?" At that point they
could click on Yes and go to the existing record to change it, OR click
Cancel in case they have selected the wrong child.

 

[Guest]

You can do this in the AfterUpdate event of the controls bound to the fields
which hold the student's ID and the date. If the date is entered
automatically you only need it in the student's ID control, so the code for
its AfterUpdate event procedure would go like this (I've assumed the
StudentID is a number and that the Student Name is being shown in the usual
way in a combo box bound to the StudentID field but with its first column
hidden so only the Student name shows)

Dim rst As ADODB.Recordset
Dim strCriteria As String
Dim strMessage As String

If Me .NewRecord Then
If Not IsNull(Me.YourDateField) Then

strMessage = "There is already an entry for " & _
Me.StudentID.Column(1) & _
" for today; would you like to change it?"

strCriteria = "StudentID = " & Me.StudentID & _
" And YourDateField = #" & _
Format(Me.YourDateField),"mm/dd/yyyy") & "#"

Set rst = New ADODB.Recordset
rst.Open "YourTable", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTable

rst.Find strCriteria
If Not rst.EOF Then
If MsgBox(strMessage, vbYesNo + vbQuestion, "Warning") =
vbYes Then
Me.Undo
Me.Bookmark = rst.Bookmark
Else
Me.Undo
End If
End If
End If

If the date is not entered automatically then you'd have to do the same in
the date control's AfterUpdate event procedure, but test for:

If Not IsNull(Me.StudentID) Then

instead of testing for a Null date field.

Ken Sheridan
Stafford, England

 

[tina]

try adding the following event procedure to the form's Error event, as

Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3022 Then
Response = acDataErrContinue
If MsgBox("There is already an entry for " _
& "reference to child's name goes here, without quotes" _
& " for today; would you like to change it?", _
vbYesNo) = vbYes Then
Dim lngID As Long, dat As Date
lngID = Me!ChildIDFieldName
dat = Me!DateFieldName
Me.Undo
Me.Recordset.FindFirst "ChildIDFieldName = " & lngID _
& " And DateFieldName = #" & dat & "#"
End If
Else
Response = acDataErrDisplay
End If

End Sub

the above assumes that you're not actually storing the child's name in this
table, but rather the primary key value of the child's record from a
"master" table listing all the children. exactly how you get the child's
name for the messagbox will depend on your setup, but i'm guessing that
you're using a combo box to choose the child, and you can reference the
"name" column of the combo box to display the name in the message box text.
and, of course, you have to substitute the correct control names in the
code.

hth

 

[Guest]

Ken and Tina, thanks so much to both of you--it's working now! You are
both marvelous to have helped me out.

 

[tina]

you're welcome