Form to check previous record and allow update based on that

[Csalge]

I have a subform in which employees enter the date and their excluded time for such day for instance:


3/14/16 excluded time is from 0730 - 1830 due to computer problems

I'm using a continuous form and I would like for the form to check if user has entered any data for the same day in previous records for the same time frame if so not allow them to update new record. I'm not sure how to do this :/

[June7]

Use VBA code, something like:

If Not IsNull(DLookup("fieldname", "tablename", "datefield=#" & Me.dateboxname & "# AND EmpID=" & Me.IDboxname & " AND <no idea about the time frame condition>")) Then
MsgBox "Data already entered."
End If

The real trick is figuring out what event to put code in.

[Csalge]

so awesome!!!! it worked!!! thank you so much!!!

[Csalge]

I thought it worked but no. for new entries for time excluded i want to check if in table for the same day same employee ID has input time values and if the new values falls within the time range then to prevent update. I get the error " run-time error '13' Type mismatch.

Right now I have the following:
If not isnull (Dlookup("fromtime" and "totime", "tbltime excluded request", "daterequested = #" & me.date_Requested & "# and fk_employeeID = " & me.fk_employeeID & " and me.from_time between#" & "fromtime &# and #" & totime & "#")) then
msgbox " Data already entered"
cancel=true
end if

My table fields are fromtime, totime, fk_employeeID and daterequested
my table is tbltime excluded request
myfields on the form are from_time, to_time, date_requested, fk_employeeID

[June7]

Your DLookup syntax is wrong.

The search field argument must be one field, actually doesn't matter which field in this case but use unique record ID field, assuming there is one.

Names with spaces must be enclosed in []. Advise not to use spaces or special characters/punctuation (underscore is exception). Better would be tblTImeExcludedRequest or tbl_Time_Excluded_Request.

"[tbltime excluded request]"

Time criteria improperly concatenated. Date/time range criteria can be tricky and having separate date and time values can be even trickier to get right. Should to_time criteria be involved?

If Not IsNull (Dlookup("ID", "[tbltime excluded request]", "daterequested = #" & me.date_Requested & "# and fk_employeeID = " & me.fk_employeeID & " and #" & me.from_time & "# BETWEEN fromtime AND totime")) Then

[Csalge]

thank you so much..it works!