Generating a Service Number Using VBA

[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.

I just realized that I placed this in the wrong forum, Sorry for the inconvenience.

 

[GUIDO22]

In order to do this I would suggest that you keep the date and the service number as seperate entities witrhin your table. In this way you can add the following code or similar to your second button :

Dim nVal as Variant

nVal = DLookup("Max([ServiceNo])", "Table1", "[TheDate]=Now()")
If IsNull(nVal) Then
nVal = 1
Else
nVal = nVal + 1
End If
MsgBox nVal

retrieving the max service number for the todays date and adding one to it!

 

[Pat Hartman]

A more straightforward expression is:

nVal = Nz(DMax("[ServiceNo]", "Table1", "[TheDate]=" & Date()), 0)
nVal = nVal + 1
MsgBox nVal

BTW - NOW() includes time of day and you won't get any hits by comparing TheDate to Now().