Lookup all the values of another table

[AceBK]

Good Day,

I have a rather interesting problem/question. I have been working in Access 2010 and by no means would I call myself an expert. I have two different tables, one is called 'JobsList' and the other one is called 'StatHolidays'. On the Jobslist form, I have a field that requires a ship date, however, I don't want to allow the user to select the dates listed in the StatHolidays table. Is there a way that when a user picks a date that is listed in StatHolidays that a pop up box will say "Sorry, this date is Christmas, do not choose this as a Ship date". The 'StatHolidays' table has a field for a date and for a description of the holiday.

Thank you,
AceBK

 

[Mile-O]

In the BeforeUpdate event, something like:

If DCount("Holiday","tblHolidays","Holiday = #" & Format(txtHoliday, "mm/dd/yyyy") & "#") <> 0 Then
    MsgBox "Sorry, that's a holiday.", vbExclamation + vbOkOnly, "Holiday!"
    Cancel = True
End If

Changing table and field names to suit, of course. If you have to let the user know it's Christmas, too, then the DLookup() domain aggregate function will also be needed.

 

[AceBK]

Syntax error with "Holiday = #". Can you possibly explain the different elements of the Dcount. What is "Holiday" represent? is that the field from "Holidays" table where the date is in? Same with "Holiday = #" is that the same name as the beginning of the Dcount statement?

 

[AceBK]

Wow, I was playing around with it and got it to work. Thank you very much. The "Holiday = " needed to me the name of the field that I put the expression in.

 

[AceBK]

Is there anyway you can explain the DLookup() function to me

 

[jdraw]

AceBK,

Glad you got it working.
For reference, you can find the proper syntax and examples for most Access functions at
TechOnTheNet

 

[Mile-O]

Can you possibly explain the different elements of the Dcount.

One of the most underused features of tools is the Help function which you can call up by pressing the F1. Put the curson on the DCount function in the VBA window, press F1, and there you go, big explanation and examples.

What is "Holiday" represent? is that the field from "Holidays" table where the date is in? Same with "Holiday = #" is that the same name as the beginning of the Dcount statement?

As I said, change tables and field names to suit.

So, in more generic terms.

DCount("FieldName", "TableOrQueryName", "CriteriaField = #DateValue#")

With your info, all we have is the table name, so whatever your fields are:

DCount("FieldName", "StatHolidays", "CriteriaField = #DateValue#")

 

[Mile-O]

Ah, you solved it while I was replying. Anyway, DLookup is in the Help files too.