Restrict date if already exists.

[jsbarroso]

I have a main form which employees use to report their bi-weekly time. The main form is automatically assigned a [RecordID] and the employee must select their [Profile] from a combo box.
This main form contains (2) sub-forms. Both sub-forms use the same data source table.
The first sub-form is visible in the body of the main form and linked by the [RecordID] and employee [Profile]. The employee enters each date worked and hours for the reporting period.
The second sub-form is hidden and linked to the main form by the employee [Profile], basically keeping a running ledger of all the dates and hours reported by the employee.
What I would like to do is have a message box appear if the employee mistakenly enters date that has already been reported.
Any ideas how I can achieve this?
Thank you in advance.

 

[Mihail]

Is, somehow, useful that hidden subform ?

Use the BeforeUpdate event of the control in order to check the actual value against all other existent values.

or

define in index, unique, at table level in order to prevent duplicates records.

 

[jsbarroso]

I am using the follow code but, nothing happens when I enter a duplicate date

If DCount("*", "tblEmployeeTimeReportDetails", "[Reporting Date] = " & Me.Reporting_Date) > 0 Then
MsgBox "The date you have enter already exists. Please check date and try again.", vbOKOnly + vbExclamation, "Date already exists"
End If

 

[Mihail]

Try this:
If DCount("*", "tblEmployeeTimeReportDetails", "[Reporting Date] = " & Me.Reporting_Date.Value) > 0 Then ....

It is not the time to make economy to words.
Inform us where is this code ? Under what event ? Show us the entire procedure.

 

[bob fitz]

If Me.Reporting_Date is holding a date type value, perhaps you need to add hash marks. Something like:
If DCount("*", "tblEmployeeTimeReportDetails", "[Reporting Date] = #" & Me.Reporting_Date & "#") > 0 Then ....

 

[jsbarroso]

I have a main form which employees use to report their bi-weekly time. The main form is automatically assigned a [RecordID] and the employee must select their [Profile] from a combo box.

This main form contains (2) sub-forms. Both sub-forms use the same data source table.

The first sub-form is visible in the body of the main form and linked by the [RecordID] and employee [Profile], which the employee enters each date worked and hours for the reporting period.

The second sub-form is hidden and linked to the main form by the employee [Profile]; basically keeping a running ledger of all the hours reported by category and tracking what the employee has taken against accrued sick, personal and vacation.

What I would like to do is have a message box appear if the employee mistakenly enters a date they have already reported.

The following code is attached to the second sub-form on the BeforeUpdate event procedure, but it is still allowing me to enter duplicate dates.


Private Sub Form_BeforeUpdate(Cancel As Integer)
If DCount("*", "tblEmployeeTimeReportDetails", "[Reporting Date] = #" & Me.Reporting_Date & "#") > 0 Then
MsgBox "The date you have enter already exists. Please check the date and try again.", vbOKOnly + vbExclamation, "Date already exists"
End If
End Sub

Any idea’s where I am going wrong.

Thank you in advance.

 

[bob fitz]

Does the code actually fire? Try:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Msgbox "Code Fired"
Msgbox "Returned by DCount: " &  DCount("*", "tblEmployeeTimeReportDetails", "[Reporting Date] = #" & Me.Reporting_Date & "#")
If DCount("*", "tblEmployeeTimeReportDetails", "[Reporting Date] = #" & Me.Reporting_Date & "#") > 0 Then
MsgBox "The date you have enter already exists. Please check the date and try again.", vbOKOnly + vbExclamation, "Date already exists"
End If
End Sub

 

[jsbarroso]

Still allowing me to enter duplicate dates. It appears the code does even hire.

 

[bob fitz]

Still allowing me to enter duplicate dates. It appears the code does even hire.

Not sure what you mean by this.
If you do not get a message box that opens with the message "Code Fired" then the code is not running. Check that you have you set the Before Update of the form to [Event Procedure]
If I have understood correctly what you are trying to do with the DCount() function, then I think you will need to add more to the criteria. You will also need to add a line of code to cancel the form update
Cancel = True
but first we need to get the code to run.

 

[jsbarroso]

Sorry meant to say "Fire" and not Hired...

I was able to get the code to work. When I enter a duplicate date the message box appears just the way I want. But now have another problem. We have 35 employees using this same form to report their time for the same dates; if another employee enters a date before me I get the “Date already exists” message.

Each employee is identified by their [Profile] which is set as a Primary Key. So my thought was to create a query based on the employee profile and pointed the code as shown below to “qryEmployeeTimeTrackerDetail", in order to limit the function lookup table.

I am not sure if this is the best approach and really have not had a change to test.


Private Sub Form_BeforeUpdate(Cancel As Integer)

MsgBox "Returned by DCount: " & DCount("*", "qryEmployeeTimeTrackerDetail", "[Reporting Date] = #" & Me.Reporting_Date & "#")
If DCount("*", "qryEmployeeTimeTrackerDetail", "[Reporting Date] = #" & Me.Reporting_Date & "#") > 0 Then
MsgBox "The date you have entered already exists. Please check the date and try again.", vbOKOnly + vbExclamation, "Date already exists"
End If

 

[Mihail]

As I already said (post 2)

define in index, unique, at table level in order to prevent duplicates records.

 

[bob fitz]

Sorry meant to say "Fire" and not Hired..
I was able to get the code to work. When I enter a duplicate date the message box appears just the way I want. But now have another problem. We have 35 employees using this same form to report their time for the same dates; if another employee enters a date before me I get the “Date already exists” message.

As I said in my last post:

If I have understood correctly what you are trying to do with the DCount() function, then I think you will need to add more to the criteria. You will also need to add a line of code to cancel the form update
Cancel = True