# 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

Micron said:


> 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

Micron said:


> 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:






JillianeSarJilliane Sar(813)543-4324(813)543-4324JessicaJonesJessica Jones(813)543-4325(813)543-4325MarySueMary Sue(813)543-4326(813)543-4326JeffGonkJeff 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


----------



## 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

If you're going to use _object_ variables, you have to SET them first. If I can, I'll review all this stuff tomorrow - but -  please use code tags (vba icon on posting toolbar) in order to maintain indentation and readability. At this point, not sure why you want to bother with a recordset, but for me, that's later because at this point I have no idea what you'd set it to. Perhaps research how to create DAO recordsets in vba for now if you don't know the answer to that.


----------



## welshgasman

Well, you should be using code tags to start with when posting on any site.

You need to set rst to the recordsetclone, as that is the recordset object?

I am not sure if you also need an rst.Update after you have changed any values, which is normally used on recordsets, but I have not used an update on a recordset of a clone. So you would need to test.

You also need to write back the last value to your table?, else you will start with the same number again.

Try and think what the code is doing, as what you have written there is just nonsense. You set lngID, (which you have not declared? (that tells me you do not have Option Explicit at the top of every module, which you should have?  ) to the value of the DMAX() then attempt to overrwrite it with something else, with nothing using it in between?

Setting a variable to a value does not explicitly mean you use the word Set, just that the variable is set to a particular value.


----------



## Micron

What is the recordsource for this form?
	

	
	
		
		

		
			






Is it this? If so, what is it and what is its name AND can you manually edit it as a test?


----------



## jrsarrat

Micron said:


> What is the recordsource for this form?
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> Is it this? If so, what is it and what is its name AND can you manually edit it as a test?


The recordsource is tblCarePointADJ1.

The Excel sheet is information that I am manually appending to my form-frmUserInputADJ1.

I do not want to clone records from the recordsource.  I want to clone the last record from another linked table and add it to the form.  From there, I want the code to auto-generate the next several IDs for the records you see there on the form.


----------



## Micron

I think I get it now. You're copying from a spreadsheet and paste/appending into a datasheet form whose recordsource is a table.
That means you have x new records without ADJ_ID values in that field. You should test and confirm by opening the table after the paste append and report back.

In that case, if I said run an update query that updates tblCarePoint.ADJ_ID to DMax value from that other field WHERE tblCarePointADJ1.ADJ_ID Is Null would that make sense? That's basically what I said in post 2 - I just didn't have the details to make a focused suggestion. If after the append you have other records in the table that also have no value in ADJ_ID then that's a problem - unless you just missed those in the past and can fix before moving on.


----------



## jrsarrat

Thank you, Micron. Your suggestion does make sense. Now, how do I get the db to update records 2 through etc. with the next IDs in sequence. This is the problem I am having. For Each, Do Until, Do While all seem to be viable solutions, but I am not experienced enough to execute. Or, am I thinking too much?


----------



## Micron

Almost there, I think. I went back to first posts to get info and ran across a potential issue.
You can return DMax of a field that starts with AD as you've shown but whether or not you should depends on whether or not a value could start with any other letters in the future. Maybe not today, but perhaps in 3 years, then you'll wonder why this doesn't work or creates duplicates. That's because AE will sort after AD:
Before adding "Rule1" to the table:
?dmax("role","[copy of emplinfo]")
*Role4*
After adding Rule1
?dmax("role","[copy of emplinfo]")
*Rule1*

If you started using AC I see those records starting not at 1 but where AD left off.

Therefore you probably should not simply update all Nulls to the max of the field, or at least you and everyone who maintains this after you needs to understand the risk. If you can split the prefix into 2 fields [AD] and [AdjID] you would be far, far better off. Last but not least, you cannot add 1 to AD123456 - you have to split the number portion from the text first, then put it back together. There's another piece of code that you and your replacements will have to understand how to fix if it comes to that.

I don't know who you'd have to convince but you really, really should split the fields. It's very easy to concatenate in forms and make AD123456 out of AD and 123456.
EDIT - BTW you really should base your form on a query of the table, not the table itself.


----------



## Micron

Slept on it and now here are other possibilities if you can't split the ID into 2 table fields (it's still the better way to go).
a) Show your datasheet form header if you already have not. 
Add 2 unbound textboxes to it. In code, populate one with prefix (e.g. AD); the other with the numeric portion+1.
Now you can edit prefix or number if need be. The same code would then run update sql for the pasted records. Again, update happens where ID is null. You didn't directly comment on that but I presume it would be OK based on your reply.

b) as above but just one unbound control with DMax value as controlsource. Now it can't be edited, so it is a do or do not sort of thing if the number looks wrong.

Either approach quite doable IMO. If all these posts seem a bit much for what you envisioned as a simple problem you can put it down to the joint text and number ID values. Almost always better to split such data.


----------



## jrsarrat

Hello, Micron! You are really swell (awesome) to have slept on this. 

I have been down in the dumps about this because I have been working on this 1 thing since October. My db is exceptional as far as my team is concerned, but if I can’t solve this one thing, it’ll all be for not. Okay—off my soap box.

Yes, I agree with your prior posts. Unfortunately, the ID structure is a corporate decision that all business segments/units follow. 

Is there anyway way you could possibly spell out the code for me? I promise I’m not code-illiterate, but your guidance down to the bare knuckles is MUCH appreciated.


----------



## jrsarrat

Also, basing my form on a query of my table vs. the table itself resulted in a “Name?” value across all fields. I change the record source from tablename to the qryname. Not sure if the type of query (update, select) is what caused the error. 

Also, what do you think of Modules? I am creating one of those to update the table.


----------



## 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

jrsarrat said:


> , 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 dbAD1AD1AD1AD1AD2AD1AD1AD3AD1AD1AD4AD1AD1AD5AD1AD1AD6AD1AD1AD7AD1AD1AD8AD1
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?


----------

