Moving A record from one table to another

[GarageFlower]

Brian

Could you elaborate on that a bit more please. or upload an example as i am failiing to grasp what you mean

many thanks

 

[Brianwarnock]

You have selection criteria on all of the fields in both your queries. If you change them to select queries you will see that they are often not selecting any data. As each Asset number will only refer to one location etc then selection can be achieved merely by using the asset number, doing this your queries will work.

If the bit about an asset number referring to only one location etc is wrong then I don't understand what's going on, which is not unusual

Brian

 

[Brianwarnock]

Attached what I ended up with.

 

[Brianwarnock]

I think I zipped the wrong file, have another go.

 

[GarageFlower]

Brian

Many THanks for that

Is their any reasons why it doesnt work if the site and department havent been filled in?

 

[Brianwarnock]

Hi
Not in yesterday hence delay in responding.

My guess is that it is to do with the fact that they are Combo boxes, you could add 3 lines to your criteria comparing for Is Null in those fields.

I must ask if you are attempting to create an audit trail, if so the correct way to do this is covered in this forum, I haven't used this forum's examples but in a true audit trail the update of a record triggers the code that adds the required info to the audit table, usually including userid and time stamp.

Brian

 

[GarageFlower]

Brian

This is an audit trail yes..

Where are the links. will have a quick search see if i can find them

Other fields specified within the queries have combo boxes and yet they do not need to be fille din

 

[ghudson]

This Audit Trail is a popular thread on the forum.

Searching the forum is a great way to discover and learn the answers to your Access programming questions.

 

[GarageFlower]

Dude

I got your audit trail function working

It doesnt store it in a table though?

where does it store it?

i got an audit trail field set as the data source

But when i view the actual table there is nothing in there

 

[GarageFlower]

Right Here is the latest

The append and update queries do not work unless all the fields that are being appended and updated are being filled in.
Any ideas how i can get it too work even if just one field is filled in and the rest are blank

FAO Ghudson

your audit trail one.
How do i get it to incorporate the subforms fields as well as the main form?

Check the attachment

Chose asset number 0 from the drop down list
You can see it from there

 

[ghudson]

Dude

I got your audit trail function working

It doesnt store it in a table though?

where does it store it?

i got an audit trail field set as the data source

But when i view the actual table there is nothing in there

You do not have it working if the audit data is not being stored in the audit trail field in your table. Go back to my sample and see how I do it then double check how you have it setup in your db.

 

[GarageFlower]

Aha

Nice one

found it

shift and f2

How do i include subforms which are also on the main form duder?

 

[baldeagle]

Nice error trapping.

 

[haimster2797]

Hello!

This code below worked great and copies my active record once I hit my command button from my current table to my archive and then deletes the record. I also added REQUERY to refresh screen. I can delete records 2 - unlimited. My issue however is in the next paragraph below the code example.

-------START CODE----------

Private Sub Delete_Test_Click()
Dim Msg, Style, Title
Msg = "Record archived and deleted!"
Style = vbOK + vbDefaultButton1
Title = "Operation Completed!"
Dim i As Integer
Dim rsOld As DAO.Recordset
Dim rsNew As DAO.Recordset
Set rsNew = CurrentDb.OpenRecordset("SELECT * FROM Deleted_Env_InventoryDB")
Set rsOld = CurrentDb.OpenRecordset("SELECT * FROM Env_InventoryDB WHERE(ID=" & ID & ")")
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 Env_InventoryDB WHERE (ID=" & ID & ")"
DoCmd.SetWarnings True

rsNew.Close
rsOld.Close
Set rsNew = Nothing
Set rsOld = Nothing
Requery
Response = MsgBox(Msg, Style, Title)
Requery
End Sub

------------END CODE--------------

However I cannot delete record #1. It says that "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data entries and try again."

Only record #1 does this, nothing special in this record compared to #2 - whatever.

Ideas? - Thanks!

 

[haimster2797]

Scratch my last reply, something was wrong with my record, I deleted this one problematic record and then tried again and I am able to delete record #1 as any other one now. Sorry about that. This solution works great by the way! Per my code, I added REQUERY which helps by refreshing after the deletion is completed. Thanks!!

 

[sdsmith]

I am now having a similar issue. I have made a button on my form and I am trying to get the action to move a record from one table to another table. The situation is moving an active record table to an archived record table. It's not even allowing me to put in a code. What steps do I put in for this?

 

[theDBguy]

I am now having a similar issue. I have made a button on my form and I am trying to get the action to move a record from one table to another table. The situation is moving an active record table to an archived record table. It's not even allowing me to put in a code. What steps do I put in for this? View attachment 86214

Hi. Welcome to AWF!

I see you started a new thread for the same question, so it's probably a good idea to just continue the discussion there. Good luck!

Moving a record from one table to another one using the button feature

Hi! I have built two tables (active and archived) and I have created an "active" form. On the form, I've placed an "archive" button on the form that I would like to be able to click and move the record to the "archived" table. It's not allowing me to put in a code and I am not sure what options...

www.access-programmers.co.uk