Moving fields to corresponding fields

[robertmcbride1126]

I would like to know how to move fields from one record to corresponding fields in another record.

Trans1!name = trans2!name
trans1!phone = trans2!phone
trans1!addr = trans2!addr

Let's say in both tables there are different fields but some fields are the same names and data types. Is there
a way to just say move trans2 to trans1 and the matching
fields will be moved from trans2 to trans1.

Thanks

 

[zevw]

Use an Update Query.

However I don't understand what you mean to another record.

Maybe you mean within the same record just to a different field, please elaborate.

 

[robertmcbride1126]

Sorry for the confusion on the explanation of my problem. I want to know if there is a way in Access to move corresponding fields from say record1 to record2 by just saying move record1 to record2 and Access would figure out which fields in both records has the same names and types.

 

[zevw]

robertmcbride1126

I understood your question, can you tell me what are you trying to do. Access is not a DOS Window where you move file1 to file2.

You need to run a query and it needs to be based on a criteria.

What are your criterias?
What do you want to gain by moving the information from record1 to record2.

Please try to be more specific.

 

[robertmcbride1126]

Thanks ZEVW for taking out the time.

I am using Microsoft Access and I have two large records say rec-A and rec-B both records have 25 fields. And 19 of those fields is the same identical fields in both records the other 6 are different.

Lets say I read rec-A and and make some changes to some fields such as calculations etc. When I am ready to move the fields from rec-A to rec-B and update rec-B's table I normally would code 19 lines of code moving all the matching fields from rec-A to rec-B.

But what I would like to do is specify in one line telling access to move rec-A to rec-B and it figure out which fields in Rec-A match fields in Rec-B and move fields from rec-A to Rec-B accordingly.

The reason I need this is I do a lot of updating large records in different tables and would like to cut down on a lot of time consuming coding. If by chance you are familar with it I also work with SAP and ABAP is the programming language and it has this feature. It's sort of a 3rd generation language.

So I think Access may have it but I just can't find it.

I know this is a lot of reading, so thanks in advance.

 

[zevw]

robertmcbride1126

Now you are talking!

Okay so you have 2 Tables and if you make changes to the current record you want to append it to another TABLE not move it within the same table. I hope I understand this correctly.

I would create in the On Change event procedure a Flag that if something changes it will set the flag to Y

The Flag is a hidden Text Box on your form.

So what happens is the following:

In the Before Update Event Procedure if the flag equals Y, you will ask the user if they want to keep their changes if Yes then keep the changes, otherwise undo the changes.

In the After Update event procedure, run an Append Query that appends this Record to Table B and you will have a new record with all changed fields.

I hope this is clear.

 

[robertmcbride1126]

Thanks, this make since I will try this method.

 

[red22j]

Another way to do it - note is is slow if you put it in a loop but will work fine if you just update one record at a time - hope its useful.

I still use DAO but i'm sure there's a similar ADO analog.

Dim rs1 as dao.recordset, rs2 as dao.recordset
dim fld1 as dao.field, fld2 as dao.field

set rs1 = currentdb.openrecordset("SELECT blah FROM tbl1 WHERE blah&quot
set rs2 = currentdb.openrecordset("SELECT blah FROM tbl2 WHERE blah&quot

'Assume both return 1 record - easy to change code anyway

for each fld1 in rs1.fields
for each fld2 in rs2.fields
if fld1.name = fld2.name then
rs2!fields(fld2.name) = rs1!fields(fld1.name)
end if
next
next

'------------------------------------------
Of course there are more efficient ways it the columns are in any sort of order. Just note that accessing the fld.name is VERY slow in a loop. but for a one-record hack - this should work out just fine.