Copy data from old record into current record open on form with the click of a button

[Josha]

I've been trying wrap my head around this for weeks now and just can't get it....



I can't explain my database for privacy reasons, so I'll use a library database as an example....

Lets say I'm storing all my books in the library in a Access Database...

The 'BOOK' table has the following fields;
BookID (primary key)
Title:
Author
Country of Origin
Language
Genre
Publisher


and I have a simple form that allows me to add the above details...

However, when the admin staff (lets call him Joe) receives a shipment of books, they usually come sorted by Publisher and Genre. For example all books from publisher: "Bloomsbury Publishing" and genre: "Fantasy" come in one box and in another box all books from publisher "Allen & Unwin" and genre "Fantasy" come in another box. This admin staff will add the books by each box.... So box 1 had the first 7 Harry Potter books.... So all fields in the form have the same details except obviously the title...... Joe enters the first books details as follows:

BookID -auto (auto generated to be #713)
Title: Harry Potter: Philosopher's Stone
Author: JK Rowling
Country of Origin: United Kingdom
Language: English
Genre: Fantasy
Publisher: Bloomsbury Publishing

Then Joe press new record to get a blank new record.

Now Joe has requested that I create a button that first asks the user which BookID do you want to copy... He types 713, and then he wants Access to automatically look at record 713 and copy Genre and publisher into the new record. Then Joe only has to fill out Title, Author, country and language....

I have scoured the internet trying to figure this out because its something I have never done before..... I found a piece of code that does the reverse.....

Private Sub cmd_copyrecord_Click()


    Dim currentID As Long
    
    'TO DO: change all instances of 'BookID' with the actual name of your table's ID or primary key
    
    If IsNull(BookID) Then
        MsgBox prompt:="Please select the record to copy first.", buttons:=vbExclamation
        Exit Sub
    End If
    
    currentID = BookID
    DoCmd.GoToRecord record:=acNewRec
    
    'TO DO: set the fields to be copied (those that most likely will have the same values)
    'FORMAT: fieldName = Dlookup("fieldname", "tableName", "primaryKeyField=" & currentID)
    
    Genre = DLookup("Genre", "Books", "BookID=" & currentID)
    Publisher = DLookup("Publisher", "Books", "BookID=" & currentID)
    
    Title.SetFocus      'TO DO: change 'Title' with name of field that is going to be edited by the user
    
End Sub

But he doesn't want this... He wants to be on the new record, that looks back at an old record to copy and paste... The above code forces the user to be on the old record first and press a button to copy and create a new record.

Can someone help or point me in the right direction?

Thanks!

[June7]

An alternative approach: User opens box and enters first book, code sets DefaultValue property for each control when a value is entered, subsequent records will use those values when new record is initiated until user types something else or form is closed.

Otherwise, open a recordset of record 713 and read data from recordset into new record. Or use multiple DLookup() calls to table.

Even apply both approaches.

[Josha]

First thanks for your reply !

How do I do your second suggestion? How does one open a recordset - I don't want my users to touch the actual table the data is stored in...

[June7]

You should research use of recordsets.

Change the posted procedure to instead of prompting user to select a record to copy, uses:

1) an UNBOUND combobox in form header for user to select book
or
2) an InputBox prompt

I prefer the combobox because it is easier to validate input and code can reference columns of combobox to pull info. No recordset and no DLookup()s needed.

[moke123]

I think the easier approach is

An alternative approach: User opens box and enters first book, code sets DefaultValue property for each control when a value is entered, subsequent records will use those values when new record is initiated until user types something else or form is closed.

Private Sub Combo9_AfterUpdate()


    Me.Combo9.DefaultValue = Me.Combo9
    
End Sub

[Gicu]

You could add a list box to the form's header showing the books' title, publisher and genre. Then Joe can simply click on it to select a record then in the Current event of the form add something like this:

If Me.NewRecord= True Then
    If IsNull(Me.txtPublisher) Then Me.txtPublisher=Me.lstBookList.Column(1) 'second column of the list box
    If IsNull(Me.txtGenre) Then Me.txtGenre=Me.lstBookList.Column(2) 'third column of the list box
End If

Cheers,

[Josha]

Thanks everyone,

I decided going with Gicu's suggestion and my problem has been fixed

Thanks again =!