How to duplicate a record in a recordset?

[TSimonick]

Hello,

Can someone suggest a way to duplicate a record in a recordset? I was going to do this in a query with a Cartesian product, but I need to be able to conditionally duplicate some records and not duplicate others, before exporting the recordset as a text file. So what I'm looking for is a text fle that might look like this:
Rec1
Rec1
Rec2
Rec2
Rec3
Rec4
Rec4
etc.

If there is a way to do other than using a recordset, that's fine too.

Thanks for any help!

Tom

 

[KenReay]

Hi

Select field1, field2 ...etc FROM myTable
UNION
Select field1, field2 ...etc FROM myTable WHERE Field1 = 2
UNION
...etc

You need to replace the field names, table names with your values and set the WHERE clause to appropriate values

Hope this helps
Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[TSimonick]

Ken,

Since I'm new at VBA I just want to be sure I'm following your idea. I would use the Union query as the source for my recordset, as in:

OpenRecordset("Union", dbOpenDynaset)

Thanks again.

Tom

 

[KenReay]

Hi
Essentially yes.

I am not sure if the code you posted is meant to illustrate of be the actual code,

it would need to be along the lines of Set Rs = Db.OpenRecordset(...ect) assuming you are using DAO.

And UNION would be a saved query def.

By the way, my advice is avoid at all costs using words as the names of objects etc which may be Access (or SQL) reserved words (UNION is such a word).

If you are a beginner I would suggest you adopt a naming convention and stick with it. This will help to avoid such mistakes and will save you a lot of grief in the long term.

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK

 

[TSimonick]

Ken,

Thanks for the clarification! You're right, I need to get systematic in my naming or I won't be able to follow my own code!

Regards,

Tom