Verification of Date and User ID only seems to check Date? Adds extra blank record...

[Heatshiver]

I have a piece of code that is meant to check both the Date and User ID to see if the User ID has already used that date. I noticed it doesn't work properly as I attempted to use a date that only a different user had already used, but the code came back with the message it should only show if the current user had used that date. This tells me that the User ID is not being properly checked along with the date. Here is the code:

Dim rs As Object

Set rs = CurrentDb.OpenRecordset("tblGenSum")

If rs.EOF And rs.BOF Then
MsgBox "No Record"
End If

Do While Not rs.EOF
If rs!Days = [Forms]![frmSTOTAct].Days And rs!UserID = [Forms]![frmSTOTAct].UserID Then
DoCmd.RunCommand acCmdUndo
MsgBox "This User ID has already used this date."
[Forms]![frmSTOTAct]![UserID] = ""
Me.Days.Value = ""
Exit Do
End If
rs.MoveNext
Loop
Set rs = Nothing

The other problem I am having is that a blank record is then created. I should not that I believe this is due to the subform... I have tried:

DoCmd.RunCommand acCmdUndo

This will delete the record in question entirely, and still add a blank record.

I have also tried:

rs.MoveLast
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord


But neither works and I still end up with a blank record without a Date or User ID. Any help would be much appreciated!

 

[boblarson]

1. Where is this code (what event)?

2. You can check to see if the user id has already used the date by using the Form's Before Update event and a DCount:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   If DCount("*", "tblGenSum", "[UserID]=" & Me.UserID & " And [Days]=" & Me.Days) > 0 Then
      Cancel = True
      Me.Undo
   End If
End If

 

[Heatshiver]

Thanks for taking a look.

The code is in the After Update event.

I tried your code in the Before Update but it gives me a run-time error: 3464 "Data type mismatch in criteria expression"

 

[boblarson]

You have to use the Before Update event. If you use the After Update event it is too late. So are you sure you used the FORM's Before Update event and not the control's before update event? Also, Are user iD and Days numbers or are any of them stored as text in the table? If text then they would need quotes added in the criteria.

 

[Heatshiver]

I did originally use the field's Before Update, so I tried the form, but it to gives me an Access error.

I noticed that doing it this way also has my combobox not work anymore for previous dates. It also gives errors for my User ID fields.

I can upload a small version of it so you can see what I am dealing with...?