i have two tables . table A has the data and a column name tradeno which is blank. Second table B is the sequence no table it only has one number field starting from 0 . now table A has almost 100 records and tradeno field is left blank intentionally i want to update this tradeno with the value of table B sequence no i have this query which adds +1 to the sequence no . i have this loop but this is not updating
Dim db As DAO.Database
Dim rs1 As DAO.Recordset
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("close_trade_import_data_1") 'THIS IS TABLE A
Do Until rs1.EOF
DoCmd.OpenQuery "upt_trd_seq" ' THIS IS QUERY WHICH ADDS +1 TO SEQ NO
'DoCmd.OpenQuery "update_trade_seq_to_import_tbl" THIS QUERY WILL ADD THE SEQUENCE NO TO TABLEA.TRADENO .
Set rs = Nothing
Set db = Nothing
Instead of putting table name is rs1 i also tried to put "update_trade_seq_to_import_tbl" this query but it says invalid operation
Hope you understand
I don't understand why you don't have tradeno update automatically. All you need to do is to identify the field (in table design mode) as an AutoNumber, which will then automatically add 1 to the sequence with each new record, and it starts with 1.
Besides that, though, would you mind copying and pasting the SQL of the two queries?
Basically i am just gathering data imported from text file , re-arranging it and then its final destiny will be moving in another table. now regarding assinging the field as autonumber it MAY not work because of possibility of the original table might have that number already and it will violate the unique key constraint . I am posting you with the 2 queries ;
UPDATE close_trade_import_data_1 SET tradeno = dlookup("trade_seq","trade_seq");
-- this one will update the tradeno field with the sequence from the other table
UPDATE trade_seq SET trade_seq = trade_seq+1;
-- this just adds +1 to the existing number