Generating a Service Number

[Demigod]

Hi,
I'm trying to make a database where a Date and a Service Number is entered into a form.

I have 2 buttons, one to automatically enter the Current Date and the second to automatically enter the Service Number.

The service number is incrimented by 1 each time and reset for every day.

eg.

Date Service Number
January 1, 2005 1
January 1, 2005 2
January 1, 2005 3
January 2, 2005 1
January 2, 2005 2
January 5, 2005 1
January 5, 2005 2

I already have the button that enters the Current Date. However, I am having trouble with the second button.

How can I code the button so that it searches the table for the Current Date and finds the record with the greatest service number for that date? After this is done, I want to create code that will automatically enter the next service number into the Service Number Textbox on the form.

eg. Using the Example Table Above
The date January 5, 2005 is entered into the Date Text Box and the Search Next Service Number button is pressed. The number '3' should show up in the Service Number Textbox

Sorry if its hard to understand, but thanks anyways.

 

[Bat17]

You can use the Dmax function to fin the next numer

=Nz(DMax("[Service Number]", "[YourTable]l", "YourDate=#" & Date & "#"), 0) + 1

Peter