Previous record

Do you have date field in your table? You can get last invoice number with dmax if you can filter month and year.

How about this

on enter event of id field on form

Private Sub ID_Enter()

If ID.Text = "" Then ID.Text = NextInvoiceID

End Sub

Select allOpen in new window

Function NextInvoiceID() As String

' Substitute your table for tmp
Dim DB As Database, Rs As Recordset
Dim sql$, m$, y$, ID$
Set DB = CurrentDb
m = Format(Month(Now), " 0#")
y = Format(Year(Now), "##")
    sql = "select top 1 ID from tmp where id like '" & y & m & "*' order by id desc"

Set Rs = DB.OpenRecordset(sql)

If Rs.RecordCount = 1 Then
    NextInvoiceID = y & m & Format(Val(Right(Rs("ID"), 2)) + 1, " 0#")
    Else: NextInvoiceID = y & m & " 01"
End If

Select allOpen in new window

Output
ID                      Data
2012 05 01      1st
2012 05 02      2nd
2012 05 03      3rd
2012 05 04      4th

Sorry for delay in replying. Took a holiday

I get the following with gbartley
 

The problem could be that there are no records in the first request?

I called the table tmp in my sql. You need to put in the proper table name and column name to suit your DB

Yes you even put it in the code. Sorry.

I can now see how that will work but in UK we write dates as Day Month Year and the fields date is short date format and looks like this 01/05/12. What I need is output month only and incremented number I.e. 05 06  or 05 202 for May and the incremented number. I spent some time as you can see trying to fix it myself but really cocked it up. Can you help, again please? will this work if we calculate mm/yyyy and show only mm. I can see that without showing the year we may get last years May increment

 

Hope this helps

The table is PInvoice
The date field to find relevent date is PInvoiceDate
and the field that holds the incremented filelocation is FileLocation

So if the Purchase Invoice Date is 01/04/12 then the File location reference number will be 04 01 for the first invoice and so on. I guess its:
Check in the PInvoice table for all previous invoices with the month number from the PInvoiceDate of the current record and set the FileLocation to the month part and an increment of +1 over the highest number. God I do make thing hard for myself.

Hi I changed
m = Format(Month(Now), " 0#")
for  m = Format(Month(PInvoiceDate), " 0#")
and bingo you're a genious. Thank you so much. I've been on it all day

Will it still work when we get to 3 digits of increment?

It will yes.

als315,s solution is very tasty.

Thank you both. Fabulous results. I used GBartley becuse I could add the following but you are right als315 is also a pritty solution. If I understood how it worked i would have used it. Does IsDate mean if there is a date or if it is a date format? and why do we use Me.FileLocation rarther than just FileLocation?
 I just modified gbartley to avoid unnessassary Zeros. But it looks clumbsy

Function NextInvoiceID() As String
Dim Limit As Single
Dim DB As Database, Rs As Recordset
Dim sql$, m$, ID$
Set DB = CurrentDb
m = Format(month(PInvoiceDate), " 0#")

sql = "select top 1 FileLocation from PInvoices where FileLocation like '" & m & "*' and year(PInvoiceDate)=" & Year(Now) & " order by FileLocation desc"

Set Rs = DB.OpenRecordset(sql)
If Format(Val(right(Rs("FileLocation"), 3)) + 1, " 00#") <= 9 Then
If Rs.RecordCount = 1 Then
    NextInvoiceID = m & Format(Val(right(Rs("FileLocation"), 1)) + 1, " #")
    Else: NextInvoiceID = m & " 1"
End If
End If
If Format(Val(right(Rs("FileLocation"), 3)) + 1, " 00#") > 9 And Format(Val(right(Rs("FileLocation"), 3)) + 1, " 00#") <= 99 Then
If Rs.RecordCount = 1 Then
    NextInvoiceID = m & Format(Val(right(Rs("FileLocation"), 2)) + 1, " 0#")
    Else: NextInvoiceID = m & " 01"
End If
End If
If Format(Val(right(Rs("FileLocation"), 3)) + 1, " 00#") > 99 And Format(Val(right(Rs("FileLocation"), 3)) + 1, " 00#") <= 999 Then
If Rs.RecordCount = 1 Then
    NextInvoiceID = m & Format(Val(right(Rs("FileLocation"), 3)) + 1, " 00#")
    Else: NextInvoiceID = m & " 001"
End If
End If
End Function

Thank you both again

Just a few comments

I'll let als315 explain his code if he wishes

m = Format(month(PInvoiceDate), " 0#")' This won't work because PInvoiceDate is not set
It needs to be the current month
m = Format(Month(Now), " 0#")

Later you're checking field values without first checking if a record was returned. You're also checking record count several times. Wrap the lot in a single
If Rs.RecordCount = 1 Then
.................
...........
end if


Also instead of checking for 1 2 or 3 digits why not keep it simple and allow for 3 digits or more if you need i.e. add the leading zeros using format.

Function NextInvoiceID() As String

'
Dim DB As Database, Rs As Recordset
Dim sql$, m$, ID$
Dim IDFormat As String, Digits As Integer
IDFormat = " 00#" ' Set the format
Digits = Len(IDFormat) - 1 
Set DB = CurrentDb
m = Format(Month(Now), " 0#") ' Get the current month here

sql = "select top 1 FileLocation from PInvoice where FileLocation like '" & m & "*' and year(PInvoiceDate)=" & Year(Now) & " order by FileLocation desc"

Set Rs = DB.OpenRecordset(sql)

If Rs.RecordCount = 1 Then
    NextInvoiceID = m & Format(Val(Right(Rs("FileLocation"), Digits)) + 1, IDFormat)
    Else: NextInvoiceID = m & Format(1, IDFormat)
End If

End Function

Select allOpen in new window

This will give the following
05 001
05 099
05-199

DatabaseDek:
Isdate is true, when your field contain valid date (for example, if you enter 31/02/12 it will be false).
Me in VBA means current form (report). It is better to declare variables to avoid possible errors. In many cases code will work, but very often it can help to find errors.

Thank you again both.

I am not sure why the m = Format(month(PInvoiceDate) works but it does. The reason I cannot use current month is because frequently Purchase Invoices come in early in the month following the month of purchase. So I will be entering Invoices dated the previous month. These are typically filed in the month of purchase folders not the month of receipt. I still want it to add the correct FileLocation reference despite which month (and year for January) it is dated. And that works Great. (Haven't checked January yet). Thanks for the reminder about If Rs.RecordCount = 1. The leading Zeros is just me. The thought of my clients having to hand write on every invoice one or two unnecessary zeros up to 99 times every month seems unreasonable. If it was just for computer generated reasons the yes that would be correct but the user has to hand write the FileLocation number on every Purchase Invoice by hand before filing.

Thanks for the explanation als315 and for that beautiful sample DB