Moving A record from one table to another

[GarageFlower]

Database is an asset register.

I have a table called assetcurrent

It displays the asset and where it is, who its with and what it is.

I want a button on the form which when clicked will move the record from the table assetcurrent into another table called asset history.

Any ideas?

 

[Hayley Baxter]

Use an Append Query to move the record(s) from your current table to the archive table.

Place a button on your form, from the wizard choose Miscellaenous, Run query (your append query name)

Hay

 

[Brianwarnock]

I think that the append query will only copy the record, you will then need to delete the original to complete the move. The professional way to do this would be to use code but the simple approach is to let the command button execute a macro which first runs the append query then the delete query.

Brian

 

[ghudson]

Use an Append Query to move the record(s) from your current table to the archive table.

Place a button on your form, from the wizard choose Miscellaenous, Run query (your append query name)

Hay

The append query will basically copy [add] the record to the other [history] table but you will have to also delete the record from the current table to mimic a transfer action.

 

[GarageFlower]

OK right

I understand this.

I have created the query

and then put a command button on my form

When i click it, it tries to move every item in the original table into the history table.

How do i associate it with the current record that is being displayed within the form? and not the entire table?

 

[Brianwarnock]

You have to include criteria to select the record in the query(s)
eg Forms!formname!idname

Brian

 

[GarageFlower]

i have inputted this but now it tries to apend 0 rows.???

 

[Brianwarnock]

Just modified a db I had lying around it uses a macro to run the 2 queries from form1, bit rough but should demo ok

Brian

 

[GarageFlower]

You have to include criteria to select the record in the query(s)
eg Forms!formname!idname

Brian

Brian that is blinding. the example db showed me where i was going wrong.

I have a form with two subforms with are all related to the one asset number.

What do i put in the query to specify it as a subform rather than the main form?

 

[Brianwarnock]

You have to name both the main and the subform but it so long since I did it i wouldn't gaurantee the syntax search the forum for subform reference I seem to remember somebody gave a good link.

Brian

 

[Brianwarnock]

Try here

Brian

 

[GarageFlower]

Right ok i have this working

I am running an append query and an update query

The problem i have is

Im specifying 7 different filelds to append and update

The thing is if one of the fields is blank to start with the whole thing wont work.
Each field needs to have an entry in it otherwise it wont work.

 

[Brianwarnock]

I'm about to dissappear till next Monday so this may be my last post on this but I thought that we were discussing Append and Delete queries?

Which table are we updating the new or the old neither makes sense.

Append queries will happily append blank fields.
Update queries will update a blank field or update a field to blank.

I think your problem must lie elsewhere, but first I would question what you are trying to do.

brian

 

[GarageFlower]

Hey

Same thing i was trying to do

Have an asset current table

Want to move data from the current table into a history table via a form

Have created an append query to move the data over
Then an update query to delete the data in the original table

It doesnt appear to be working though?!?!?

just says it is going to append 0 records?!?

 

[skea]

I know what you are trying to do.Deleting a Record and wanting to store it in another table.Hope iam right. Ifts its not that then Remove the DELETE Query from the code below.
Make sure that the table where you want to store your deleted records is present(i called it tblDeletedRecords)
Here is the Code.Put it in your button's click event

dim i as integer
dim rsOld as DAO.Recordset
dim rsNew as DAO.Recordset
set rsNew=currentdb.openRecordSet("SELECT * FROM tblDeletedRecords")
set rsOld=currentdb.openRecordset("SELECT * FROM tblOldRecords WHERE(ID=" & YourTableID & ")")
rsNew.AddNew
For i = 0 To rsOld.Fields.Count - 1
rsNew.Fields(i).Value = rsOld.Fields(i).Value
Next
rsNew.Update

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblOldRecords WHERE (ID=" & YourTableID & ")
DoCmd.SetWarnings True

rsNew.Close
rsOld.Close
Set rsNew = Nothing
Set rsOld = Nothing

Hope it will be of help.

 

[GarageFlower]

Dude I will try that out

However

I decided to get rid of the delete query and use an update query

As i didnt want to delete the entire record just some of the fields

So i run an append query to copy the fields i need over into the assethistory table

Then run a update query which blanks the fields in the original table.

The only problem i have is that if some of the original fields are blank it will not work at all. it says 0 rows selected.

but if i enter information into all the fields, then it works fine

any ideas?

 

[skea]

Post Your Code snippet and we have a look.

 

[GarageFlower]

Its just done with queries,

Will look into the code.

Are you talking the sql code for the queries or the vb code on the form?

 

[skea]

ohh my my...it seems that you never tried the solution i posted.
Try it out GarageFlower.
That implies that if it jams, i will need your vb code snippet, but you can also post your SQl statement.

 

[GarageFlower]

duder

here is the vb from the onclick event on the button which runs the macro which first runs the append query then the update query

Its comes up with a compile error in the debugger


Private Sub Command68_Click()
On Error GoTo Err_Command68_Click

Dim i As Integer
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
Set rsNew = CurrentDb.openRecordset("SELECT * FROM tblAssetHistory")
Set rsOld = CurrentDb.openRecordset("SELECT * FROM tblAssetMain WHERE(ID=" & YourTableID & ")")
rsNew.AddNew
For i = 0 To rsOld.Fields.Count - 1
rsNew.Fields(i).Value = rsOld.Fields(i).Value
Next
rsNew.Update

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblAssetMain WHERE (ID=" & YourTableID & ")"
DoCmd.SetWarnings True

rsNew.Close
rsOld.Close
Set rsNew = Nothing
Set rsOld = Nothing

Dim stDocName As String

stDocName = "McroHistory"
DoCmd.RunMacro stDocName

Exit_Command68_Click:
Exit Sub

Err_Command68_Click:
MsgBox Err.Description
Resume Exit_Command68_Click

End Sub