Validating a form's field

[TomJ58]

Hi All,

I have created a form that adds a new record to a table. One of the fields in the form is call Job Number. The new number entered in this field must be greater than or equal to the last number used. The number does not have to be I have come up with a simple query to determine the maximum number last used.

SELECT Max(tblCostarJobMaster.JobNumber) AS MaxJob
FROM tblCostarJobMaster;

My delima is how to use this query to validate the number being entered into the form's field. Any help would be appreciated.

 

[hkimpact]

Not sure if this would be of any help but maybe you should read a bit more on this subject. I don't know enough to help you with the sql you seek, but check the bottom portion of this link out.

http://www.techonthenet.com/sql/max.php

Also why not use the autonumber? I'm no pro either, but how could your job number field be validated if the number (I'm guessing this is your Primary Key) is already being used?

 

[TomJ58]

The job number is not the primary key because the number originates in a MS SQL database. The SQL database is part of a web based design that allowed duplicate job numbers. Also, the job numbers entered in the web designed database were not always sequential entered in the past, but are still in use today.

Secondly, the query produces the results I am looking for. I am trying to figure out how to use the query as a validation rule.

 

[TomJ58]

I found the solution. In the form's validation rule for Access 2010 use the following format:

=>DMax("[field name]","[table name]")