Hello Everyone,
I have an Access 2016 DB with a table called Parts with the following structure. ID is the Primary Key field.
ID PART_DESCRIPTION PART_NUMBER PART_CATEGORY 1 PART 1 1001 CATEGORY 1 2 PART 2 CATEGORY 1 3 PART 3 CATEGORY 2 4 PART 4 CATEGORY 3 5 PART 5 CATEGORY 4
This table currently has several thousand records. The PART_DESCRIPTION and PART_CATEGORY values already exist. No PART_NUMBER values currently exist. I want the database to generate them as they are smart numbers that will mean something. I have been able to generate the part number using VBA and a recordset/do loop but it is slow and I would prefer to do this in SQL if possible with an UPDATE statement. I know that some people would suggest that I just use the Primary Key ID to dynamically generate the PART_NUMBER but I have good reason not to do that. What i'm really trying to do is just generate the seed numbers. I will do more with it after the update statement, using the Category to make the final smart number. That is why I really need this type of solution.
So my question is this: If the first part number exists (1001) is there a way to use an UPDATE statement inside VBA that will find the last part number used (DMAX function?) and then increment it for every existing record in the table? If the table above were the starting table, the table below would be the final output of the query. It would find that last number used is 1001 and then start updating all the NULL part numbers by sequentially adding 1 to the last part number.
ID PART_DESCRIPTION PART_NUMBER PART_CATEGORY 1 PART 1 1001 CATEGORY 1 2 PART 2 1002 CATEGORY 1 3 PART 3 1003 CATEGORY 2 4 PART 4 1004 CATEGORY 3 5 PART 5 1005 CATEGORY 4
I have tried the below but it doesnt work. I now understand why it doesnt work, but i'm stumped on how to execute a SQL statement that does something different on a per record basis. In this case, incrementing by 1.
Hope my question is clear and that someone can help me out!Code:Dim strSQL As String strSQL = "UPDATE PARTS SET PART_NUMBER = " & DMax("PART_NUMBER", "PARTS") + 1 CurrentDb.Execute strSQL