Copy values from one record to another

Topic

New Reply

Access 2007

I have been asked to create a feature in our database that:
1. Searches the database for a duplicate record based on one field
2. If found, the user is asked if he wants the values of the original record to be copied into thenew record.

For example, if this were a library database, assume that you have a record that stores information about a book (title, ISBN, author, etc). Let’s say the library purchases a second copy of the same book. Enter the ISBN and if it matches one that already exists, ask the user if he wants to automatically import the information from the existing record into the record for the second copy of the same book, saving time and possible data entry errors.

Is something like that possible?

I assume that the code for this would be very complicated, but perhaps someone has already posted something similar somewhere (I searched this site but was didn’t find anything.)

Thanks,

JoeK

Reply | Quote

Replies

Why should you have duplicated info in you database ? It’s against normalization. It seems that you database structure is not correct.

Reply | Quote

If you’re willing to approach it slightly differently, you could do it without code:

[*]Click in the control bound to the relevant field (e.g. ISBN).[*]Press Ctrl+F to open the Find dialog.[*]Type the value you want to search for.[*]Click Find Next or press Enter.[*]If found, select the entire record.[*]Copy it.[*]Paste Append the record.[*]If there is an AutoNumber field, it will not be copied – the new record will be assigned a new value.

If you prefer the approach you outlined, you’ll need code, and you’ll need to indicate which fields should be copied. For example, if there is an AutoNumber field, this should not be copied. If there is another type of unique key, you’ll have to specify how to set the value of this key for the new record.

Reply | Quote

I would suggest that you not add a second record as Gold Lounger pointed out, instead have field in your table for number of books. In your ISBN field on your form use the before update event when you add a new book and do a DLookup to see if the number exists. If it does, direct it to that record and update the quantity.

Reply | Quote

Assuming your example of library books is close to the mark, there is a potentially legitimate issue here. Whether you’re running a library of books or tapes or videos, what you really want to track is the copies of each item, not just the item itself. My library may have four copies of “Gone with the Wind”; where is each copy? Who has a particular copy?

While all the advice here is sound under particular circumstances, you really do need to evaluate the structure of your database vis a vis the business model you’re trying to replicate. A list of books (tblTitles) is obviously necessary from your description, but you will also need database structure to identify copies of the books (tblBooks) and link it back to tblTitles. This kind of structure allows you to keep track of the status of each physical copy, how many copies you have, etc. and still allow you to update the attributes of a particular title without violating normalization.

Hope this helps.

Reply | Quote

I think the distinction between books and copies is important, but it is not clear (to me at least), just which fields belong in which of the tables, and just when something is a new book, and when it is another copy.

Over time I have bought several copies of this book,

[*]the first was exactly the same as the ones I already had,[*]the next was a reprint with a different date[*]the next had a new forward, so there were more pages[*]the next was a new edition with an extra author added[*]the next was a paperback[*]The next was the illustrated edition with lots of photos etc

Which are these are just copies, and which count as a new book, but maybe with the same ISBN.

So it seems quite reasonable to want to copy an existing record, then edit some of the data in it.

Reply | Quote

Thank you all for your answers and comments. I decided to go back and review the structure of the database. It seems that I didn’t design it in the most optimal way. I am now in the process of redeveloping it, making sure to stick to the rules of normalization – which is what I should have done in the beginning.

It is a big job, but in the end it will be worth it.

Thanks again,

JoeK

Reply | Quote