Creating a new record from copied form and subform in one step

[KLouise]

I am trying to copy and paste a record into a new record in form view including the subform at the same time.

(I'm going to use an example of a database of books as an example so I don't disclose anything I shouldn't)

The main form has all the details about a book for example, such as title author, ISBN, date published, etc and the subform has a list of the categories that can be used to search for the book (such as crime, sci fi, etc).

The main form is based on the 'main table' in the database which has a one to many relationship with the table which has the categories in for each book and which the subform is based on.

I can copy and paste the main record by selecting it and copying it into a new record but I have to go back to the original record to copy and paste the category information into the subform.

Is there an easy way I can do both in one step either using the tables or the forms?

 

[Swemebegur]

Hi KLouise
Why are you wanting to copy all the details of a book (or whatever) into a new record?
Is it because you have a separate record for each instance of a book? Might it not be simpler to have one record for the book with a quantity field?

Swemebegur

 

[KLouise]

Hi Swemebegur

Because they may be different formats (hardback, paperback, audio, etc)

 

[Swemebegur]

Hi KLouise

I must admit I am still not convinced. Can you show us your table structure?

Swemebegur

 

[KLouise]

I want to copy it and paste it into a new record in instances where they are two separate 'books' but share the same main information including the categories they have assigned.

We already do this, but have to copy the main form/table and sub form/related table entries separately... I just want to try and do it in one step instead of two...

I don't have the structure to hand at the moment but I'll see if I can upload someting later.

 

[RainLover]

Because they may be different formats (hardback, paperback, audio, etc)

You can always add an extra Field to describe that it is Hardback etc

 

[KLouise]

We keep a different version of the record to correlate with each different version of the 'book'.

The subform/category table lists a combination of different categories to define the 'book' that might be used to search for it - such as hardback, crime, fiction...

Each 'book' can have an unlimited number of these categories assigned so there is a one to many relationship with the main table and the category table.

The search that this then goes into is similar of that used by online companies like comet... so in this example you have a main form/table for the appliance (such as a washing machine) and a table for the categories that can be used to search for that washing machine on the website. If you already have one record for both these for the washing machine in white and the only information that differs is the colour, when entering the data for a black or red version it would be easier to copy it than create a new one from scratch.

 

[RainLover]

I would add another Table, One to Many that stores the different Colours.

So this is one field rather than a whole new record.

 

[Swemebegur]

Hi again KLouise

I see now what you want to do.

One way would be to use an append query to create the category records. This would select records from the book/category table with the old book's id as their foreign key, then append these to the same table but with the new book's id.

I have done something similar but where the copying was always from the sub-records of the immediately preceding main record - ie it was simulating the carrying forward of the previous record's data as default values.

I will see if I can adapt that to your requirement.

Swemebegur

 

[jdraw]

You might want to step back and check your design. To help give you a broad perspective to help get your particular requirements and design into focus see this free model

http://www.databaseanswers.org/data_models/libraries_and_books/index.htm

This is just for your consideration. This is not saying to change your database.

 

[Swemebegur]

Hi KLouise

I have been playing around with the copying problem, and here is a .mdb showing how it might be done

HTH

Swemebegur

 

[RainLover]

The Table BookCategoryTable has two fields that form the Primary Key.

This is wrong. They should both be Foreign keys. Then add a New Primary Key of type Autonumber.

IMO all Primary Keys should be Autonumber.

You should also look at Naming Conventions. There is a link in my signature that will take you to a Folder that has an article that explains a Naming Convention. Suggest that you read.

 

[Swemebegur]

Please RainLover, your statement is nonsense. There is nothing wrong in using natural keys. Using surrogate keys is just your opinion, nothing more. But I do not want to get into that stale old debate, which has been thrashed to death over the years. And I happen to use a different naming convention from yours, which is not cast in tablets of stone.

Swemebegur

 

[RainLover]

Please RainLover, your statement is nonsense. There is nothing wrong in using natural keys. Using surrogate keys is just your opinion, nothing more. But I do not want to get into that stale old debate, which has been thrashed to death over the years. And I happen to use a different naming convention from yours, which is not cast in tablets of stone.

Swemebegur

You say my Statement is nonsence. That is a bit rough. You could say the same thing in a different way. BTW It is not just my opinion. I would guess that the community is divided fairly equally over the matter.

You are correct to a point that there is nothing wrong with Surrogate Keys and you can use them if that is your personal preference.

If you read the Naming Conventions you should have noted the opening statement which I will now post for your enlightment.

Naming Conventions.

This document is aimed at the user who is unfamiliar with any naming convention.

It is based upon personal experience and the interaction I have had with others.


It is not a hard and fast rule.

Note the last line. It is not a hard and fast rule. Also people are encouraged to develop their own system. The important thing is that they have a convention and follow it.

If you feel inclined you could write your version and have it posted in the archives.

There is room for an advanced version for people other than newbies.

See how it is possible to disagree without being rude.

 

[RainLover]

I just realised that other than me you are the only one attempting to help.

I can see why you may have taken a little offense with my correction of your design.

One thing I will stand fast on is the use of a Composite Primary Key.
That method is not good programming. Should you need to use one in order to meet your needs then use Autonumber instead.

 

[Swemebegur]

hi Rainlover

If you think I was rude it was because I found your statements arrogant as well as inaccurate. You did not write "I think this is wrong", you wrote "This is wrong". And you implied that I was not using a naming convention, apparently because it was not the one you happen to favour.

As I said before, I am not going to be drawn into a sterile debate about keys or programming style. So let's drop it now.

Swemebegur

 

[jdraw]

I am trying to help as well. I'm not convinced KLouise has a clear view of WHAT she/he is trying to do. While I like to work from a data model, naming conventions , a clear understanding of the issue and some relevant test data, I'm not yet convinced that this thread is, at the point in the interest of the poster, at the level of the pros and cons of natural vs surrogate keys.

Can anyone state in a sentence or 2 what exactly is the requirement, and can we get KLouise to confirm? Nothing like jumping in, however well-intended, and solving the wrong problem.

I'm all for opinions, advice etc whether they agree or disagree with my own -- you can always learn from anyone if you're willing to listen.

I like Rain's Naming Conventions and have recommended them to others. They are an approach - not an edict.

This is not a criticism of anyone or their opinions -- I just want the problem clearly stated so we are in a position to help.

 

[RainLover]

Swemebegur

I don't know why you are so aggresive.

The Naming Convention was a suggestion nothing else.


When I said This is Wrong it was and still is wrong. One Links a Primary Key to a Foreign key. One does not link a Primary Key to a Primary Key. It is that simple.

 

[RainLover]

jdraw

You are right when you say the OP has not stated clearly what his/her objective is. As you know this can be difficult when you don't know the terminology.

The OP stated;

I don't have the structure to hand at the moment but I'll see if I can upload someting later.

I have not seen anything as yet.

I do get the feeling though that the OP has Normalisation problems. Copying records to the same table worries me in this regard.

The only thing we can do is wait for the OP to stick his/her head up again.

PS Sorry I did not acknowledge you earlier when I said the Swemebegur was the only person helping. I was trying to be nice but it back fired.

 

[jdraw]

Rain,

I was just trying to get the thread back on track. There are many ways to skin a cat, but wanted to make sure we're all talking about "cats".

Swemebegur said

I see now what you want to do.

Whatever it is/was that he saw went right passed me, and I was hoping to get some confirmation from the original poster as to a clear statement of the issue.

Philosophies, debates, approaches tend to get subject, and often from experience, and make great discussions. But let's get the problem defined, or at least help the poster see that we only know what he/she tells us. We don't know the environment, the possible business issues, the poster's expertise or background; or even the mother tongue in many cases.

Sometimes I think - if the poster only knew how to Google a little better, or at least first, or had some experience in general problem solving - they would appreciate the need for a description of the "problem" with some context, before any meaningful responses are made.

Sometime people just need to be pointed in a direction. Sometimes they have a clear, well articulated technical, syntax type issue. Sometimes they have to be told that a search engine with a few parameters will get them several pages of relevant information with examples.

Let's see if KLouise can shed more light on the issue at hand.