Display Record with Highest Number

[Lloyd]

I have a form to create new records in a grants table.
The form contains Key fields for three tables; grantee,
grant program and the grant number. The grant numbering
varies between the programs, but new grants generally
increment the last number by one.

I want to write code which, when one fills in and exits
the grant program field, the largest grant number in that
grant program is displayed in a textbox on the form. That
would allow the user to manually enter the next number.

I'm not clear how to begin on this piece. Anyone have a
suggestion about a likely way to proceed?

Thanks, Lloyd

 

[PC Datasheet]

Put the following code in the AfterUpdate event of your grant program field:

Me!NameOfGrantNumberTextbox = _
DMax("[GrantNumber]","GrantNumberTable","[GrantProgram] = '" &
Forms!NameOfCurrentForm!NameOfGrantProgramTextbox)