Validation Rule Question

[alpine82]

I have a table that has items entered into it, each with a corresponding item #. I want on a form for the validation rule to be set so that they cannot enter an item number that is greater than the last record in the table. I could just say
[ItemNumber]<Number, but the table will always be changing in record count and therefore ItemNumbers. Is there anyway to lookup the last record and use its # as the validation rule, automatically on entry of the form. Or any other ideas are very welcome.

 

[dcx693]

I want on a form for the validation rule to be set so that they cannot enter an item number that is greater than the last record in the table.

What is your definition of "last" record in the table. Tables are not ordered. If you open a table view and happen to see a record in the bottom row, it's just a coincidence. You can't consistently hope to get that record in the same position unless you refer to something within the data itself, like an ID number, transaction number, or item number.

If you can do that, then you can use a DMax or a query to find the item number of that "last" record. I'd put some code into the form's before update event to check the number and make sure it's less than that in the last record.

 

[alpine82]

I have the table ordere by item number. So the highest item number is the last record in the table. And that last item number is what I need.

 

[alpine82]

I have anew question, I figured out a work around. Can I stop access from giving me error messages except for the MsgBox I programmed in? I made a MsgBox() in the on Error of a form. It works, but I also get like 3 error messages from Access like Primary Key cannot be null, and you cannot save this record at this time. Is there anyway to stop access from printing error messages to the screen except for the on that I programmed in?

 

[dcx693]

Yes, you can use error handling like I posted in this thread: Duplicates error message .

So if your table is ordered, great. You can use a DMax function or a query with VBA to find the biggest item number.