auto generate filenumber based on number of records in that year

[amycclark]

I have a form [IUDATA]
I have a add record button.
I have a date field [DATEIN]
I have a text field [DRPNO]

If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.

I'd like the format of [DPRNO] to be "dpr YY-XXX"

Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.

So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.


Any thoughts?
Thanks soooo much in advance!
~Amy in cold NH

 

[jdraw]

I recommend you don't try to put too much intelligence in a code.

1 field 1 fact

see Database Principles

 

[amycclark]

I could have the user add "drp 14-" themselves then when they hit enter... how would I get Access to count the number of records with "14" in the [DPRNO] then append that number (+1) to the end of it?

So that I still end up with "drp 14-###".

I should state that this is an existing database from the late 80's and this [DPRNO] format has been used all of this time, it is just not automatic.

 

[pr2-eugin]

I should state that this is an existing database from the late 80's and this [DPRNO] format has been used all of this time, it is just not automatic.

So what have they been using since the 80's?

Apart from the fact that it bends the basic DB structure it also goes against the concept of independent data. By doing the way you have shown, you are storing calculated values, which again is not the normal way. IF you only want to display the values in Query you can do so by means of a function, but no need to store this information.

 

[David R]

Look into DCount if you must supply the number to them. Don't count on the numbers being exactly consecutive.

 

[pr2-eugin]

I am not sure if this means exactly what I think it means,

Don't count on the numbers being exactly consecutive.

Consider, the following data,

DPR 13-001
DPR 13-003
DPR 13-004
DPR 13-005
DPR 13-006

Based on a "twisted" DCount, the Next Number (provided the date selected was 31-Dec-2013) would be, DRP 13-006. So would this be a Duplicate, is this allowed? Or should it recognize it exists and add another 1 to it (loop until a unique value is found)?

 

[amycclark]

I feel I really do need to supply a number to them. This number becomes the "file number" for the project and is the main number used on all our commpunications, etc. So I don't see a way around that.

So since 1987 or there abouts the user goes in to the table first, looks to see what the last file number is, then goes into the form and when they are ready to add a new project they just type in text (i.e., dpr 14-009) but I find that sometimes people write drp or no space or a typo with the year, etc. Just thought automating it would eliminate some of that.

We are a State program with little to no IT support for Access so this leads me here once in a while.

Thanks. I'll reconsider making edits.
~Amy

 

[amycclark]

Paul, sorry missed your previous message.
I cannot have duplicates!

Meaning... if I had two projects that came in on the same day, the first one entered would be 006 and the next one entered would be 007.

 

[amycclark]

OK, here's what I came up with this morning.... it isn't working but I thought I would toss it out there to see if I should really abandon this atempt. Clearly I am not a programer. But any comments are appreciated. Thoughts as to why this does not work?:


Dim YEAR_COUNT As Integer
Dim MAX_NUM As Integer

If (Me![DATEIN]) = Not Null Then
YEAR_COUNT = DCount("[DATEIN]", "IUDATA", "year([DATEIN]) = " & Year(Me![DATEIN]))
If YEAR_COUNT = 0 Then
Me![DRRNO] = "idr " + Year([DATEIN]) + "-001"
Else
MAX_NUM = YEAR_COUNT + 1
Me![DPRNO] = "idr" + Year([DATEIN]) + "-" + Format$(MAX_NUM, "000")
End If
End If

 

[namliam]

You concatinate text with & not +, + is SQL Server speak, & is VBA/Access

...
Me![DRRNO] = "idr " & Year([DATEIN]) & "-001"
...
Me![DPRNO] = "idr" & Year([DATEIN]) & "-" & Format$(MAX_NUM, "000")
...

And please use the code tags when posting code, be carefull though, note the extra/missing space behind the IDR string

 

[nanscombe]

I have a configurable function that should achieve this for you. Rather than using DCount(), to count the records, it is based on DMax() so it looks at the largest existing value and increments it.

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String, Optional ByVal nisNumberOfZeros As Integer = 4) As Variant
    
    ' Function nextIdString() takes 3 / 4 parameters
    '  1 - (nisFieldName) The name of the field where the ID is found.
    '  2 - (nisTableName) The name of the table.
    '  3 - (nisPrefix) The prefix, if necessary, for the ID.
    '  4 - (nisNumberOfZeros) The number of zeroes, or numerals, to be used for the ID.

    ' nisPrefix & "0" gives you a default value if one is not found in the table
    nextIdString = Nz(DMax("[" & nisFieldName & "]", nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")

    ' Get next numerical value by looking at the highest value number after the prefix and adding 1
    nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1

    ' Create new ID string by concatenating the formated number to the prefix
    nextIdString = nisPrefix & Format(nextIdString, String(nisNumberOfZeros, "0"))
End Function

I have created, and attached, a demo showing how it could be used,

I have added code to the cmdAddRecord_Click() event to generate the next id.

Private Sub cmdAddRecord_Click()
Dim strPrefix As String
  
  ' If DATEIN is empty give a warning and exit
  If Len(Me.DATEIN & vbNullString) = 0 Then
    MsgBox "DATEIN should have a value", vbInformation + vbOKOnly
    Me.DATEIN.SetFocus
    Exit Sub
  End If
  
  ' If Me.DPRNO is empty generate next value
  If Len(Me.DPRNO & vbNullString) = 0 Then
  
    ' Create prefix for nextIdString() function
    strPrefix = "dpr " & Format(DATEIN, "YY") & "-" ' dpr YY-
    
    'Create next ID based the highest existing version of the prefix +1 with 3 digits (001 - 999) available. dpr YY-XXX
    Me.DPRNO = nextIdString("DPRNO", "IUDATA", strPrefix, 3)
  End If
End Sub

Result in table IUDATA:

ID	DATEIN		DRRNO	DPRNO
2	22/01/2014		dpr 14-001
3	23/01/2014		dpr 14-002
4	02/12/2013		dpr 13-001
5	03/01/2014		dpr 14-003

Have a look and see if it is of use.

 

[amycclark]

This works perfectly! Thank you so much for taking the time to show me the details too.
Thanks Nigel!!!!!!!!

 

[nanscombe]

That's Ok. I'm glad it works for you.