Data validation between tables

[daveUK]

Hi guys I've already searched the forums and can't find anything to solve my problem.

I'm designing at d'base for the university reprographics dept and have already come unstuck

I have 2 tables Cost_Code and Job set up with a 1-n relationship.
I have a form based on the Job table, with a field to allow the users to enter their Cost Code. The Cost Code needs to be valid, before an other details can be entered, or before moving onto another record. This can either be via the AfterUpdate event or a cmd button, it doesn't really matter which.

I've tried to use the DLookup function within Access, but no joy.

Any help greatly appreciated.

Dave

 

[Fizzio]

A simpler way is to use a combo to provide a list of valid cost_codes but if confidentiality is an issue then you'll have to stick to the textbox.

In the BeforeUpdate event of the cost_code control you could try

[b]If cost code is number[/b]
If DCount("IDField","Cost_Code","[Cost Code] = " & Forms!NameofForm!CostCodeControl) =1 then

[b] if cost code is text[/b]
If DCount("IDField","Cost_Code","[Cost Code] = '" & Forms!NameofForm!CostCodeControl & "'") =1 then

else msgbox"Please enter a valid cost code", vbexclamation,"Invalid Cost Code"
cancel = true
end if

 

[daveUK]

Thanks Fizzio, the code worked a treat

I meant to say in my original message that there were over 1000 cost codes, so a combo box wasn't an option.

Dave

 

[Rich]

Thanks Fizzio, the code worked a treat

I meant to say in my original message that there were over 1000 cost codes, so a combo box wasn't an option.

Dave

Why not? It's more efficient than DLookUp

 

[Fizzio]

More efficient and no need for the validation code if you have autocomplete turned on. I nearly always use combos even with several thousand records.
Aggregate functions such as DLookup, Dmax, Dcount etc are relatively slow in comparison to combos and stored queryDefs and the more you use, the slower it gets!