QBE to select all columns and paste

[casey]

Hello all,

I would like to design a query that selects all fields from one record and pastes them all into a new record (in essense creating a duplicate record). I've read that using a QBE is what I need. Can some one direct me to where I might find examples or more information on QBE? This is a new concept for me. Please advise as I will continue searching. Thanks.

Casey

 

[llkhoutx]

It's extraordinary to have duplicate records. One goes to great lengths not to have them.

You can use an append query, with criteria specifying the record you want to (try to) create a duplicate record. Depending on the structure of your table, you may get a key violation prohibiting duplicate records.

 

[casey]

llkhoutx,

Thanks for the reply. I will look at append queries to see if they would work. I'm out of practice and may be missing something.

I think the append query would do it, but I'm looking for an easy way to select all the fields(minus the autonumber) for one record and paste them into a new record. With an append query, I would need to specify every field in the query(I think). Then if fields change in the table, I would have to go and update the the query in VB. I read that a copy and paste method could be used from a QBE. This way the procedure would stay constant even if the table's structure would change. Thought that sounded easier.

**Actually, duplicate data is required in this situation because although the records are duplicated, each will have slight differences from any before or after it. Any changes need to be stored so one can view each record as it appeared when last modified.**

If anyone can direct me to info. on QBE, I might be able to work through it.

Casey

 

[llkhoutx]

There's lots of good books on Access. Look through them for the trick you need. Access Developer's Handbook (comes in various Access editions) by Litwin, Getz et al. is the best for my money.

You can select all fields in the QBE frame by double clicking the acterick in the table pane. However, you will also get the auto number field, but I don't think that you can append with the autonumber field. I've never tried that and I've been using Access since Version 1.

 

[casey]

There I go making up my own terms again

Thanks, llkhoutx.

I usually refer to the QBE as Query Builder. Did I just make that up? I thought the QBE was some mystical world or something for awhile. That must've really sounded stupid, hehe.

OK. You suggest creating an append query. But by selecting all, I will be selecting the autonumber also, causing a duplication error on the autonumber. I think I've read somewhere that it was possible to do this, but I can't seem to find it right away. I'll keep looking.

Perhaps I could look into the Updating a Recordset? I could create a TableDef and a Recordset and loop through the fields names in the TableDef and Update each field name in the Recordset for each iteration skipping the AutoNumber. Does that sound like too much overhead. What do you think?

Thanks.

 

[llkhoutx]

I use recordsets all the time.

There's no one way to do something in Access. Ask ten and you get 10 different solutions.

It's not like the "good ole days" when memory and efficiency were manditory because PC's were so slow. Now, most have a screamer, inefficiency isn't particularily noticed by anyone except the Dilbert's who thrive on criticizing others work.

 

[casey]

Funny!

Yeah, I tend to do things inefficiently until I find I better way. Usually things take a week to design and then I find one command that does it for me. It's all about learning, right?

Thanks for your input.

Paul