Month and Year Validation

[Jody]

Hi,

Since a full date is not recorded, my form has a MonthID field (Number
datatype) and a YearID field (also Number datatype). Both receive values via
a combo list.

I would like to have the form validate that the month and year combination
that are selected do not already exist in the underlying table.

Is there a DCount or DLookup statement or something else that can validate
both field values by checking the existing records to see if the combination
already exists within a record in the underlying table?

Any help would be greatly appreciated.

Thanks,
Jody

 

[Arvin Meyer [MVP]]

Try this example, substituting your own field and table names:

DLookUp("[Rate]","[tblRates]","[ID] = " & Form![ID] & " And [CompanyID] = "
& Form![CompanyID])

The above will only work if ID and CompanyID are numeric. If, for example,
ID were text, then you would need to add quotes:

DLookUp("[Rate]","[tblRates]","[ID] = """ & Form![ID] & """ And [CompanyID]
= " & Form![CompanyID])

 

[Jody]

Thanks Arvin. It worked!

Jody

Try this example, substituting your own field and table names:

DLookUp("[Rate]","[tblRates]","[ID] = " & Form![ID] & " And [CompanyID] = "
& Form![CompanyID])

The above will only work if ID and CompanyID are numeric. If, for example,
ID were text, then you would need to add quotes:

DLookUp("[Rate]","[tblRates]","[ID] = """ & Form![ID] & """ And [CompanyID]
= " & Form![CompanyID])
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,

Since a full date is not recorded, my form has a MonthID field (Number
datatype) and a YearID field (also Number datatype). Both receive values
via
a combo list.

I would like to have the form validate that the month and year combination
that are selected do not already exist in the underlying table.

Is there a DCount or DLookup statement or something else that can validate
both field values by checking the existing records to see if the
combination
already exists within a record in the underlying table?

Any help would be greatly appreciated.

Thanks,
Jody