Moving A record from one table to another (1 Viewer)

[skea]

it seems you just copied all the code direct into your form without internalising it.
Any way,Here we go...
Try to first remove all the Error Trapping and All the Code that calls the macro.
Also replace ID in the WHERE clauses with the Unique Identifier of the table tblAssetMain.
Replace YourTableID accordingly.
If you are still puzzled, then its better off posting your form and the two tables you want to make Append and Delete and we will Rectify it for you.

 

[skea]

Here is an example.
Inform me if it helped.

 

[GarageFlower]

Cheers for that dude

I need mine to be able to copy and move the first and last name fields but keep the ID number in there.

Is is possible to specify fields to move and not actual records so you can keep some of the data for that record.

Basically

Copy certain fields into a new table including id number and first name.

But then on the original record keep the id number in there but delete the first and last name

Does that make sense?

 

[Peter2222]

Hi, looks like you're problem is sorted, I was going to suggest using ADO instead of DAO simply because it's newer. If interested I'll send an example of the code that I would use...

 

[GarageFlower]

dude the problem is not sorted

ive been able to do that all allong.

what i need is to be able to just copy some of the fields of a record into another table and then delete the same ones from the original table and keep some of them at the same time.

for example copy of the id number and first name but not the surname

and then go back to the original table and delete the surname and first name but not the id number

any ideas please?

 

[Peter2222]

Ado

here's an example of opening another table and adding a record:

this bit of code opens the connection and recordset to the target table where DBFullName is the full name string of the db and TableName is a string for the table name. [Id] is the Id in target table, and Id is the Id in the present form if that makes sense.

Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT * FROM " & TableName & " WHERE [Id]=" & Id, cn, adOpenStatic, adLockBatchOptimistic

'----------now to add a record:

rs.addnew

'----------assign a value

rs![Criteria1] = "something"

cn.begintrans
rs.updatebatch
cn.committrans

I know this is very wishy washy, there is loads of help on ADO on www.msdn.com

if you want a full example I'll send one

 

[GarageFlower]

Example would great if at all possible

 

[skea]

was going to suggest using ADO instead of DAO simply because it's newer.

I have never dared for ADO in Access, since i know it does the same tasks in almost the same time frame with DAO. But i use ADO.NET in my .NET applications

GarageFlower,attched is what i think you want and guided in the Code comments.

 

[GarageFlower]

Dude

I sorta get it but not sure it is doing what i need it to do

I have a table called assetmain
table called location
table called employee
and a table called asset history

I have a form for assetmain which has subforms for location and employee

I need code so i can press a button and it will cut over
From Asset Main: Asset Number, Date Installed
Employee: Title, First Name, Surname
Location ; Region, Department, Floor, Desknumber

And past that information for that record into the asset history table.

At the same time i want the asset number in the Assetmain table to remain in the assetmain table as well as being copied to the assethistory table.
All the others can be deleted from original tables.

Any ideas?

Also I have no idea how to set a reference to microsoft DAO object library.

Can i just do this using queries.

I have done this allready and it work except.
It wont copy over any records which have a blank entry in a field specified to be copied. And it wont delete anything if one entry is blank in a specified field.

 

[GarageFlower]

Right

Here is an example of the db

Open up assetmainfiltered form

Click the move to history button

Notice how record 1 moves fine and does exactly what i need it to do apart from a few error messages which dont do anything anyway and are just annoying.

Try moving records 2 and 3
Notice they dont work as not all the fields in the query have been filled in. therefore it doesnt move anythign at all.

Any way i can fix either of these problems.
as in it will still work like when you move record one, but you dont have to have all the fields filled in.

 

[skea]

You just NEED INSERT,UPDATE and DELETE QUERIES under that command button. But i have some guidings for you below.
I have attched a sample but i had to go deep into correcting your tables and Relationships.

Also I have no idea how to set a reference to microsoft DAO object library.

In your code page, goto Tools--->References---Microsoft DAO 3.6 Object Library. Select it.

1)Iam sorry, with me i hard-code most of my queries and i dont deal much with macros in access, so i disbanded what calls the macro under that Move To History button.

2)The DoMenuItem code is left over A95. Microsoft recommendeds NOT using it since A2K. But in case you are to use them then use the runCommands as below

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord

Its good practice to not leave spaces in your Fields,for example [Date Disposed] should look like [Date_Disposed] or [DateDisposed]

All your tables should also have a unique index. its only tblAssetsMain that had a unique index. You will need to spend some time and learn about database Normalisation. The forum also has a sticky

You also need to make the field names in the History table similar to corresponding field names in the other tables. For example in the table Employees you had LastName and its corresponding in the history table was SurName!!!

In The Relationships, enforce referencial integrity and cascade to delete related records should be checked.

Have a nice weekend.

 

[GarageFlower]

Dude

This doesnt work....

It doesnt move any of the data from the location of the employee tables.

it only moves data from the date installed thingy

And then when it does that the view history link doesnt work?!?!!?

I have noramlised the database.

It's working fine.. apart from this one thing?!?!?

I onlhy uploaded a very small sample of it

I cant got changing things now as there are lots of other bits that i have not uploaded.??

any idease please?

 

[skea]

I tested it and It Does...GarageFlower.
Be slow and internalise all i have done.
And make sure that you have put in Data in the tblAssetsMain and Related records in the corresponding tables(tblEmployees and tblLocations) then see the output.

 

[GarageFlower]

hmm i did indeed. and it didnt seem to work for me?

is there more stuff that i need to do within your example to get it working?

 

[GarageFlower]

dude

try moving record one

its moves the date installed into the first name field.

something from the location table into the title field

and it misses out the last name field totally??!?!?

Also the view history doesnt work any more

 

[skea]

Yes you need alot to change in there.I gave you the solution but if you just go and paste all that code in your project as it is, it wont work, thats why you need to internalise it and change where change is necessary.
I changed alot of things about the names of the fields and form controls. May be its the one giving you problems.
Try entering data in the tables directly and not from the form and then later use the button on a particular record.
If data of another field is displayed in another field then you will need to play around and change the fields in the hard-coded Queries to suit what you really want.But thats the syntax.
Tchao

 

[Brianwarnock]

Hi
As I stated in an earlier responnse the professional way is to use code and hopefully you have it working now however, if you want to continue with your queries there are a few alterations to make.

1 Remove criteria test on date from both queries . it is not needed as asset number is unique and thus only one record will exist in table main.
2 In the update query set the update to field to Null for the Date and include the asset number check in the criteria but with empty update to field ie don’t type anything

Does it make sense for the site or department to be blank? Isn’t that why you are going to use combo boxes so that this data is entered correctly?
It works ok with other fields being blank.

brian

 

[GarageFlower]

Brian

I have done this but it is still having the same problems.

If you view the attachment i uploaded previously and use the advice you have given me it is still not working

 

[Brianwarnock]

I used your example to run the tests , as i said if the department and site have data in them everything is fine even if other fields are blank.

brian

 

[Brianwarnock]

ok back to basics, if you are reusing the sub tables surely your relationships are 1 to 1 other than main to history, thus to recognise any selection of data all you need is the asset number, and as it is the selection part of your queries that is failing remove all criteria except that to select the asset number ie [forms]![frmassetmainfiltered]![asset number], both queries will now work.

Brian