Question Auto generated number (1 Viewer)

Maclain · Jul 1, 2013

Good afternoon all,

We have a large split database with 3 users on a single site. We will soon be increasing this to 6 users across 2 sites.

One of the fields in the main table is "report number" where a line is given a unique number. Not every line is given this number, as it depends on the finished status of the product.

The numbers are currently hand written onto a sheet as they are used, which obviously avoids duplicates. This however will be near on impossible once the database is shared across 2 sites.

the format of the number is 00/0000 where the first two digits are the year, and the last four increase sequentially.

Is there a reasonably easy way to incorporate this into the database itself, where a number can be generated as required, as apposed to every time?

Many thanks in advance.

CJ_London · Jul 1, 2013

Your definition of unique is flawed since you are allowing multiple instances of null so I would recommend you reconsidering your numbering options.

You haven't said whether the incremental numbers return to 0 for a new year howver the simple answer would be to keep the year and incremental parts in separate fields and to use a subquery or domain function to determine the maximum number - something like

nextnum=dmax("incNum","myTable")+1

or

nextnum=dmax("incNum","myTable","yrNum=" & curYrNum)+1

There has been a recent thread on this subject - tried to find it but without success, and there are more complex considerations - for example if two people do this lookup at the same time there is a risk of duplication - hopefully those posters will see this thread and point you in the right direction.

nanscombe · Jul 1, 2013

I imagine that the values will either be empty or be a unique value once they are filled in.

You could stick a button next to the field to auto-generate the next number in sequence when pressed.

I see you are using the format 00/0000. That will give you up to 9,999 entries a year. Will that still be enough once the second site is added?

Would each site be likely to create more that 4999 entries a year?

If not you could make it so that site 1 creates entries yy/0000 - yy/4999 and site 2 creates yy/5000 - yy/9999. That would be one way to avoid creating duplicate numbers.

Code:

Public Function getNextId()
Dim strPrefix as String, strDefaultNumber as String
  strPrefix = Format(Now(), "yy") & "/"
  strDefaultNumber = 0
' strDefaultNumber = 5000 ' Alternate starting number for other site

  getNextId = NZ(DMAX("yourID", "yourTable", "yourID Like '" & strPrefix & "*'"), strPrefix & strDefaultNumber)
' strPrefix & strDefaultNumber gives you a default value if one is not found in the table

  getNextId = Val(Mid(getNextId, len(strPrefix) +1) +1 ' get next numerical value

  getNextId = strPrefix & Format(getNextId, "0000") ' create next string Id

End Function

If the business was agreeable you could adapt the numbering to include the site number, yy/ss/0000. I use ss as a two digit site number as that would future-proof you with up to 99 sites.

The other thing to consider would be any supporting data; buildings, people, other materiel etc. If you have more than one database there are chances for people to create duplicates of them as well.

Whenever I've built databases that were to be used in more than one place I made sure that there were three fields which appeared in all the tables:

tableNameId (Long, though never Autonumber, or Text)- The ID of the record
deleted (Boolean) - A way of marking records as deleted without physically deleting them
dateStamp (DateTime) - The date and time of the last update to the record.

With those three fields I was able to swap data between the sites and update the records to the latest values available.

I used a deleted field because if a record were physically deleted from one database it could accidentally be restored from another copy of the database.

As long as the deleted record had a later datestamp it would never re-appear.

nanscombe · Jul 2, 2013

On another thread I have come up with a slightly different version ...

Code:

Private Sub yourButton_Click ()
Dim strPrefix as String
  strPrefix = Format(Now(), "yy") & "/"
  if Len(Me.[yourField] & vbNullString) = 0 then Me.[yourField] = getNextId (strPrefix)
End Sub

Public Function getNextId(byval gniPrefix as String) AS Variant

  getNextId = NZ(DMAX("yourID", "yourTable", "yourID Like '" & gniPrefix & "*'"), gniPrefix & "0")
' gniPrefix & "0" gives you a default value if one is not found in the table

  getNextId = Val(Mid(getNextId, len(gniPrefix) +1) +1 ' get next numerical value

  getNextId = gniPrefix & Format(getNextId, "0000") ' create next string Id

End Function

Maclain · Jul 2, 2013

Afternoon all, and many thanks for the replies!

I think I had better explain better...

Here's how our database looks:

The numbering system isn't entirely as I described before. Yes there can be a null value, or any other text entered. However, what I would like is a more automated way of generating numbers as required.

I think what has been described in earlier posts is almost exactly what I'm after.

At the bottom of our main form (above) we have "mass WIP" and "serial number quick find". Ideally next to that I'd like the last used report number to be entered, with a button next to that when pressed, would generate the next number in the sequence.

Of course with multi user, the next number in the sequence may not be the number after the one displayed on the users screen. So how this would be tackled I'm not entirely sure?

The process would be when a record requires a new report number, the generate button would be pressed and a new number would be displayed, this would be added to another table so the next user to press the button wouldn't receive a duplicate?

The "functions" detailed shown above seem to be on the correct lines? although I'd like a more detailed explanation on how functions work, and how to implement them.

Excuse my ignorance, as my knowledge of access is forever increasing!

nanscombe · Jul 2, 2013

After a bit of playing around I've changed the code to make it more flexible and included a demo of how it could be used with a Command button.

I've renamed it to nextIdString().

Code:

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String) As Variant

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

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

    nextIdString = nisPrefix & Format(nextIdString, "0000") ' create next string Id
    ' Create new ID string by concatenating the formated number to the prefix
End Function

It now takes 3 parameters.
1) nisFieldName - This is the field in the table where it is to use for it's value.
2) nisTableName - This is the field in the table where it is to use.
3) nisPrefix - This is the prefix that will be placed at the start of the ID string.

In this example it is handed a client ID followed by a "/" as the prefix.

Code:

Private Sub cmdGenId_Click()
Dim strPrefix As String
    If Len(Me.cboClientNo & vbNullString) = 0 Then
        
        Me.cboClientNo.SetFocus
        MsgBox "Please choose a client Number", vbCritical + vbOKOnly
        Me.cboClientNo.Dropdown
        
    Else
    
      strPrefix = Format(Me.cboClientNo, "00") & "/"
      If Len(Me.ID & vbNullString) = 0 Then Me.ID = nextIdString("ID", "tblMyData", strPrefix)
    
  End If
  
End Sub

Feel free to have a poke around.

Maclain · Jul 3, 2013

Morning Nanscombe,

thanks for taking the time with me on this one.

I've added a new field to the badly named table for this use.

the table is called "Job Register and report Log" and the new field is called "generaterptnumber"

I've now created a new module using the code you supplied earlier, this code is:

Code:

[COLOR=black][FONT=Verdana]Public Function getNextId()[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Dim strPrefix As String, strDefaultNumber As String[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]strPrefix = Format(Now(), "yy") & "/"[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]strDefaultNumber = 0[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]' strDefaultNumber = 5000 ' Alternate starting number for other site[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]getNextId = Nz(DMax("generaterptnumber", "Job Register and Report Log", "generateprtnumber Like '" & strPrefix & "*'"), strPrefix & strDefaultNumber)[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]' strPrefix & strDefaultNumber gives you a default value if one is not found in the table[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]getNextId = Val(Mid(getNextId, len(strPrefix) +1)) +1 ' get next numerical value[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]getNextId = strPrefix & Format(getNextId, "0000") ' create next string Id[/FONT][/COLOR]
 
[COLOR=black][FONT=Verdana]End Function[/FONT][/COLOR]

I've used this as I don't need the client drop down and it must be prefixed with the current year.

So now I have a module that will find the max value of “generaterptnumber” and add 1 to this value, and prefix this with yy / ?

Now I’ve got this field added to the footer of the form, how would I get a button to generate the next number?

Note: This has raised a number of questions in my head:

1. As the generate field is in the footer of the form, how will it know which record to add the last number generate to?
2. Assuming the field needs to be in a new table (which is I suspect how this should be done) how can I create a relationship to the main table in order to add the field to the form, or should I use a sub form?

nanscombe · Jul 3, 2013

You are quite correct. Since you are using a continuous form and the field is in the footer it won't know where to put the value.

Personally I prefer to use a single form

Anyway, am I correct in thinking that you want this number to go in the "Report No" field of your form?

I would tackle it in one of three ways:

1) Create a small button, with a caption of "...", beside the "Report No" field which will appear on each record. This could be used to place the value in "Report No".

Code:

Public Sub yourButton_Click()
' If character 3 is not / then generate number
  if Mid(Me.yourFieldName & vbNullString,3,1) <> "/" Then Me.yourFieldName = getNextId()
End Sub

Or

2) Create some code in the double-click event of the "Report No" field itself.

Code:

Public Sub yourFieldName_DoubleClick()
' If character 3 is not / then generate number
  if Mid(Me.yourFieldName & vbNullString,3,1) <> "/" Then Me.yourFieldName = getNextId()
End Sub

Or

3) Leave the field and button in the footer and generate the ID in an unbound textbox then copy and paste it to the required field.

Code:

Public Sub yourButton_Click()
  Me.yourUnboundTextBox = getNextId()
End Sub

In the line of code below generaterptnumber should be the field in the table which is bound to the text box, not the name of the text box, where you want the generated Id to go. As it is using the prefix of "yy/" it will ignore any other values in the table that don't match.

Code:

getNextId = Nz(DMax("[b]generaterptnumber[/b]", "Job Register and Report Log", "[b]generateprtnumber[/b] Like '" & strPrefix & "*'"), strPrefix & strDefaultNumber)

Maclain · Jul 4, 2013

Your correct this number will be going in the report number field. However this could be duplicated across many lines, ie when multiple serial numbers are on the same report.

Also there could be many other variants in the report number field, such as "repair" or "Cut out" so it can't be restricted to just the report number format.

So, I've got a new unbound text box in the footer "txtgenrpt" and command button "cmdgenrpt".

I've altered the code in module "getNextId" to look at the report number field already in the main table, as you said it ignores anything that doesn't match the format yy/?

So this line now reads

Code:

 getNextId = Nz(DMax("Report No", "Job Register and Report Log", "Report No Like '" & strPrefix & "*'"), strPrefix & strDefaultNumber)

However, when I hit the cmdgenrpt button I get an error:

compile error: Expected variable or procedure, not module

Debugger has highlighted:

Code:

Private Sub cmdgenrpt_Click()
Me.txtgenrpt = getNextId()
End Sub

As a side note, wouldn't this method increase the possibility of two people entering the same report number as the time taken to create the values increases? Would reducing the refresh time of the database limit these chances? Would having this field in a separate table it mean it could be set to no duplicates so this wouldn't happen?

nanscombe · Jul 4, 2013

Yes. When I tried out live I was getting that debug error as well, which is one of the reasons I changed it in the other database.

Take a look at the attached database and you will see three example forms:

frmExample01-CommandButton

A button with '...' on each record to generate the ID. It will also save the record if Ok'ed by the user.

frmExample02-DoubleClick

The ID is generated by double-clicking the Report No field. It will also save the record if Ok'ed by the user.

frmExample03-CopyPaste

The ID is generated in a text box in the footer then copy and pasted to the correct field. This won't save the record automatically so could be vulnerable to duplicates until such time as the record is saved.

Maclain · Jul 4, 2013

Excellent!

I'd like to use example 1.

I've adapted the module and created the command button. I;ve created the event as below:

Code:

Private Sub Command269_Click()
addNewId
End Sub
Private Sub addNewId()
Dim strPrefix As String, varResponse As Variant
If Mid(Me.ReportNo & vbNullString, 3, 1) = "/" Then
    MsgBox "This Report No has already been generated", vbInformation + vbOKOnly
    Exit Sub
End If
strPrefix = Format(Now(), "yy") & "/"
Me.ReportNo = nextIdString("Report No", "Job Register and Report Log", strPrefix)
varResponse = MsgBox("'" & Me.ReportNo & "' generated." & vbCrLf & "Save record", vbQuestion + vbOKCancel)
If varResponse = vbOK Then
    DoCmd.RunCommand acCmdSaveRecord
Else
    SendKeys "{ESC}"
End If
End Sub

The only alteration is "ReportNo" to "Report No", however, when I click the button I get the following error:
Compile Error: Method or data member not found

the highlighted area is .ReportNo in If Mid(Me.ReportNo & vbNullString, 3, 1) = "/" Then

CJ_London · Jul 4, 2013

From

The only alteration is "ReportNo" to "Report No"

and

Mid(Me.ReportNo & vbNullString, 3, 1)

implies you haven't made all the changes

Shouldn't this be me.[Report no]?

Maclain · Jul 4, 2013

I thought

Code:

Me.ReportNo

relates to

Code:

Me.ReportNo = nextIdString("Report No", "Job Register and Report Log", strPrefix)

as apposed to the filed name?

CJ_London · Jul 4, 2013

Apologies, I thought your form was using [Job Register and Report Log] as a recordsource - if so it will need to be changed here

Maclain · Jul 4, 2013

The table "Job Register and Report log" is the record source for the table, however if I change the field "Report No" to "ReportNo" in the table wont that make a mess of all my other related stuff?

CJ_London · Jul 4, 2013

me.reportno is referring to a field in your form - the Compile Error: Method or data member not found is saying it can't find it.

So I'm suggesting if your recordsource for the form includes the [Job Register and Report Log] table and you are looking for a value in this table called [Report No] then this is also the name on your form so also needs to be changed.

The only other thing I have noticed is

nextIdString("Report No", "Job Register and Report Log", strPrefix)

I'm not familiar with this function, but it is normal practice to include square brackets when there are spaces involved. It may be the function does this for you, but be prepared for another error here.

Using spaces in table and field names is a really bad idea, the amount of time spent debugging is criminal - current case in point

nanscombe · Jul 4, 2013

Code, and forms, changed to reflect "Report No" rather than ReportNo. Mostly just a sprinkling of "[" and "]" around the names.

This is one reason why names with spaces in them are generally frowned upon, because it makes a bit more difficult.

It's not until you start writing code for yourself that you come to realise why. :banghead:

Maclain · Jul 4, 2013

Yeah I know about spaces and correct table naming.

I've inherited this database as you can probably guess. It's fun...

Maclain · Jul 4, 2013

Works a charm! Many thanks!

Next one.. The Job No field is a unique number for each record, again using the format yy/00000

I'd like this to be generated on entry. I've adapted the module code:

Code:

Public Function 
 
nextJobString(ByVal njsFieldName As String, ByVal njsTableName As String, ByVal njsPrefix As String) As Variant
 
nextJobString = Nz(DMax("[" & njsFieldName & "]", njsTableName, "[" & njsFieldName & "] Like '" & njsPrefix & "*'"), njsPrefix & "0")
 
' nisPrefix & "0" gives you a default value if one is not found in the table
 
nextJobString = Val(Mid(nextJobString, Len(njsPrefix) + 1)) + 1
 
' Get next numerical value by looking at the highest value number after the prefix and adding 1
 
nextJobString = njsPrefix & Format(nextJobString, "00000") ' create next string Id
 
' Create new ID string by concatenating the formated number to te prefix
End Function

but do not need the msg box confirmation. Ideally I'd like the next number to be generated on entry to the field, and the record saved on exit of "customer" field as this is the only other required field. As I understand it will error if the on exit function is set to save record if not all required fields are completed?

Assuming the module code above is correct (I assumed nisfieldname could be changed to njsfieldname ?) I now need to add on enter event to the Job No field on the form to create the value.

I'm now struggling with cutting down the event code from the command buttom to an on enter code for the "Job No" field.

Code:

Private Sub Job_No_Enter()
addNewJob
End Sub
Private Sub addNewJob()
Dim strPrefix As String
strPrefix = Format(Now(), "yy") & "/"
Me.[Job No] = nextJobString("Job No", "Job Register and Report Log", strPrefix)
End Sub

nanscombe · Jul 4, 2013

How about ...

Code:

Private Sub Customer_AfterUpdate()
Dim strPrefix As String, varResponse As Variant
strPrefix = Format(Now(), "yy") & "/"

If Len(Me.[Job No] & vbNullString) = 0 Then
    Me.[Job No] = nextIdString("Job No", "Job Register and Report Log", strPrefix)
    DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

The function nextIdString was made to be re-usable just by passing it different parameters.

After the user chooses a Customer the code will look to see if there is a "Job No". If there isn't one then it will create one and save the record.

If you set the Tab Stop property of "Job No" to False it won't automatically tab into that field.

You could also make the cursor go to Customer field each time you enter a record.

Code:

Form_Current()
Me.Customer.SetFocus
End Sub

Question Auto generated number (1 Viewer)

Registered User.

Super Moderator

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Registered User.

Attachments

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

Attachments

Registered User.

Registered User.

Registered User.

Similar threads

Users who are viewing this thread