[Webinar] Streamline your web hosting managementRegister Today

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
GiaHughes
Asked:
GiaHughes
  • 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
 
GiaHughesAuthor 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
GiaHughesAuthor Commented:
Hi John
Do I click on Module in the ribbon or something else
Gordon
0
 
GiaHughesAuthor 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
 
GiaHughesAuthor 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
 
GiaHughesAuthor Commented:
All ok now
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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