Control Validation

[Jason Lopez]

I am trying to add a validation to a form that I have. To maintain unique
course ID numbers, I have set the course ID number as primary key. The
instructor has the authority to create their own course number based on what
course numbers have not already been used. Over time, the course numbers
expire and become available again. But that is a seperate course number
that is also a primary key. Students can see the Course ID Number and
select the course from there. The course number is used internally to track
what courses have been offered, when and by whom.

On the form that I am designing, I am trying to create a control that will
validate the Course ID Number and (perhaps) give a recommendation if an
invalid one is entered. Essentially, the validation rule would compare the
requested number with a query of currently used Course ID Numbers. If the
rule finds that the number already exists, it would return an error and
recommend a number (perhaps the next number in line; or one or two away from
their desired). I have guessed that I could add the validation as an event
procedure for the control as an "On Exit" event. But, how would I code the
validation rule?

Jason Lopez

 

[Steve Schapel]

Jason,

I think the Before Update event is best.

Validation code could be like this...

If DCount("*","YourTable","[Course ID] = " & Me.Course_ID) > 0 Then
MsgBox "Course Number " & Me.Course_ID & " has already been
allocated!"
Cancel = True
End If
(assumes Course ID is a number data type)

Making a recommendation for an available number is a bit trickier. If
you can give specific examples of the data you have in this field, and
what sort of considerations in the recommended alternative, then it will
make it easier for someone to give more explicit advice.