Text box restriction based on the values from two tables

[Shamli]

Hello Everyone
I have 2 tables. Holiday table (Holiday date, Holiday Desc, company checkboxes) and Employee Table (EmpID, EmpName, Company).
I am working on the timesheet management system.
what I am trying to do is

• Each contractor company has their own set of holidays.
• I have to limit the employees not to enter hours on a holiday based on the company they are working for.
• How should I link both the tables. Holiday and employee table (Employee table has the company information).
• I used the below query. but this worked for the holidays without any company specific. (In general). How should I change the query to make my requirement work.

Private Sub Monday_AfterUpdate()
Dim LResponse As Integer

 
If Monday.Value > 0 And Text284 = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "#") Then
LResponse = MsgBox("Please note this day is a holiday. Do you still want to charge on this day? ", vbYesNo, "CONTINUE")
If LResponse = vbYes Then
Me.Monday.SetFocus
Else
Me.Monday.Value = 0
End If
   End If
End Sub

Holiday Table:

[pbaldy]

The H & L aren't companies, are they? I'd expect a table with fields for each holiday/company combination, and you just add a company criteria to your code.

[June7]

The checkbox fields are a company - so company H and company L? Company H doesn't celebrate Christmas?

You need the appropriate company checkbox field in the DLookup criteria. This might mean you need to first do a lookup on the employee to determine the company identifier.

companyfield = some code to return "H" or "L"

DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "# AND " & companyfield)

[CJ_London]

include a column for the companyFK in the holiday table then by joining to the company table which in turn joins to the employee table you will get the relevant days. This does mean if you have 10 companies, the holiday dates will be allocated 10 times

perhaps more sophisticated you could identify holidays which are relevant to all companies by setting the companyFK to 0, then modify your query to bring those through as well i.e. a cartesian query (no joins) with a criteria of something like

WHERE (tblHolidays.companyFK=0 or tblHolidays.companyFK=tblCompanies.companyPK)

[Shamli]

Yes they are companies.
I have changed H and L to company1 and company2


Do you want me to change the table like this.

HolidayDate	HolidayDescription	Firms
1/1/2018	New Year's Day	Company1
1/15/2018	Birthday of Martin Luther King, Jr.	Company2
2/19/2018	Washington's Birthday	Company1
5/28/2018	Memorial Day	Company2
7/4/2018	Independence Day	Company1
9/3/2018	Labor Day	Company2
11/11/2018	Veterans Day	Company1
11/22/2018	Thanksgiving Day	Company2
12/25/2018	Christmas Day	Company1
12/24/2018	Christmas Eve	Company2
11/23/2018	Day after thanksgiving day	Company1
3/31/2018	Cesar Chavez	Company2
3/31/2018	Cesar Chavez	Company1
11/23/2018	Day after thanksgiving day	Company2

The H & L aren't companies, are they? I'd expect a table with fields for each holiday/company combination, and you just add a company criteria to your code.

[pbaldy]

That would be the normalized structure, and how I'd probably structure it. Ajax makes a suggestion on how to handle holidays everybody uses.

[Shamli]

Yes they are company names.

Company H doesn't celebrate Christmas?

Lol I am using a sample to explain the problem

The checkbox fields are a company - so company H and company L?

You need the appropriate company checkbox field in the DLookup criteria. This might mean you need to first do a lookup on the employee to determine the company identifier.

companyfield = some code to return "H" or "L"

DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "# AND " & companyfield)

So I have to first create a Dlookup to check for the company name associated to the Employee and then another dlookup for the holiday.
Is that right?

[June7]

Going with the current data structure, that's my suggestion.

[CJ_London]

if you are storing the companyfk/name in the employee table, you don't need to go to the company table to get the companyfk/name, you already have it. However dlookup code provided is missing a bit

this

….AND " & companyfield

should be

….AND Companyfield ='" & companyfield & "'"

(assumes company field is text and is named the same in both tables)

[June7]

Not with the company as yes/no type fields as shown in the original Holidays table. That's what I meant by 'current data structure'. If OP has changed the table then yes, my suggestion is not relevant.

The first lookup would be to Employees table or a query that joins Employees to Companies to get the company (depends what exactly is stored in Employees - companyID or companyName - and exactly how the fields are named).

[Shamli]

I tried the below code:

Private Sub Monday_AfterUpdate()
Dim LResponse As Integer
 
If Monday.Value > 0 And Text284 = DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "#") And Text295 = DLookup("[FIRMS]", "Holiday_T") Then
LResponse = MsgBox("Please note this day is a holiday. Do you still want to charge on this day? ", vbYesNo, "CONTINUE")
If LResponse = vbYes Then
Me.Monday.SetFocus
Else
Me.Monday.Value = 0
End If
   End If
End Sub

But its not showing any warning message.
Text295 stores the company name from the employee Table.
I think there is a problem with the Dlookup.
Its not looking up the firm name. How can I change the query?
And the company name is a text and it is same in both the tables.

[June7]

Meaningful names for textboxes can be helpful. This second lookup will just find random record since there is no search criteria. But why a second lookup instead of additional criteria in the first?

If DLookup does not find match it returns Null.

If Monday.Value > 0 And Not IsNull(DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "# And Firms='" & Text295 & "'")) Then

[Shamli]

Thank you for your reply.
Yeah. later realized to add an another criteria.
But I am getting a type mismatch.(Run Time Error 13)
Both the fields are in the Short text data type.
Why is this error?
Is the syntax wrong?

Meaningful names for textboxes can be helpful. This second lookup will just find random record since there is no search criteria. But why a second lookup instead of additional criteria in the first?

If DLookup does not find match it returns Null.

If Monday.Value > 0 And Not IsNull(DLookup("[HolidayDate]", "Holiday_T", "HolidayDate = #" & Format(Nz(Forms!Create_New_Entry_F!Text284, 0), "Short Date") & "# And Firms='" & Text295 & "'")) Then

[June7]

Syntax looks fine. Are you sure HolidayDate is a date/time type and Firms is text type?

[Shamli]

yes .
They are as mentioned.

Syntax looks fine. Are you sure HolidayDate is a date/time type and Firms is text type?