We help IT Professionals succeed at work.

Previous record

Derek Brown
Derek Brown asked
on
222 Views
Last Modified: 2012-06-21
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?
Comment
Watch Question

CERTIFIED EXPERT

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

Commented:
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

Author

Commented:
Sorry for delay in replying. Took a holiday

I get the following with gbartley
 Codeerror message

Author

Commented:
The problem could be that there are no records in the first request?
Gerry BartleySoftware Engineer

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

Author

Commented:
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

Author

Commented:
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.
Software Engineer
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 BartleySoftware Engineer

Commented:
It will yes.

als315,s solution is very tasty.

Author

Commented:
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 BartleySoftware Engineer

Commented:
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
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.