By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,375 Members | 1,095 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,375 IT Pros & Developers. It's quick & easy.

Just a query - Moving entry from 1 table to another

P: n/a
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, adLockPessimistic
strTempID = rst("ID")
strTempField1 = rst("Field1")
'//..... all the way to the last field

'// 2nd table
rst.Open "Select * From tblNew", DB, adOpenStatic, adLockPessimistic
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, adLockPessimistic
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!


Jul 19 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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*************@tk2msftngp13.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, adLockPessimistic
strTempID = rst("ID")
strTempField1 = rst("Field1")
'//..... all the way to the last field

'// 2nd table
rst.Open "Select * From tblNew", DB, adOpenStatic, adLockPessimistic
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, adLockPessimistic
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!

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.