INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Copy current record on form to new record in underlying table

Copy current record on form to new record in underlying table

(OP)
Hello,

I have a single form which does not allow addition of records.

I would like a button on the form to copy the current record and duplicate it so that it is stored in the underlying table.

Please could someone give me some basic code for doing this.

Many thanks Mark

RE: Copy current record on form to new record in underlying table

Set the code behind the button to run an SQL statement like:


CODE --> vba

Dim strSQL as String
strSQL = "INSERT INTO tblYourTable ([Your field list here]) " & _
    "SELECT [Your field list here] FROM tblYourTable WHERE [YourPrimaryKey] = " & Me.txtYourPrimaryKey
CurrentDb.Execute strSQL, dbFailOnError 

If you need more help, we need actual table and field names and data types.

Duane
Hook'D on Access
MS Access MVP

RE: Copy current record on form to new record in underlying table

(OP)
Is there a way of selecting all fields or do I have to put each field in separately?

If I select * - would that work, or would it error as it would duplicate the primary key?

Many thanks for your help this far.

Mark

RE: Copy current record on form to new record in underlying table

You may try it, but it will error (just a guess here) because of PK
So list all the fields (without PK field) twice in your (dhookom's) INSERT statement.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Copy current record on form to new record in underlying table

I typically use the Acces query designer interface to build most of the SQL statement and then copy it into my code.

Duane
Hook'D on Access
MS Access MVP

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close