Solved: Default value set to max value based on a criteria

[nepotist]

Hello all I need some help here.

I have add new project form and update project form.( and both these forms have a sub form that is table 2)

the main form is based on table 1 which provides all the project information.
well my update form works fine as I provide the user to select a project from a drop down list and run a query and relate it to the form.

In the add new project form the user types in year and number and based on some mathematical calculation the ID is obtained and is and should always be unique.
IN the table one we can have unique number for each year like year 2008 can have 1-500 numbers(no number repeates twice) but they can repeate again for yr 2009 .

in the add new form i want a default value of the number text box to be next available number for that year

i Tried
default value = Dmax(Dlookup("Number","Projects","Year"=frmNew!Year.value)) but that dosent work.

Table 1 looks like this

Year Number ID
2008 1 2008001
2008 2 2008002
2009 1 2009001

I want the default value for the txtbox to be 2 when the year is 2009

[OBP]

I use VBA and a Recordset to do this.
This is the code, it is slightly more complicated than yours, you wouldn't need the Dealer Code and Month.

Dim year As String, month As String, count As Integer, recount As Integer, rs As Object, zeroes As String, callno As String
Dim SQL As String
zeroes = "000000": year = Format(Date, "yyyy"): month = Format(Date, "mm")
SQL = "SELECT Que_CallNumber.* " & _
"FROM Que_CallNumber " & _
"WHERE DealerCode = " & Me.Dealercode
Set rs = CurrentDb.OpenRecordset(SQL)
If rs.RecordCount <> 0 Then
rs.MoveLast
rs.MoveFirst
End If
recount = rs.RecordCount
If recount = 0 Then
callno = "000001"
Else
callno = Str(recount + 1)
callno = Right(callno, (Len(callno) - 1))
End If
If Len(callno) < Len(zeroes) Then callno = Left(zeroes, Len(zeroes) - Len(callno)) & callno
Me.CallNumber = Me.Dealercode & year & month & callno

[nepotist]

Sorry for the late reply.. I was sick

OBP I thought of doing that but felt like there is some other better way than that.
Was there any thing wrong with my DMAX function?
and another thing is that right now I am working on database tht was created by some one else and till this point the the user used to assign number to the project , there are few records where the numbers are noit consecutive. and that might cause a problem when I use the count function.

[OBP]

I don't know if there was anything wrong with the Dmax, I just don't use it.
You can actually pull up the last Record and take the last Value form that if you prefer.

[nepotist]

hmm. Thankyou OBP.. i will go ahead with that method then