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]

Should be as simple as
Last_ID = DMax("[CUSTNMBR]", "dbo_RM00101", "[CUSTNMBR] like 'AD000*'") +1
and putting that in an update query. Or maybe you should explain the outcome you want rather than asking how to do something that might not be necessary, because I don't see the need for a loop or a recordset.

 

[jrsarrat]

Thank you for your reply. Good point! The outcome I’d like is for my code to pull the last ID entered into the RM00101 tbl, and using the next ID (Dmax + 1) in sequence, update my data sheet form, looping through the new records and applying that next ID + 1 for each new record. So, the first record on my form would get Last_ID = DMax("[CUSTNMBR]", "dbo_RM00101", "[CUSTNMBR] like 'AD000*'") +1, and every subsequent record on my form would be the next number in the iteration (Last_ID + 1; Last_ID + 2; Last_ID + 3). I think I need to use FOR EACH?

 

[Micron]

You're saying that you get the max value from another db table, then if in your form you have 6 records, you want 6 increments of that number? I suppose it doesn't matter if now the last value in your form is way ahead of the DMax value from the source table, so that if you run this again, it will repeat the same 6 incremented values? OK, assuming you handle that somehow, I still don't see the need for a loop. Just run an update query against the form's underlying recordsource then requery the form. You can do all of that in code (including the updating sql statement), or create a stored query that gets form values as criteria if need be and run that from your code event.

 

[jrsarrat]

You're saying that you get the max value from another db table, then if in your form you have 6 records, you want 6 increments of that number? I suppose it doesn't matter if now the last value in your form is way ahead of the DMax value from the source table, so that if you run this again, it will repeat the same 6 incremented values? OK, assuming you handle that somehow, I still don't see the need for a loop. Just run an update query against the form's underlying recordsource then requery the form. You can do all of that in code (including the updating sql statement), or create a stored query that gets form values as criteria if need be and run that from your code event.

This is my conundrum—I have not handled that part. I created a stored query that returns the last record from the other db (which is linked to my db), but I have not coded my form and incorporated the query yet to apply those ids. I am at an impasse. I’ll keep digging. Thanks!

 

[Micron]

If you remain stuck, post a file copy somewhere and I'll probably take a look. Post instruction here if you can, pm if you can't for privacy reasons.
Most of tomorrow will be a write-off for me though.

 

[jrsarrat]

If you remain stuck, post a file copy somewhere and I'll probably take a look. Post instruction here if you can, pm if you can't for privacy reasons.
Most of tomorrow will be a write-off for me though.

Thank you so much! Got to get you while I can

Instructions:

Below, is the linked database that stores all ids. Using dbo_RM00101, I want to retrieve the last entry—Dmax

I am using the below qry to retrieve the last id entered into RM00101. Right now, it’s a Select Statement.

SELECT TOP 1 dbo_RM00101.CUSTNMBR AS CUSTNMBR
FROM dbo_RM00101
WHERE (((dbo_RM00101.CUSTNMBR) Like "AD*"))
ORDER BY dbo_RM00101.CUSTNMBR DESC;



My form is named below, which is set to Datasheet view using VBA.

My data is:

	Jilliane	Sar	Jilliane Sar			(813)543-4324		(813)543-4324
	Jessica	Jones	Jessica Jones			(813)543-4325		(813)543-4325
	Mary	Sue	Mary Sue			(813)543-4326		(813)543-4326
	Jeff	Gonk	Jeff Gonk			(813)543-4327		(813)543-4327

I manually append this to the form (Datasheet):

My code (wishing upon a star) is set to iterate through each row and add a value to the ADJ_ID. So, if the last id entered in RM00101 is AD12345678, then I want these 4 rows to be allocated as follows:

AD12345679

AD12345680

AD12345681

AD12345682



Below, is the result of my code when I double click on the datasheet (the Event I am using is On Dbl Click). The query is pulling the last entry from RM00101, but it is not looping through the recordset.

Here’s my code:

P.S. I know I have written code to add + 1 to ADJ_ID in each record, but at the very least, my records should update with the same id, the one you see in the snippet, right?

Private Sub Form_DblClick(Cancel As Integer)





Set db = CurrentDb

Set rs = db.OpenRecordset("qryLast_ADJ_ID", dbOpenDynaset, dbSeeChanges)





Do Until rs.EOF



Forms!frmUserInputADJ1.Form!ADJ_ID = rs("CUSTNMBR")

rs.MoveNext

Loop









'Dim Last_ID, Next_ID, ADJ_ID





'Last_ID = DMax("[CUSTNMBR]", "dbo_RM00101", "[CUSTNMBR] like 'AD000*'")



'For Each Last_ID In Next_ID

'If IsNull(Me.ADJ_ID) Then _

'Last_ID = Last_ID + 1

'Me.ADJ_ID = Last_ID

'Exit For

'Next



End Sub

Before the query path, I used the method below to get the last id entered. I had to parse the id this way in order to get the system to increase by one due to the data type.

'Pick last ADJ record from PMI database, and enter next in sequence in Validation db
'seq_id = DMax("[CUSTNMBR]", "dbo_RM00101", "[CUSTNMBR] like 'AD000*'")
'Me!ADJ_ID.Value = "AD000" & Right(seq_id, 10) + 1
or
''Me!ADJ_ID.Value = "AD" & IsNumeric(Right(seq_id, 9)) + 1

 

[welshgasman]

As Micron is away for today, I will try and help.

I would forget about the query, easier to use DMax().
Use that to get your last ID. Set that to lngID
Within a loop for the form recordsetclone
Add 1 to lngID
Set the recordsetclone file Adj_ID to that value
At end of loop run an update query with the current value of lngID to reset that table.

 

[welshgasman]

Set the recordsetclone file Adj_ID to that value

That should be

Set the recordsetclone field Adj_ID to that value

but this site limits the time to make edits.

In a multi user environment, this could get tricky and you might need to do all this within one transaction, even if you use a recordset to lock the record out from other users until you complete your transaction.

 

[jrsarrat]

Thank you for your help. Unfortunately, I keep running into errors with the formation of my code. What am I missing? Also, should I be using a different Event to trigger this code? I am using the dblclick on the ADJ field that requires the update. Before, the Event was on the Form. I am second-guessing myself about everything since I can't seem to pull this code together. Not to mention, I keep getting a Run-time error.

Thanks for your help. My current code is below:

Private Sub ADJ_ID_DblClick(Cancel As Integer)


Dim ADJ_ID As Field
Dim rst As Recordset

IngID = DMax("[CUSTNMBR]", "dbo_RM00101", "[CUSTNMBR] like 'AD000*'")


Set IngID = Me.RecordsetClone

rst.MoveFirst

Do Until rst.EOF
Me.ADJ_ID.Value = "AD000" & Right(IngID, 10) + 1
rst.MoveNext

Loop
rst.Close





End Sub