Just a query - Moving entry from 1 table to another

[Steven Burn]

I know I could probably write something along the lines of the below to do
this but, am hoping there's an easier way.

Basically what I am wanting to do is, select an entry in 1 table, then move
it to another. However, at the same time, in the second table, find all the
entries marked "Yes" in the "New" field, and change the oldest one to "No".

What I was thinking, was something along the lines of;

'// 1st table
rst.Open "Select * From tblMain Where ID = '" & strID & "'", DB,
adOpenStatic, adLockPessimist ic
strTempID = rst("ID")
strTempField1 = rst("Field1")
'//..... all the way to the last field

'// 2nd table
rst.Open "Select * From tblNew", DB, adOpenStatic, adLockPessimist ic
rst.AddNew
'// as the ID from the first table is most likely going to be taken
'// I'll just ignore strTempID, and add it as I would any other
rst("ID") = rst.RecordCount + 1
rst("New") = "Yes"
rst("Field1") = strTempField1
'//..... all the way to the last field
rst.Update

'// Locate the oldest entry that still has it's "New" field marked "Yes"
'// And change it's "New" field to "No"
rst.Open "Select * From tblNew", DB, adOpenStatic, adLockPessimist ic
Do Until rst.EOF
If rst("New")="Yes " Then
'// This is where I'm now stuck.....
End If
rst.MoveNext
Loop

Apologies if this makes no sense, I've just woken up....... but this is
bugging the life out of me, so any advice would be appreciated.

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!

[Steven Burn]

Nevermind..... figured it out :o) (all I needed was a caffiene hit, hehe)

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!
"Steven Burn" <pv*@noyb.com > wrote in message
news:un******** *****@tk2msftng p13.phx.gbl...

I know I could probably write something along the lines of the below to do
this but, am hoping there's an easier way.

Basically what I am wanting to do is, select an entry in 1 table, then move it to another. However, at the same time, in the second table, find all the entries marked "Yes" in the "New" field, and change the oldest one to "No".
What I was thinking, was something along the lines of;

'// 1st table
rst.Open "Select * From tblMain Where ID = '" & strID & "'", DB,
adOpenStatic, adLockPessimist ic
strTempID = rst("ID")
strTempField1 = rst("Field1")
'//..... all the way to the last field

'// 2nd table
rst.Open "Select * From tblNew", DB, adOpenStatic, adLockPessimist ic
rst.AddNew
'// as the ID from the first table is most likely going to be taken '// I'll just ignore strTempID, and add it as I would any other
rst("ID") = rst.RecordCount + 1
rst("New") = "Yes"
rst("Field1") = strTempField1
'//..... all the way to the last field
rst.Update

'// Locate the oldest entry that still has it's "New" field marked "Yes" '// And change it's "New" field to "No"
rst.Open "Select * From tblNew", DB, adOpenStatic, adLockPessimist ic
Do Until rst.EOF
If rst("New")="Yes " Then
'// This is where I'm now stuck.....
End If
rst.MoveNext
Loop

Apologies if this makes no sense, I've just woken up....... but this is
bugging the life out of me, so any advice would be appreciated.

--

Regards

Steven Burn
Ur I.T. Mate Group
www.it-mate.co.uk

Keeping it FREE!