Validation Help Urgently Required Please

[Dominic84]

Hello

I am currently devloping an Access database for the company I work for. It will be used to store data from job cards and time cards. Many time cards being linked to one 'job card'

What I want to be able to do is, set it up so that when you enter a job number on the Time Card form it checks against the job numbers already in the Job Card table to see if it exists. If it doesn't you get an error box, if it does it lets you carry on and fill out the rest of the form. This is to stop people entering job card data on a non existant Job Number.

I've been playing about with this for a few hours now and niehter the book I have or the Help files seem to have the answer.

Advice would be much appreciated.

Thanks in advance

Dominic

 

[jimbrooking]

I suggest you use a combo box, not a text box. Make your combo box rowsource be a query that returns only valid job numbers, and have the "limit to list" property set to Yes. That way the user can enter only valid job numbers and you don't have to write any validation code!

The other issue you need to address is whether you will accept an entry with NO job number. If no Job Number is required, you're done. If you require a job number you can set the job number field in the underlying table so "Required" is Yes. Also, in the form's BeforeUpdate event, you should have something like

If IsNull(Me.cboJobNumber) then
MsgBox "Missing Job Number",,"Can't Save Record"
Cancel = -1
Exit Sub
End If

This code will present a reasonably informative error message, unlike the Access message you get without the code.

HTH--
Jim

 

[Dominic84]

Thanks that's sorted it out nicely.

Now that's done I've nearly got it finished, may run into a few more problems though...

Thanks again

Dom