Auto Assign ID to new records

[swadson]

Hello,

I am new to Access and an trying to create a macro where in which an new incremental id is assigned to new records that are being added to the table. Here is my table

DGID Element_Name Element_Desc
----- ------------- -------------
DG001 abcd1 abcs1


DG002 abdds2 abds2
.
.
.

NULL acbds.. abcd...


As show above when new records are added to the table they would not have a DGID assigned. I need to write a query where the max DGID is taken and assigned to the new records. When there are multiple new records the macro should iterate by incremental assignment of the DGID

To do so I have created a reference table pre populated with DGID till 3500. and also have written a query to update this reference table which has a second column called Element_Present if there is a matching record found in the main table

Query as below.

UPDATE tbl_DGID_Reference INNER JOIN tbl_Repository ON tbl_DGID_Reference.Data_Governance_ID = tbl_Repository.Data_Governance_ID SET tbl_DGID_Reference.Element_Present = IIf([tbl_DGID_Reference]![Data_Governance_ID]=[tbl_Repository]![Data_Governance_ID],"Yes","No");

If a field is present then Element_Present will have "Yes" populated, otherwise its "No"

Now, I need to write a macros to iterate the records by getting the Min(DGID) where Element_Present = "No" and iterate the above query every time a new DGID is populated.


Please let me know how to write a macro to acheive this.

Thanks!

[June7]

This ID cannot have gaps in sequence? Are there multiple users?

Review:
https://www.accessforums.net/forms/a...ing-23329.html

[swadson]

June7, Thanks for replying. The ID can have gaps and for now there is only one user. The below link that you have given is based on a form. I don't have any forms in this scenario. I load all the new records from a Excel load template, which basically a linked file from a share point location.

Thank you!

This ID cannot have gaps in sequence? Are there multiple users?

Review:
https://www.accessforums.net/forms/a...ing-23329.html

[June7]

Yes, the sample code generates a number for a new record, which could be done without form.

If you don't care about possible gaps in sequence, use the Autonumber datatype to generate unique numbers. The value can be formatted to look like your DGxxx structure. Otherwise, require code in a general module to update the newly imported records with unique ID.

[swadson]

Well, using autonumber would actually not work as let say if the element/record is deleted, the entire list is re numbered. Once a record has been assigned with a number, that should not change for me.

Can you provide the code in the general module that you have mentioned? I am not familiar with macros in Access and would appriciate it if you can send me the code.

Thanks!!

Yes, the sample code generates a number for a new record, which could be done without form.

If you don't care about possible gaps in sequence, use the Autonumber datatype to generate unique numbers. The value can be formatted to look like your DGxxx structure. Otherwise, require code in a general module to update the newly imported records with unique ID.

[June7]

Deleting record with an autonumber unique ID would not 'renumber' the other records. They would retain their numbers and the result will be a 'gap' in the sequence. This is the simplest solution for your situation.

I have already given you sample code you can use as a guide to write your own. It is not a macro, it is VBA.

[swadson]

How can I format the autonumber?

Please let me know.

Thank you!

Deleting record with an autonumber unique ID would not 'renumber' the other records. They would retain their numbers and the result will be a 'gap' in the sequence. This is the simplest solution for your situation.

I have already given you sample code you can use as a guide to write your own. It is not a macro, it is VBA.

[June7]

Here's how to set Format property in table or in textbox. I don't do this in tables, only in textboxes. http://www.databasedev.co.uk/add_prefix.html

Format function http://msdn.microsoft.com/en-us/libr...(v=VS.80).aspx

[swadson]

Got it!! Thank you!