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]

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]

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.