Need advice for form opening options

[Indigo]

Hello, I need some advice / critique on some code I have written that opens a form. I wrote it so that if there is a current record (i.e. same date and shift) then to go to that record. However, if there is no record, then go to a new record.

It seemed to be working fine for the past two weeks, but suddenly last night I had a user who was somehow able to create 5 new records for yesterday's date and shift. This is the code for the OnClick event from my main form to the input form the user enters their notes into:

Private Sub cmdNotes_Click()
    Dim HoldShiftDate As Date
    Dim HoldShift As String
 
    HoldShiftDate = Forms!frmSecurity!ShiftDate.Value
    HoldShift = Forms!frmSecurity!Shift
 
    If DCount("NotesID", "Notes", "Shift ='" & HoldShift & "'" & _
        " AND ShiftDate = #" & HoldShiftDate & "#") > 0 Then
 
        DoCmd.OpenForm "frmQtrNotes", acNormal, , "Shift ='" & HoldShift & "'" & _
        " AND ShiftDate = #" & HoldShiftDate & "#"
            Else
        DoCmd.OpenForm "frmQtrNotes", acNormal
        DoCmd.GoToRecord , "frmQtrNotes", acNewRec
        Forms!frmQtrNotes!InputDate = HoldShiftDate
        Forms!frmQtrNotes!Shift = HoldShift
            End If
 
End Sub

Any advice anyone can offer would be appreciated. Thank you.

 

[jdraw]

I had a user who was somehow able to create 5 new records for yesterday's date and shift.

So you went to your table and found the 5 records??
Do you have a primary key on that table?
Do you have a composite unique index on the combination of Shift and ShiftDate?
Do you have some other means of adding records to the table?

 

[Indigo]

Yes, I went to the table and found 5 records
Yes, I have a primary key on that table
I'm sorry, but I do not know what you mean by: "a composite unique index on the combination of Shift and ShiftDate"
No, there are no other means to add records to this table other than the bound form I created

 

[jdraw]

Where in Canada are you?

For unique composite index

You need some mechanism to ensure the records are unique. Perhaps our PK is not adequate. With proper PK and use of unique composite index you should not have duplicates/replicates.

 

[Indigo]

Thank you for that link. I will give it a try....just one thing, I don't want the users to get the duplicate index Error message - do you think the combination of my code and the unique composite index for the date and shift fields that all will be well? Or should I include some error trapping that is a little more user friendly than the standard duplicate index Error message?

 

[jdraw]

Always include error trapping.
You can check the error message that you receive during testing (I think 3022), then set your error handling to trap it and display your own friendlier message.

 

[arnelgp]

I wrote it so that if there is a current record (i.e. same date and shift) then to go to that record. However, if there is no record, then go to a new record.

do you mean user must be in edit mode when your Dcount > 0? do you know he can still add record even in edit mode. if you only want an edit only form when the above condition is true, make your form AllowAddition = false.