Duplicate Record

[gselliott]

Im not sure if what I am trying to do can be done using a query but here goes anyway!

I have a database that is used to store Customer and Supplier invoice records. Each month an export is ran from the database, basically this is a text file which contains the results from the query. The text file is imported into a Unix based program and so needs to be set out in a Pipe Delimiter Format.

I have managed to do this using a query and the Export feature in Access, but what I now need is to duplicate each record so that one of the fields can contain different information. Due to the limitations of the Unix program I am unable to just add another column to the record and so I need to duplicate the whole record and change just one of the fields.

Is there anyway of duplicating a record using a query or a mixture of the query and code? I have added an example of the text file that is exported, which might help understand what is needed!

I really hope someone can help as I am completely stuck and confused now.

Thanks

 

[GumbyD]

This code will create a duplicate record for each record in your table. You will need to change it around a bit and put in your field names. Not sure if the date value will be the same for each of the new records or not, but this should give you a place to start. Good Luck!

GumbyD

Public Function AddDupRecords()
Dim rstMakeDups As Recordset
Dim F1 As String
Dim f2 As String
Dim f3 As String
Dim f4 As Date
Dim f5 As String

Set rstMakeDups = CurrentDb.OpenRecordset("YourTableName", dbOpenDynaset)
If rstMakeDups.EOF Then
MsgBox "No records in table please populate", vbOKOnly
Exit Function
Else
rstMakeDups.MoveFirst
Do Until rstMakeDups.EOF
F1 = rstMakeDups!Field1
f2 = rstMakeDups!Field2
f3 = rstMakeDups!Field3
f4 = rstMakeDups!Field4
f5 = rstMakeDups!Field5
rstMakeDups.AddNew
rstMakeDups!Field1 = F1
rstMakeDups!Field2 = f2
rstMakeDups!Field3 = f3
rstMakeDups!Field4 = #12/15/2003# 'I am not sure where you date value is coming from you may have to use a variable here instead
rstMakeDups!Field5 = f5
rstMakeDups.Update
rstMakeDups.MoveNext
rstMakeDups.MoveNext 'Move next twice because we added a record
Loop
End If
rstMakeDups.Close

MsgBox "process complete", vbOKOnly

End Function

 

[gselliott]

Thanks for both replies i will look into both. Pat how would i go about creating a Union Query i am not familiar with this.

Thanks again.

 

[gselliott]

I have had a go at using the code that GumbyD has suggested but i am getting a Run-Time error 3001 on the following line:

Set rstMakeDups = CurrentDb.OpenRecordset("tblCharges", dbOpenDynaset)

I am probably doing something really stupid, i have not used Recordsets much before.

Hope you can point me in the right direction.

Thanks again.
gselliott.

 

[gselliott]

Pat I worked out what a UNION query is (was being stupid) and it has worked with no problems what so ever.

Thanks all for your help much appreciated!