move a record to a new tabel in code

[boomer1974]

Good day experts,

I am trying to find a way to move a record to a new table using recordsets and code. If the value of field x is greater than y, I need to remove the entire record from it's current table and add it to a new table. Any help would be greatly appreciated

Boomer

 

[KenReay]

Hi

Essentially

DOCMD.RUNSQL "INSERT INTO ... WHERE X > Y"

see help for INSERT INTO, there are examples

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KyleS]

OK, you're going to need to fill in the blanks here but this should get you started: (you'll need to add some error handling in here - And all the red text needs to be replaced with actual field/table names)
Access 97:

Dim rst AS DAO.Recordset

Set rst = CurrentdB.Open Recordset(" YourTableName &quot

Do Until rst.EOF
IF rst! FIELDX > Y Then
Docmd.RunSQL "INSERT INTO NEWTABLENAME (FieldA, FieldB, FieldC...)
SELECT FieldA, FieldB, FieldC...
FROM OLDTABLENAME
WHERE OLDTABLENAME.IDFIELD = rst!IDFIELD
End IF
Loop

rst.Close
Set rst = Nothing


Kyle

 

[KyleS]

OK, this is what I get for doing 2 things at once... Where's the dunce cap?

Kyle

 

[tag013]

You could do something like this:

Private Sub ChkRec()
'open recordset first
With rst
.MoveFirst
Do Until .EOF
If recX > recY Then
updateTbl (recX)
.Delete
End If
.MoveNext
Loop
End With
End Sub

Public Function updateTbl(rec As String)
'open recordset for the table to be updated
With rstTbl
.Fields("recX&quot = rec
.update
End With
rstTbl.Close
End Function

First you open the recordset that needs to be validated, the loops move from one rec to the other checking whether it meets the criteria if it does then it calls the function that will insert the record into the second table. As argument to that function u pass the rec you need to move (recX). The record will then be deleted from the first table. Using operator "AND" within the if statement you can be more specific about the record you want to validate.

 

[KyleS]

The only thing to add to Ken's single query is the DELETE query (since we're moving, not copying). So we need to ADD

Docmd.RunSQL "DELETE * FROM... WHERE X > Y"


No need for a recordset here

Kyle