populate a column with unique ID

[itm]

Hi,
I have a table with about 700 records in it that I import. In my code I am using Alter table to add a column called BlID. I want to populate each record with a unique number. Because of other things that are happening I cannot use auto number, so I am using the date (with the date, hours, min, and seconds). My problem is that I cannot think of a ways to loop through this and insert a the date and time in each record. Any ideas would be really appreciated.


Note: I am not sure that this makes a difference, but I am using MS Access 2007.
Thank you
itm

[Robeen]

I used a similar idea in an Access program I wrote a year or so ago.

What I did was wrote code to create a new ID for each new row of data.
I started off with yyyy [Eg: 2011] using the Date() function and then incremented a 4-digit number at the end of the yyyy.

My IDs look like this
20110001
20110002
20110003
20110004
. . .

In my case I was not adding the ID field to existing rows of data but rather creating a new row with a new ID - by incrementing the previous largest number in the Table by 1.

But if you already have your 700 rows of data in the table and just need to update the ID field of each row with an incrementing number, you can use the same logic in your code. No?

[itm]

I do not have a problem with the date part, the problem is that I cannot loop through the recordset. If I try to uspdate all the fields where it is null, it will update all the fields with the date and then non of them anull; so now they all have the same date and it si done. I need a was to go record buy record and do this. here is th ecode that I wm trying to do something with:

 StrSQL = ""
StrSQL = "SELECT BdgID From tblBudget_Import_File"
rs1.Open StrSQL, cn
 
rs1.MoveFirst
While Not rs1.EOF
StrSQL = ""
st = InStr(Now, "M")
stn = CStr(CInt(st - 2))
St1 = CStr(Mid(Now, 1, stn))
 
St1 = "1118201104701" 'an example of a date that I wouid use
StrSQL = "Update tblBudget_Import_File Set BdgID = '" & St1 & "' Where ISNull(Id) "
cn.Execute StrSQL
DoEvents
rs1.MoveNext
Wend

Thank you

[jzwp11]

You will probably have to use Visual Basic for Application (VBA) code in order to add the unique numbers after the records are imported.

I'm not sure if you have your heart set on using the date, but if you were using a sequential numbering scheme, you would need a DMax() function to pull that greatest current number. Then you open a recordset based on a a query that selects those records without a number and then loop through those records, incrementing the number and updating the appropriate field accordingly.


I do not know if you want the sequential number to be tied somehow to the order in which the records were imported.

[jzwp11]

Once you open the record set, you would just update each record directly, you would not need to run an update query while looping through the actual records. I'm not really up to speed on DAO (I typically use ADO), but I gave it a shot below

StrSQL = ""
StrSQL = "SELECT BdgID From tblBudget_Import_File WHERE bdgID is Null"
  
  

rs1.Open StrSQL, cn
  
rs1.MoveFirst
While Not rs1.EOF
StrSQL = ""
st = InStr(Now, "M")
stn = CStr(CInt(st - 2))
St1 = CStr(Mid(Now, 1, stn))
rs1!BdgID=st1

rs1.update
rs1.MoveNext
Wend

[jzwp11]

I've attached an example database that does what you want. I used ADO since I am more familiar with that. I just used a sequential number. You can substitute your date scheme.

The code executes via the button on form1.

[jzwp11]

I've modified the DB I posted previously and included form2 with a button with the DAO equivalent code. Again I just used a sequential numbering scheme; you will have to substitute your date scheme.