One Record Per Day Per User - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

One Record Per Day Per User

Robert Sena

4 years ago

The following code is being used to limit the entry of one record per day per user. Only the current date is allowed. How can I revise to 1 record entry per day per user regardless of date?

'------------------------------------------------------------
' btnAdd_Click
'
'------------------------------------------------------------
Private Sub btnAdd_Click()
On Error GoTo btnAdd_Click_Err

Dim drEntered As Integer

drEntered = DCount("*", "DailyReportExistQ")

If drEntered > 0 Then
MsgBox txtCurUser + ", you have already entered a report for today", vbCritical, dbTitle
Exit Sub
End If

Me.AllowAdditions = True
Me.EmployeeID.Enabled = True

    On Error Resume Next
    DoCmd.GoToRecord , "", acNewRec
    Me.EmployeeID = curUserId
    Me.EmployeeID.Enabled = False
    Me.ProjectInformationID.SetFocus
    Me.btnSave.Enabled = True
    
    
btnAdd_Click_Exit:
    Exit Sub

btnAdd_Click_Err:
    MsgBox Error$
    Resume btnAdd_Click_Exit

End Sub

The query sql is as follows. It needs to be revised. Maybe the MAX function?

SELECT DailyWorkReportT.EmployeeID, DailyWorkReportT.DailyWorkReportDate
FROM DailyWorkReportT
WHERE (((DailyWorkReportT.EmployeeID)=GetUserID()) AND ((DailyWorkReportT.DailyWorkReportDate)=Date() or Date()-6));

Any guidance will be appreciated. Thanks

	Adam Schwanz @Reply 4 years ago
The date is in the where clause of the query. What are you trying to do though? If you take out the date clause there will always be records unless your deleting them after for some reason. The whole thing might need reworked depending on how you're setup

	Adam Schwanz @Reply 4 years ago
Unless you wanted to type in the date to select before you pushed the button or something. If you take out the date clause you should see all records ever

	Robert Sena OP @Reply 4 years ago
Ok. I see what you mean

	Robert Sena OP @Reply 4 years ago
If I remove the date clause will it still limit one record per user per specific date? I tried just entering the date as suggested but I get the message that I have already prepared a record for the specific date, no matter what date I insert.

Adam Schwanz @Reply

4 years ago

I'm assuming you have multiple records in there for every user, if you didn't you would have to be deleting them. So no the Where clause would find the UserID and limit it to them, but they are still going to have all the records they've ever had in there.

You need to be able to limit it to one day, so you could do like a textbox on the form and then do
AND ((DailyWorkReportT.DailyWorkReportDate)=Forms!FormName!FieldName

	Robert Sena OP @Reply 4 years ago
I think that is going to work. Thanks Adam!

	Scott Axton @Reply 4 years ago
You might check out the NZ Function video if that didn't work for you. It would be very similar to not allowing the email address shown in the extended cut. You would just have to have the criteria to determine if the report was there or not correct.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View