Update with record number

[ping99]

Hi all,

Is it possible to update with record number not with field name ?

update mytable set field1 ="OK" where recno() = 1 ;

I means here record number refering to index key 1,2,3,4
etc...

index key 1 equal record 1
index key 2 equal record 2

TIA

 

[lameid]

Can you be more descript? I can't figure out what you are trying to accomplish.

 

[ping99]

Hi,

Normally we can update with field name that existed in the table for example:

update mytable set field1 = "OK" where field2 = "YES"

but in my case, I want to update with key record number :
1st,2nd and 3rd record number showed in ACCESS mytable

mytable in access that has 1 field1 only :

field1
1st OK
2nd OK
3rd OK

That's why my question, is it possible to update with
record number ? instead of field name

TIA

 

[Golom]

Unless you have a field in the table that contains a record number, there are no record numbers in Access. To put it another way, what you see as the first 3 records depends on the ordering that you use to display the table. Just displaying a table usually defaults to primary key order but that is not guaranteed.

You can do things like this

UPDATE myTable SET Field1 = 'OK'

WHERE KeyField IN (Select TOP 3 KeyField From myTable
                   Order By KeyField)

But that is still selecting on a field and not on a specific numbering of the records.

 

[ping99]

Hi Golom,

I used the autonumber called ID field to update that works fine

update mytable set field1 = "OK" where ID = 1;

update mytable set field1 = "yes" where ID = 2;

......
......

ID field come with autonumber automatically.

mytable before updated with field1 empty
ID field1
1
2
3
4

mytable after updated
ID field1
1 ok
2 yes
3 no
4 yea

 

[Golom]

That works ... but it's not a record number ... it's a field. If for example, you were to delete the record where the autonumber field has the value "2" then

update mytable set field1 = "yes" where ID = 2;​

wouldn't do anything because "ID = 2" doesn't exist anymore.

 

[ping99]

Golom,

You are right, but that's no deletion for a moment,

Thanks again for your very advice.

 

[lameid]

How about something like the below code?

Sub UpdateMyFields()
Dim cnn As New ADODB.Connection
Dim RSRead As New ADODB.Recordset
Dim RSWrite As New ADODB.Recordset

Set cnn = CurrentProject().Connection
RSRead.Open "TableNameToRead", cnn, adOpenDynamic, adLockOptimistic
RSWrite.Open "TableNameToWrite", cnn, adOpenDynamic, adLockOptimistic

While Not RSRead.EOF
RSWrite.Fields(RSRead!AutonumberField).Value = RSRead!FieldWithValue
RSRead.MoveNext
Wend
End Sub