Solved

# Can I automate duplication and renumbering in Access 2013

Posted on 2016-11-15
Medium Priority
42 Views
Ok I have records that contain a total column which varies
I would like to duplicate the record by the quantity shown in the total column
Ie
Total 9
Currently hold one record, need it duplicated a further 8 time
The Equipment no would then say GR-A0302-OUT012-9-B 9 times
Then the GR-A0302-OUT012-9-B would need to say
GR-A0302-OUT012-1-B
GR-A0302-OUT012-2-B
GR-A0302-OUT012-3-B
GR-A0302-OUT012-4-B
GR-A0302-OUT012-5-B
GR-A0302-OUT012-6-B
GR-A0302-OUT012-7-B
GR-A0302-OUT012-8-B
GR-A0302-OUT012-9-B

The question is can this be done with some query/script of do I have to do it manually?
Gordon
0
Question by:GiaHughes
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3

LVL 17

Assisted Solution

John Tsioumpris earned 2000 total points
ID: 41887688
You will need a function
``````Dim rst as dao.Recordset
Dim specialCode as string
Dim counter as integer
set rst = currentdb.OpenRecordSet("TheNameofYourTable")
with rst
specialcode = inputbox("Enter Special Code")
counter =inputbox ("Enter how many times to duplicate")
for i = 1 to counter
.fields("SpecialCodeDup") = specialCode & i  'You need some tweaking here like left(specialcode,10) & i & mid(specialcode,11)
.Update
next
end with
set rst =nothing
``````
0

Author Comment

ID: 41887722
Hi John
I do not know how to create a function, can you help me
Gordon
0

LVL 17

Expert Comment

ID: 41887741
on the form you will use to initiate the function ....insert a button...on the click event...copy paste what i have wrote above...
0

Author Comment

ID: 41888250
Hi John
Do I click on Module in the ribbon or something else
Gordon
0

Assisted Solution

GiaHughes earned 0 total points
ID: 41889279
Hi
I am sure the function would work but on the basis I would need to enter each unique record it would take ages as there are 17k records

Maybe I could copy a group of records and do a replacement. Sounds OK, but a replace replaces all in the selected filter
Am I able to replace a value in only the selected records
IE:- I copy 2k records and paste the 2k below the original records, then do a replacement on the copied records only, currently it seems to change all the records within the filter, ie it would change the 4k

Any ideas
Gordon
0

Accepted Solution

GiaHughes earned 0 total points
ID: 41889288
OK I think I have found a workaround using Excel to do the replacement and then copy the records back into access

Gordon
0

LVL 17

Assisted Solution

John Tsioumpris earned 2000 total points
ID: 41889295
This is also a solution...:)
0

Author Closing Comment

ID: 41898881
All ok now
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
###### Suggested Courses
Course of the Month14 days, 1 hour left to enroll