VBA Code for 5 copies from records to another Table (1 Viewer)

[imousen]

Hi.
I have a form and a button "Add to Estimate". like below picture:

Records are activity in a project.

I want when i click "Add to Estimate", make 5 copies from every records to another table, and create column name with "Type" to [M,L,E,S,O] for on record.
and finally text join in last column.

like below picture.

 

[Galaxiom]

Your requirement appears to be pointless. The UniqueCode can be generated form the information in the first table. Why do you need these records?

 

[Galaxiom]

If you were to have a related table with more fields it would have a Foreign key for ID and Type (use a better name as this is a reserved word). The UniqueCodes would be generated in a query. The codes in the first table would absolutely not being repeated in a second table. To do so would be a breach of normalization.

 

[arnelgp]

you add Type table then create a query (Query1) to concatenate the type.

 

[XPS35]

I totally agree with Galaxiom. You will first need to get your database design in order. The second table should only contain the key from the first table and no other fields from that table. Working with compound fields is also not a good idea if the underlying data is already elsewhere. You then record the same information twice. This is unnecessary and causes problems if the data in question changes.

 

[The_Doc_Man]

Basically, your question and example reflect "flat-file" thinking, like one would use for Excel. With Access, you can develop pointers (called foreign keys) to lead you back to a parent data source. Then rather than repeating a whole row of data, you simply follow the pointer when you need the parent item's data. The other posts are using this concept to explain what you should do. However, to understand WHY you should do this, I might suggest some reading materials:

1. Database Normalization - the process of organizing data to prevent its unnecessary duplication and to assure optimal layout.

2. Foreign Keys - the pointers from dependent data back to the independent data source

Side note: When search for info on normalization, you can search this forum for "Normalization" - but if you search the general Internet, you must search for "Database Normalization" because by itself, the word "Normalization" applies to math, chemistry, diplomacy, psychiatry, social work, databases, and a couple of other more obscure topics. So you need "Database" as a filter to keep away those other definitions.

 

[Pat Hartman]

The query created by @arnelgp then needs to be converted to an append query. The append query needs two arguments. One in the WHERE argument so that the correct records can be selected from the "from" table and the second is the ID of the parent of the "to" table which will be appended as the FK field.

The rest of the folks might feel better about this request if you explained the quote process a little