Sequential Number on the basis of a cell value in Table

[harpreett.singhh@gmail.co]

Hi,



Thanks in advance for your views and replies.

I have a table called External Audit.

I want each record to get a unique sequential number on the basis of Type of Audit.

Type of Audit column has 3 options OB, INR, MINR to choose from

If i select type as OB then the notification number should start from 1 and carry on numbering it whenever I select the same value.

When I select different type eg: INR then it should again start number on the basis of type. INR1 INR2, INR3 etc.

Please advise.

PS: Is sequential number is the right word to use for this function ?

Regards

[June7]

This is a custom unique identifier. Common topic. Search forum. Here is one: https://www.accessforums.net/access/...nce-40410.html

How would this identifier be useful?

[trevor40]

Where are you storing the numer?
do you need 3 seperate fields, OB, INR, MINR each with thier own sequential numbers?

with string manipulation you could use 1 field, adding OB,INR or MINR to the result and save that, to get the max number from that strip the text off the text fron the field then get the max number?

[harpreett.singhh@gmail.co]

I want to store the number to a new field called notification number. unfortunately the links above are for year and other case. I think I have similar situation but could not get around the solution.

SO @trevor40, I have already a drop down combo box to select the type of the incident which is OB, INR, MINR. In my form I should be able to select one of those and a new auto sequential number on the basis of selection should appear.

For example if the previous OB number was OB1 then next time if I select OB again OB2 should appear or #2 which is second time I entered OB. Hope you have clear idea now !

[June7]

Of what use is this sequential id? The code provided can be adapted but why do you need this?

This sequential number can be generated when needed on a report. Textbox on report has RunningSum property. Organize report to group by Type of Incident and this sequence can be calculated within each group.

[harpreett.singhh@gmail.co]

So that at the end of the audit the auditor can see how many OB, INR, IMINR issued in a serial way. If anybody is looking for OB6 and OB8 there should be OB 7 as well (people might think). OB, INR and MINR have different impact and different cause but all the categories are from the same audit but separate purpose.

[June7]

Still not sure why the sequence is important. A count can be accomplished without assigning sequence number. This sequence can be generated when needed on report. It is reproducible as long as records are not deleted and the sort order is the same.

Otherwise will need code similar to the examples.

Is this a multi-user db? Must take measures to prevent multiple users from generating the same sequence ID.

[harpreett.singhh@gmail.co]

So bit of a history. I am trying to make an external audit database (one user).

In this audit database I have a form which states

Audit number,
Incident number (Auto Number - so that total number of actions required are accumulated),
type of Incident (OB, INR, MINR),
sequential number of Type of Incident
Actions 1, 2 ,3,,.........
Date Required.
Closed Date
Etc...

After I have entered all the data on these forms it will populate a register which states all of the data.

Our auditors wants types as continuous serial numbers if its OB then a serial number start from 1 is followed for all the OB if its for INR then serial number of INR maintained so that its easy to check cross the data.

otherwise the numbers will be jumbled and have to manually see which ones are INR and which ones are OB. this is easy way to understand how many OB are raised to date and what serial number is resolved and which is not.

Hope this clears the requirement.

[June7]

All of which can be managed in a properly constructed report.

Attempt code like the examples and when you encounter specific issue, post for analysis.

[harpreett.singhh@gmail.co]

Hi, I think we are missing something here. I want to put the number in the table generated from the form so that the number appears in every record.

Regards

[June7]

Yes, I understand that.

Requires code. Examples referenced. Adapt code for you requirements.