Solved

Can I automate duplication and renumbering in Access 2013

Posted on 2016-11-15
8
32 Views
Last Modified: 2016-11-23
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
Comment
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
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 16

Assisted Solution

by:John Tsioumpris
John Tsioumpris earned 500 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
.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
 

Author Comment

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

Expert Comment

by:John Tsioumpris
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

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

Assisted Solution

by:GiaHughes
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

by:
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 16

Assisted Solution

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

Author Closing Comment

by:GiaHughes
ID: 41898881
All ok now
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question