Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 51
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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