Avatar of Derek Brown
Derek Brown
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Previous record

Hi all

I am entering data into a field on a continuos form.
I am trying to create a reference number in a field that is basically the two digits from the current month and an incrementing number starting at 1.  The increment must start at 1 for each new month and year.

This is to automate a File reference number so that when a customer is entering supplier purchase invoices they like to file the purchase invoice in monthly order. this reference number is then phisically hand written on each purchase invoice and is a number that allows users to find purchase invoices quickly in the filing cabinet. So all purchase invoices will have a ref for example a June invoice might be   06 12. That will be placed as the 12th invoice in the file for June. But I also need to make sure that when incrementing that the increment does not pick up last years June and increment from that largest number.

Whats the simplest way to do this?
Microsoft Access

Avatar of undefined
Last Comment
Derek Brown

8/22/2022 - Mon
als315

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

How about this

on enter event of id field on form


Private Sub ID_Enter()

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

End Sub

Open 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

Open in new window


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

ASKER
Sorry for delay in replying. Took a holiday

I get the following with gbartley
 Codeerror message
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Derek Brown

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

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

ASKER
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

 My error
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Derek Brown

ASKER
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.
ASKER CERTIFIED SOLUTION
Gerry Bartley

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Derek Brown

ASKER
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?
Gerry Bartley

It will yes.

als315,s solution is very tasty.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Derek Brown

ASKER
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
Gerry Bartley

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

Open in new window



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

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Derek Brown

ASKER
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