• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 54
  • Last Modified:

Can I automate duplication and renumbering in Access 2013

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
Gordon Hughes
Asked:
Gordon Hughes
  • 5
  • 3
4 Solutions
 
John TsioumprisSoftware & Systems EngineerCommented:
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
.AddNew
.fields("SpecialCodeDup") = specialCode & i  'You need some tweaking here like left(specialcode,10) & i & mid(specialcode,11)
.Update
next
end with
set rst =nothing

Open in new window

0
 
Gordon HughesDirectorAuthor Commented:
Hi John
I do not know how to create a function, can you help me
Gordon
0
 
John TsioumprisSoftware & Systems EngineerCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Gordon HughesDirectorAuthor Commented:
Hi John
Do I click on Module in the ribbon or something else
Gordon
0
 
Gordon HughesDirectorAuthor Commented:
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
 
Gordon HughesDirectorAuthor Commented:
OK I think I have found a workaround using Excel to do the replacement and then copy the records back into access

Gordon
0
 
John TsioumprisSoftware & Systems EngineerCommented:
This is also a solution...:)
0
 
Gordon HughesDirectorAuthor Commented:
All ok now
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now