MS Access 2007 Form to Form data validation (data type: date)

Question

I think I have the way forward but require guidance on the best method for what I would like to do:

MS Access 2007 Database

I have 2 tables for which I have created 2 user input forms.

Form 1. Site location: The field of concern within this form is: Date site found (using date data type)

Form 2. Site Detail: The field of concern within the form is: Work date start (using date data type)

This issue is I am trying to create a form to form validation rule that defines the following:

The work date start must be >= than the date site found.

I have tried various validation rules all of which do not work such as:

[work date start]>=[Date Site Found]

=[work Location]![Date Site Found]

The one rule I did get to work required both forms to be open but I did not want that.

However is this the solution:

willir (MIS) post re: Two form validation using <=Dlookup

Or is the best way to write code in a before / after update event ? Or is there another way?

Mike Sherrill 'Cat Recall' Mike Sherrill 'Cat Recall' · Accepted Answer · 2012-10-14T19:23:44

The simplest approach is to look at the value that's in the table, not a value that's in another form. DLookup() lets you look at a value that's in another table.

In your case, the DLookup() itself might look something like this, if your table is named "Site location".

DLookup("[Date Site Found]", "[Site Location]", "Your WHERE clause goes here")

You access data in SQL databases by specifying a table, column, and key. Your WHERE clause supplies the key. There's no way to tell from your description what that's supposed to look like, but I'll make a guess.

'Using a literal number. Only useful for testing.
DLookup("[Date Site Found]", "[Site Location]", "[Site ID] = 32")

'Referring to another control on the same form.
DLookup("[Date Site Found]", "[Site Location]", "[Site ID] = Form![Site ID"])

The linked article show several variations of the WHERE clause that each return the same result. This kind of WHERE clause doesn't include the word where. That trips up a lot of people.

Using the DLookup

Your event code should look something like this. (I think this should be in the [work start date] control's BeforeUpdate event.)

Dim date_found as Date
date_found = DLookup("[Date Site Found]", "[Site Location]", "[Site ID] = Form![Site ID"])
If ([work date start] >= date_found) Then
    'Good. Do nothing.
Else
    'Bad [work start date]. Throw the user a dialog box, and cancel this event.
    'Left as an exercise for the OP.
End If

MS Access 2007 Form to Form data validation (data type: date)

1 Answers