Creating a sequential inquiry number (not Autonumber)

GopherUK

Active Member
Joined
Jan 23, 2009
Messages
473
Hi folks,

I am creating a new database for a client and I need a method to create an incrementing number for the number of the enquiry in the form of XX0001 etc.

I am not really sure to start with this as I have never done anything like this before. Am I best to use a domain function to count the current number of inquiries in the DB and have this automatically create the sequential number when the new record is saved?

Thanks in advance,

Gopher.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

Kreszch68

Active Member
Joined
Mar 10, 2011
Messages
409
This is how I would do this.
Basics, having a table (tInquiry) with fields InquiryID (long) and InquiryNumb (string).

Field InquiryID is just for doing the math, so it could be a hidden field on your form.
Supposing you have a command button on your form, the next code generates a new InquiryID which then is used to compose the InquiryNumb. And yes, after reading this, it should have been more logical to have the ID being the string value and the number being the number, but ok.

Code:
Private Sub cmd_SaveRec_Click()
Const sAlphaPrefix As String = "XX"
Const sNumbPrefix As String = "0000"
Const sTableName As String = "tInquiry"
Const sNumFldName As String = "InquiryID"
Dim lInqID As Long
With Me
    'Test if record is not yet numbered
    If Nz(Me.InquiryID.Value, "NoVal") = "NoVal" Then
        lInqID = Nz(DMax(sNumFldName, sTableName), 0) + 1
    Else
        lInqID = Me.InquiryID
    End If
    
    .InquiryID = lInqID
    .InquiryNumb = sAlphaPrefix & Right(sNumbPrefix & lInqID, 4)
End With
End Sub
 

GopherUK

Active Member
Joined
Jan 23, 2009
Messages
473
Thank you very much. I will implement that later on today and see how it goes.
 

GopherUK

Active Member
Joined
Jan 23, 2009
Messages
473
BTW I assume the number basically cant be displayed in the form until the record is saved as if the number is created when there are multiple users, it might attempt to input the same number twice? I think that is fairly obvious but I just wanted to clarify.
 

GopherUK

Active Member
Joined
Jan 23, 2009
Messages
473
BTW would is best practice when it comes to the number format of the incrementing number i.e. is it preferable to have it "padded" like 000102 or just simply 102? I understand there wouldn't be a whole hell of a lot of difference overall but just thought I would throw it out there.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
I think it's fair to say that you normally wouldn't want to display the number while the user is entering data - you want to assign that immediately before the update. If you did display the number, as you suspect, it might happen that someone else uses it to. If it makes a user feel better, you could display it anyway, but check again before the record is committed (though this could be even more confusing if it does change).

As far as formatting goes, you can format numbers any way you like. if the datatype in Access is numeric, it won't be stored with leading zeroes either way.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,918
Messages
5,627,620
Members
416,257
Latest member
salomon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top