Loop through records to auto-generate ID

[jrsarrat]

Hello! I am expanding my db to auto-assign unique ids to new records appended to a Datasheet form. The unique ids should be in sequence, retrieved from another database (EXT) that stores existing records (DMAX + 1). If the last record from EXT is 12345, then the first record on my from should be 12346 and so forth. How do I create a For Each statement that calls to EXT and retrieves the last record, then updates my records accordingly. The ADJ_ID is the field I want auto-updated on my datasheet form. The dbo_RM00101 table is the table that has the CUSTNMBR that is storing existing records.

This is what I have so far:

Private Sub Form_DblClick(Cancel As Integer)
Dim db As Database
Dim rst As DAO.Recordset
Dim ADJ_ID As String
Dim CLICK As Form_frmUserInputADJ1

For Each CLICK in ?
Last_ID = DMax("[CUSTNMBR]", "dbo_RM00101", "[CUSTNMBR] like 'AD000*'")


Set db = CurrentDb
Set rst = db.OpenRecordset("Select [CUSTNMBR] from dbo_RM00101 where [CUSTNMBR] like 'AD000*'", dbOpenSnapshot)
Do Until rst.EOF


next_ID = "AD000" & Right(Last_ID, 8) + 1
next_ID = Me.ADJ_ID.Value
This_ID = next_ID


rst.MoveNext


Loop
Set rst = Nothing

Set db = Nothing



End Sub

 

[Micron]

, the ID structure is a corporate decision that all business segments/units follow.

I'm not proposing that you change it - only that you change how it's used. If it comes from another system, it still doesn't matter. Say it comes from SAP

Stored (e.g. from SAP)	Prefix (new field)	Idnum (new field)	form/report/query view in db
AD1	AD	1	AD1
AD1	AD	2	AD1
AD1	AD	3	AD1
AD1	AD	4	AD1
AD1	AD	5	AD1
AD1	AD	6	AD1
AD1	AD	7	AD1
AD1	AD	8	AD1

Problem now is, if you said where you copy the data from I don't recall so that might not be applicable.
If not, then as I said it should be doable. I'm going to suggest that you copy/compact & repair & post somewhere. You can remove all but what's needed to fix this. You can hide data too if that helps. Then I can take a look and probably get it to work. If mgnmt says no upload, I understand, even though I have no interest in whatever your db is about. I have abt 10k posts at the linked site, so I think you'll find I'm above board if you review some of them. I could probably cobble together something at my end that accepts pasted data but I don't know what to make that look like, so the db would solve a lot. The dbo table is linked, so that won't come across, but I can make my own. Will that help?

 

[SalimSD]

Thank you for your help.