Please help me understand this code & make it work:)

[pedie]

Hi everyone!
I think i have posted here too many times now....however i really do need help on this so please dont mind...

I have 2 table with same fields..
Fields are qt1, Q2,Attac which is Attachment field. I want to move all records including attachements in records to the new table...

How can I modify this code to work with my table...

Thanks in advance for helping...
Regards
Pedie

[FONT=Courier New]Set rstFrom = dbs.OpenRecordset("SELECT [Risk Assessment and Area Classification].* FROM [Risk Assessment and Area Classification] " & _
"WHERE ((([Risk Assessment and Area Classification].ProjectRef)= '" & targetProjectRef & "'))" & _
"ORDER BY [Risk Assessment and Area Classification].[Installation ID]", dbOpenSnapshot)[/FONT]
[FONT=Courier New]Set rstTo = dbs.OpenRecordset("Risk Assessment and Area Classification", dbOpenDynaset)[/FONT]
[FONT=Courier New]rstFrom.MoveFirst
Do Until rstFrom.EOF[/FONT]
[FONT=Courier New]rstTo.AddNew
For iFor = 1 To rstFrom.Fields.Count - 1 ' use 1 not 0 as has ID field is Autonumber[/FONT]
[FONT=Courier New]If rstFrom.Fields(iFor).Type <> 104 And rstFrom.Fields(iFor).Type <> 109 Then ' types 104 and 109 are Multi-Value field[/FONT]
[FONT=Courier New]If rstTo.Fields(iFor).Name = "ProjectRef" Then
rstTo.Fields(iFor).Value = myProjectRef
Else
rstTo.Fields(iFor).Value = rstFrom.Fields(iFor).Value
End If[/FONT]
[FONT=Courier New]Else
Set rstMVFrom = rstFrom.Fields(iFor).Value[/FONT]
[FONT=Courier New]If rstMVFrom.RecordCount > 0 Then
Set rstMVTo = rstTo.Fields(iFor).Value[/FONT]
[FONT=Courier New]Do While rstMVFrom.EOF = False
rstMVTo.AddNew
rstMVTo.Fields(0).Value = rstMVFrom.Fields(0).Value
rstMVTo.Update
rstMVFrom.MoveNext
Loop
rstMVFrom.Close
rstMVTo.Close
End If ' rstFrom[/FONT]
[FONT=Courier New]End If ' iFor
Next iFor[/FONT]
[FONT=Courier New]rstTo.Update[/FONT]
[FONT=Courier New]rstFrom.MoveNext
Loop ' rstFrom[/FONT]
[FONT=Courier New]rst2.Close
Set rst2 = Nothing
[/FONT]

 

[Marbles]

It would probably be easier to use an Append Query.

 

[pedie]

Marbles, thanks for replying...
Append Query does not work when we refer to Multi-Value field.

It would probably be easier to use an Append Query.

 

[Norie]

Pedie

It can be made to work, but why are you using a multivalue field anyway?

In Excel you wouldn't usually have multiple values in one cell.

 

[HiTechCoach]

Norie,

pedie is wanting to move records between tables that have an attachment data type field with multiple values.

pedie has other posts here on the same topic.

 

[Norie]

HiTechCoach

I know pedie has other posts.

The question still stands though, why a multivalue field?

Even more so if this multivalue field is being used to store multiple attachments.

 

[pedie]

Norie, correct....!
I have searched the google for few days looking for semiliar thread...to move multi value fields from one databse to another but cant find one...


Why Multi - Value field?
Thats is how the business wants.... so cant help!

 

[Norie]

Pedie

I know that's what the business wants, you've kind of mentioned it a few times.

How is the business using this field and the attachments?

Is a user selecting an attachment from the field and then opening it?

 

[pedie]

Pedie

I know that's what the business wants, you've kind of mentioned it a few times.

How is the business using this field and the attachments?

Is a user selecting an attachment from the field and then opening it?

Oh alright, apologies...
The form is linked to table2 in currentdb
Users attaches the files here from front-end form then is transered to table2 along with other fields....
I want to transfer this to different database directky once it is transfered to this table2.

This are basically holidays and others Leaves approval emials that needs to be attached and stored for latter purpose along with the leave from work information....



Thanks again,

 

[Norie]

Pedie

So the user attachs them to 'their' database and then they are exported to the other database?

That still doesn't really explain the need for a multivalue field.

Why not have a separate table for attachments?

This would list all the attachments and would include a link to whatever it is these attachments 'belong' to.

Then when you want to update the other database you can run a simple append query to a similar table.

So you still have all the attachments but they aren't all stored in one field.

That should make it a lot easier to deal with work with them.

For example if you need to delete an attachment just select it from the attachments table and delete it.