Multi-Duplication...

[roystreet]

Hello,
I'm not sure how to go about this or even exactly how to title this question...I have a database in which from time to time I need to do a 'mass duplication' Where I take one record and duplicate sometimes hundreds of times. The only difference between each case is a four digit number. How I go about it now is a series of queries. One query I put the initial case, but the field 'unique_number' (which is the four digit number) I do not include that. Instead it will an auto number in the table that the record will be appended into. I do use the unique_number field initially to set the autonumber to start at the right number, but after that autonumber goes up sequentially just fine. As I copy and paste these records I can copy more than one at a time and paste more than one at a time. Each time I can add a 'whole truck load' more. Then in the end I use a query that will place those cases back into the MasterTable. Since the MasterTable has an autonumber field in it called 'EntryNumber' I couldn't use another autonumber in the same table. Some of the records have what is called a 'local_number' of which I have to run through a query that appends to a table with an autonumber so that it is sequential as well. If you need me to further explain what I mean or if it my question isn't easily understood, please feel free to tell me. So, I'm trying to make an easy way (if possible) for someone to tell it what case (record) number to duplicate and how many times. But in this case, it will have to run through a series of updating the unique number and the local numbers behind the scenes. Obviously, how it currently works, only the database administrator can do this work - General users couldn't. Now, I don't know if I'd give them access to a 'wizard' anyway just because the chances of 'messing things up' will be multiplied, but I as the admin would like to have one.

Thanks,
---roystreet

 

[dhookom]

Roystreet,
Is there anyway that you can provide a small sampling of records and results to walk us through this?

Also, next time, try to break your posting into paragraphs. It makes your question much easier to read.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[roystreet]

Hello,
Is there a way I can attach a small version of my database here? I have been searching for that option and do not see it anywhere? I will keep looking, but that would be the best route - If possible to help you understand my question.

Thanks,
---roystreet

 

[ProgramError]

dim copies as integer
dim x as interger

copies = InputBox("Enter the number of request copies to add! (minimum 2)", "Question", "2")
For x = 1 To copies
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70 'select current record
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
Next x

message = copies & " requests Entered"
MsgBox message, , "Message"

Program Error
Programmers do it one finger at a time!

 

[roystreet]

ProgramError,
Thank you for your assistance. It didn't work at all the first time, but I figured out what the problem was:

dim x as inte[highlight]r[/highlight]ger

it should be:

dim x as integer

Anyway, that did duplicate the case exactly and quite quickly. I [highlight]WILL[/highlight] use that code in the future! The problem that I have now is the duplicated cases can't be exactly the same, I'll explain since I can't attach a simple version of the DB here:

Each record has several fields that specifically identify each record and are key fields;
Standard_Number & FY & Unique_Number
An example of a case would be:
NC-0061-AAL-10-0099
[ul]
[li][blue]NC-0061-AAL <Standard_Number>[/blue][/li]
[li][green]10 <FY>[/green][/li]
[li][red]0099 <Unique_Number>[/red][/li]
[/ul]


[blue]Standard_Number identifies the union.[/blue]
[green]FY identifies the Fiscal Year[/green]
[red]Unique_Number identifies that unique case for that specific union during that specific fiscal year.[/red]

There are other fields like; Name, Article, LOB, etc. When I refer to duplication, I need basically all of the fields exactly the same with the exception of the unique_number

They will all be during the same FY and within the same union, but there must be a unique identifier for each one.

My example would be for the above case number: NC-0061-AAL-10-0099 - I need all the cases to be exactly the same from the unique number from 0099 to 0110. In the past I would take the original case (#0099) and append it into a table of which the [red]unique_number[/red] is an [highlight]autonumber[/highlight]. This would 'force' the autonumber to begin at 99 and the next number would be 100 and on and on - for each record that I would insert in the table. So, I would just copy it and paste it back into the table.

Then, I create a 'new formatting' query that would format the unique_number so that it would always have 4 digits:

UpdateUnique: Format([Unique_Number],"0000")

(UpdateUnique would just be a temporary field name - Ultimately it would be Unique_Number in the master table)

I would use the 'new formatting' query to append those cases into the master table. This would give me the correct formatting and would also keep all of the data in tact as I need.

So, I will need to do some appending even if I 'automate' it more. I hope I've explained this better this time. In the end, the unique_number field is just a plain text field - In the master table. I may not be using the right method to perform this task?

Thanks,
---roystreet

 

[dhookom]

I would do this with an append query made up of a table of all possible integers, the max of the unique ID from your autonumber field, and the one record that you are copying.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[roystreet]

ProgramError,
I have further reviewed the code you gave me. I've noticed that it will only duplicate the fields that are visible on the form. This may not be a problem, but I do have some hidden fields that may have administrative notes fields that the user shouldn't see. Since that field isn't present on the form, it will not duplicate that information into the new records. Even if I include the fields on the form, but make them invisible - It will treat them as if they weren't there. Any ideas?

Thanks,
---roystreet

 

[dhookom]

I would never pass control of an application over to copy and paste.

I am not sure if this scenario can be compared to yours however, assuming (Northwind.mdb):
-you want to copy OrderID 10251 from the Orders table
-you have a table "tblNums" with a single numeric field "Num" with lots of records 1 through whatever
-you want to start numbering your new records at one more than the Max(ProductID) field from the Products table.

Try this:
SELECT [Num]+(SELECT MAX(ProductID) FROM Products) AS MaxProd, Orders.OrderID, Orders.CustomerID, Orders.EmployeeID
FROM tblNums, Orders
WHERE tblNums.Num Between 1 And [Enter Number to copy] AND Orders.OrderID=10251;

You could change this into an append query if necessary.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[roystreet]

So far, I have used the copy and paste method. Making a type of 'wizard' for the user...
[ul]
[li]First an event creates a copy of a template table that will become Temp01 table to store the cases[/li]
[li]Second, they select from a drop down of what case number to use as the 'basis'[/li]
[li]Next, an appending query takes that case and places it in a temporary table - Temp01[/li]
[li]Now, it runs the code that prompts how many copies do you want - The Temp01 table has the unique_number as an autonumber[/li]
[li]Next, a delete query deletes the orignal case out of the temporary table - so it doesn't attempt to place that table back into the master table[/li]
[li]Finally, an appending query places the new cases into the master table.[/li]
[/ul]
Each time, you begin the wizard it will try to create a new temp table - If one is already present, it will prompt to replace it and you say yes. This 'resets' the auto number to start with the one you need it at.

I resolved the problem with the visible fields by placing them on top of each other and then placing a box on top of them (then grouping them together). It covers them up and they don't have Tab stop enabled, they can't ever get to any of those fields. It's kind of a 'crude' way of doing, but it does the job quite well.
Also, in the admin notes field of the table - I coded the final query so that it makes note of what user used the mass appender (as I call it) and what date and also what case this one was copied from. Just as a little note to the Administrator of where this case came from.

Thanks for all of your help!! I greatly appreciate it.
---roystreet