Modules & VBA :: Copy Record N Number Of Times - Incrementing Date
Jun 3, 2015
Is it possible to copy a record 'n' number of times, incrementing the date by either days, months, weeks etc?
I have a regular payments table that will need to be edited at some point should either dates, or amounts change, but... regardless of the size of the table, it's not a problem, as these will be moved to a different table and deleted once paid.
Given the example, would it be possible to copy this record and additional 5 times, incrementing the date by one week.
I would like to do a loop but never done one before, basically i want to copy the current record by the number of times specified in a quantity field
So if the quantity field in the record says 5 then copy that record 5 times (I have managed to create the copy and paste code but dont know how to make it do it 5 times
I would like to be able to copy a single record in a table and then paste this record a pre-defined number of times 10,20,30 depending on requirements. The reason I need to do this is that I want to set up a number of identical records that can easily be amended into unique records. This will vastly reduce the amount of work and time spent entering records individually.
The only way that I have been able to achieve this is to copy and transfer the master record into Excel, and then copy the row and paste it into a range of cells. Copy the range of cells back from Excel and then paste this range back into the Access table.
This works but is long winded; what I would like to know is is there a simply way of achieving this.
I have "Master" table with fields "Job No" and "Revision No". Both together is a primary key, so that combination of both cannot be duplicated. I have 100 other tables to be related with referential integrity(+update&delete) to Master for both fields. Apart from Job No and Revision No, all 100 tables have different set of fields which is why I had to come with so many tables.
Due to 32 limit rule, I had to come up with workaround method to have all 100 tables in the relationship. So, I created 5 other SubMaster1, SubMaster2, ...., SubMaster5 which are related to Master with relationship with referential integrity (+update&delete). Then I assigned 20 tables to each SubMaster so that 20 tables are related to each SubMaster table.
Whenever I create new record in Unit, the new record is generated in each SubMaster using update query for each SubMaster table. I have all the forms and necessary query laid out. The only missing part is being able to duplicate a record. I have limited knowledge in VBA, but I should be able to modify it to address to my requirement.
I want to copy a given record in Master, SubMasters and 100 tables as a new record. I need this feature so that I can select certain Job No and Revision No and copy that as a new Job No(assigned manually in a form) and 0 as the revision number. Possibly a button which will ask for new job number and copy everything from the active Job No and Revision No to a New Job No and "0" Revision No. The existing record may not be there on all 100 tables for the given Job No and Revision No. If it is there, then copy otherwise ignore for each of the tables.
I have a table "ItemList" which lists all the unique name of the 100 tables.
What I would like to do is have a field in a given record increment itself every time that record is selected. For example, say you have a table that stores items in the inventory: each item has a times_search_for_field.
Now, lets say a user runs a search which results in the following SQL:
SELECT * FROM Inventory WHERE experation_date=Today();
Now I want to have every record returned by that SQL statement increment it's times_search_for_field incremented by one. I'm just looking for the most efficiant way to do this.
I know I chould just have my asp .net code loop through the results and call an SQL UPDATE statement for each record, but it seems there should be a more efficient way of doing this.
I want to limit the numer of times a record can be entere into a table. After a record has been entered 3 times, I want an error message or validation rule to tell me it can't be done.
I want to use to count how many times a record is displayed in the period and lesson field, in another table, so that this data can be used to show how many people are attending a class. e.g how many times people are attending art 1, by counting how many times art one appeared in the first table.
At the moment I am working a little project at work and am stuck on how to calculate departments due date based on actual order due date.
example:
OrderID: xxxxxx-xx DueDate: 10/8/13
Now let say i have 5 different departments but I want/need to know what should be the due date for each of those departments based on the due date on top.
I am adding two records to an empty table using an append query (or make table query). I would like the first record to have 1 in the ID field and the second to have 2. I will then delete 2, append 1 elsewhere and empty the table and start over again.
Any suggestions? It needs to be 1 and 2 each time not just an autonumber.
I have a database created that is used to catalog different projects. When the user adds a new record and that project is already in the database it gives the user a message stating that that project already exists. It gives the option of cancelling and goes back so the user can name it something different. Or the user can continue and it creates a duplicate copy.
Instead of creating a duplicate copy, I want it to increment a number field named [Duplicates] by 1.
Example: If project 'A' is already in the database and the user adds it again it increments the duplicate field by one. So now the properties of project 'A' shows that there is 1 duplicate. Does that make sense?
Here's my duplicate code so far:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Response As Integer
If Not IsNull((DLookup("Project_Name", "Development_Projects", "[Project Name]= '" & Me.Project_Name & "'"))) Then Response = MsgBox("That Project already exists" & vbCr & vbCr & "Select Yes to duplicate record, No to cancel.", vbCritical + _ vbYesNo + vbDefaultButton2, "Duplicate Data")
If Response = vbYes Then [Duplicate] = [Duplicate + 1] Exit Sub Else Cancel = True End If End If
Hope you can help, I have been looking on the forum but with no luck. I am trying to design an append query that adds any number of records to another table but need one of the fields to increment by one for each record, so for example;
I append 10 records and the other field increments from 060,061,062 etc for each record
I want my db to generate our reference # automatically based on input from other fields. The number is the date record entered, the users initials, and an incremental number. Ex. 120505GMM02
My form is based on a query and I have the query generating the first two components from the date entered in reqdate and the initials entered. How can I get an incremental number generated to go with the rest.
The number should start with 01 for each day and also for each user. Therefore when GMM is entering his third record on the 5th it should generate 120505GMM03 even if there were 10 other records entered on the fifth by other users. Each users numbers would start at 01 for each day.
I asked this earlier but thought I would restate my question in hopes of getting my thoughts around this. I appreciate your patience with me.
I have a table, Purchases, which has a 1:N relationship with another table, Items. Each purchase can have multiple Items.
'PurchaseID' is the primary key of my Purchases table.
The Items table has a composite primary key, made up of PurchaseID (making it an identifying relationship) and ItemID.
As you can see, I have ItemID starting reverting back to 1 each time PurchaseID increments. PurchaseID is an autonumber on the Purchases table, however ItemID is just a Number and has to be entered manually. If I set ItemID to an autonumber, it will continue to increment even after PurchaseID changes.
What I am trying to do is to get ItemID to auto-increment but revert back to 1 each time PurchaseID increments.
The entry form for both looks like this:
The Record Source of the main form is the Purchases table, and the subform is a Multiple Items Form based on the Items table. Filling out the form will create a new Purchase record with an auto-incremented PurchaseID, and a number of new Item records with that same PurchaseID, and whatever ItemID's are specified in the subform. Currently the ItemID's have to be entered manually as 1, 2, 3, etc.
Is there a way to get them to auto-increment, but begin from 1 for each new Purchase record?
Been looking around for a solution for a custom ID and can't quite find one that's specific to my problem.
I am creating a log and I want to give each new log entry a new log number. The format would be: IEyy-xxx. The ID is the letters IE, then 2digit year, a dash, then 3digit incrementing number. The ID should reset back to 001 every year (e.g. if IE14-623 is last log entry of 2014, first log entry of 2015 would be IE15-001).
My table has an autonumber that I will use as the primary key- which is not the log number- and looks like this:
These are the fields in my table:
Index Log Number (autonumber and primary key) IE number Program Name Date Created
Is there any way it would be possible to have a number automatically created using NZ() or DMax function; based on certain criteria and also when creating the number preventing a duplicate number creation in the even of a simultaneous record creation?
Here's my example:
I have a CCN Number (Currently the employee manually types it in; and it is the primary key. What I would like is to take out the human element (error possibilities) and let access create it for the employee.
Here is how the CCN looks and the breakdown of each section (the important part's will be the first 3 sets of numbers):
96-06-184-100-000
96 = Region Code 06 = Current Year 184 = Julian Calendar Date (based on the receipt date field) 100 = Filler number based on day of the week (Eg. Monday = 100, Tuesday = 200, etc.) 000 = Filler numbers (Should always be Zero's (000)
I need to find away to pull this information from a table of some sort and combine it to create the CCN and to then add 1 (incriment) to the 4th set of numbers. Example 100 (1st record of the day) 101, 102, 103, etc.
I've created a report and report has the same number of pages as the number of records that it's displaying.
If there are two records, the report has 4 pages...the first 2 are the actual report and then the other 2 are a copy. If there are 3 records, the report would have 3 copies (...6 pages).
Any idea how to change this so that I have only one copy of the report?
My access database is used to process vehicle trips. Each shift, a vehicle is assigned a docket number (paper based), and on this docket the driver fills out each trip he/she makes. In a shift (thus on a docket) there may be 5 - 15 trips. I want to create an incrementing number for each trip in a new field - can be created as they are processed or after the fact by looping through the resultant data - whatever is easier.
So in simple terms, return all trips for each docket number then number each trip.
ok so I have two forms open: One is a SP Bookings form with a subform SPDetails Query subform. The other form is the not connected/related SP Contacts Query form, to which users enter Full Name weight age ect. Need to copy the names of the new customers Full Name to the Lookup Contact Column in SPDetails Query subform. I got this far, with my trial runs are commented out. The only thing it does when I click on the button is move to new record on Sp Contacts Query Full Name and Copy the previous record. How do I get the button to copy the Full Name from form SPContacts Query.Full Name to the SPBooking Query.subform SPDetails Query Subform.LookupContact
Code: Private Sub Command52_Click() Me.Full_Name.SetFocus DoCmd.RunCommand acCmdSelectRecord
While the validation runs a boolean keeps track of validated input and errored input.
After validation the validated input is dumped in the table.
Now what I want is de saving the errored record from "rsSQLIn" to be copied to a new .csv file.
The problem I have is that I cant seem to get the current record from the recordset "rsSQLIn". How do I reference this? I need the complete set of 24 fields being the same within "rsSQLIn"
I am trying to copy a record as new record in vba in access so i make a button for the user so that they can copy a record each time and change a certain field if they wanted. How would i do that.
Here's my problem. I have a table that is for maintenance jobs that recur on a fixed schedule, i.e. cleaning gutters yearly. I need to run an append query that will take all the information and cause it to recur a given number of times. I've gotten that to work with just a standard append query pulling data from a form.
The part I'm having trouble with is this. I need to get that append query to take the start date for the job and increment it properly based on my selection of daily/monthly/yearly recurrence for each individual occurance of the job.
Its been a few years since I used Access, and despite searches I cant find what I'm looking for. I have a VERY simple form, with only one record on it - "address". I have some print buttons which will print that address to different size labels, and some navigation items, but only one actual record - which is "address" (in the table, this address is a MEMO)
I would like to have a command button that when clicked copies the current record, and opens an existing Excel Spreadsheet and pastes that address into (for example) cell C8
Can it be done easily? Is there a DoCmd.TransferSpreadsheet kind of secnario that works with current record only?
