Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Can I automate duplication and renumbering in Access 2013

Posted on 2016-11-15
8
Medium Priority
?
47 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 19

Assisted Solution

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

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 19

Assisted Solution

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

Author Closing Comment

by:GiaHughes
ID: 41898881
All ok now
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

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.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

610 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