Modules & VBA :: Copy Record As New In Large Number Of Tables

Jan 20, 2015

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.

View Replies


ADVERTISEMENT

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.

View 11 Replies View Related

Modules & VBA :: Loop - Copy Current Record By Number Of Times Specified In Quantity Field

Sep 20, 2013

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

Code:
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPaste

View 2 Replies View Related

Modules & VBA :: Fill In A Large Number Of Records

Jul 23, 2014

I need to fill in 200000 records counting from 100000,100001,100002.... and so forth, just one column (and maybe the auto numbering).

make a new DB with these columns: ID, counter
set counter to 100000 where ID=1 (in the first record)
move to next record (or make a new record)
if ID < 300000 then set counter = 1+ (the value of counter in the previous record)
continue until ID=300000

View 8 Replies View Related

General :: Copy Single Record In A Table And Paste It Predefined Number Of Times

Sep 25, 2012

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.

View 3 Replies View Related

Tables :: Copy Table Record To Another Record?

Mar 18, 2014

I have a database for staff to request checks to be cut. I have one table with a group of regularly used payees & addresses. A second table stores data for each individual check request. I need to be able to copy a record from the addresses to the check request. I do not want to add all addresses to the address table, as it is only for commonly used payees. So from the check request table, I am able to lookup a payee, which opens the address form to display the address. I need to be able to copy the displayed address to the open check request form.

View 3 Replies View Related

Tables :: Procedure To Restrict / Stop Auto Number Increment For Certain Number Of Record Count

Mar 16, 2014

I would like to know if there is any procedure to restrict/stop auto number increment for certain number of record count (say 50), then increment by 1 for next 50 records.

View 8 Replies View Related

Tables :: How To Copy A Record From Table A To B

Mar 16, 2015

I have a form to edit records on table A and i would like to be able to put a button on the form to copy that record from table A to table B. The code i have on the button so far is as follows:

<UserInterfaceMacro For="Command55" Event="OnClick">
<Statements>
<Action Name="OnError"/>
<Action Name="RunMenuCommand">
<Argument Name="Command">SelectRecord</Argument></Action>
<ConditionalBlock><If><Condition>[MacroError]=0</Condition><Statements>

[Code] ....

I want someone to be able to search a product for an order, edit it to reflect how many they are taking but at the same time have a button to copy that record to table B. Then after all the products have been found and copied to table B, they can print of the report based on table B and give to the factory floor so they can find the stock, run a query to clear table B and start on the next order.

I have everything working but copying the records across.

View 1 Replies View Related

Tables :: Use Checkbox To Copy A Record In One Table To Another

Dec 13, 2013

Access 2013

I'd like to copy checked records from one tale to another after a user presses a button.

Ex.
The table 'Equipment List' has a checkbox column that the user can check off as they scroll through the table on a form. When the user presses a button, after they are completed with all of the checkboxes, each checked record is then copied to 'Equipment Transactions' to keep a record of which pieces of equipment were used for the day.

View 1 Replies View Related

Modules & VBA :: Copy Record From One Form To Another?

Nov 14, 2013

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

[Code].....

View 3 Replies View Related

Modules & VBA :: Copy Current Record From Recordset To Csv

Aug 4, 2013

I have a DAO.recordset called "rsSQLIn". This comes from a csv file by:

Code:
strSql = "SELECT * " _
& "FROM [Text;Database=" _
& strFolder _

[Code]....

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"

View 3 Replies View Related

Modules & VBA :: Copy A Record As New And Change Certain Field

Dec 12, 2014

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.

View 1 Replies View Related

Modules & VBA :: Copy Subform Recordset To New Record

May 8, 2015

I am trying to copy a subform record set from one record to the next record. The data in the main table is copied over using this method;

Code:

Dim v1 As Variant
Dim v2 As Variant
Dim v3 As Variant
Dim v4 As Variant

v1 = Me![Today's Date].Value
v2 = Me!Insured.Value

[Code] ....

Can I integrate the copying of the subform data to the new record with this or do I need something different, and if so what?

It's a many to many relationship and I've tried adding the following line to the code:

v5=Me![Endorsements Umb XS].[UmbID]
same=v5

and it works but it only copies the first record out of the set.

View 14 Replies View Related

Number Too Large For Field

Mar 28, 2006

This number is too large [220020220020] for a field in my table. I currently have it set to Long Integer. What's the proper setting for a number this large?

Thanks

View 3 Replies View Related

Modules & VBA :: Copy And Paste Current Record Only To Excel

Jul 13, 2014

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?

View 8 Replies View Related

Modules & VBA :: Copy A Record Without Triggering Form Events

Dec 30, 2014

I have a form which has been in use for some time now, and works extremely well for all users; as part of a recent update to that form, I added a few events to various controls; most of them are message box prompts to remind the user to do something, but one is automatic entry of text into a memo field when another field is updated, and it is really this last one that is causing an issue.

There is a command button on the form which has an embedded macro, created using the wizard - it is probably the last, or one of the last few buttons that have a macro as opposed to VBA.

Essentially, when this button is clicked, I would like a duplicate of the current record to be made (thus giving it an Autonumber ID of its own), and if possible I would like this to be done without triggering any of the after update events on the form. Is there a way to disable these events and copy a record in its entirety, re-enabling the events at the end?

View 2 Replies View Related

Modules & VBA :: Copy / Paste Field From Previous Record

Jun 22, 2013

It is a continuous subform (Names: Form = ClientUpdate / Subform = ClientUpdateSub.

All of the data implicated here is direct and in the subform's query table including the button we want to program.

(This is my example but it wont stay in columns in this "post box" )

IDNo App _ID App_Freq App_Date GetDates Action_Date
22 18 56 21-Jun-13 BUTTON 14-Jun-13
21 19 56 16-Aug-13 BUTTON 9-Aug-13

*

The GetDates Command BUTTON should generate the red data above, like:

Private Sub GetDates_Click()

Go to a new record
Go to the field App_Freq and fill it with:
Copy/paste the data from the field of the same name in the previous record
Go to the field App_Date and fill it with:
data based on this calculation using the PREVIOUS record fields: App_Date + App_Freq (which are days)
Go to the field Action_Date and fill it with:
data based on this calculation: App_Date (of current record now filled out - step above), minus 7 (days).
End Sub

That's it!

All the dates format is set to medium date. An error message should come up in case App_Freq is empty, for example

Also note that the data can be manually changed at any time and should not revert to its last calculation. The button only generate data into a new record.

View 14 Replies View Related

Modules & VBA :: Export Function Not Working If Link To Different Copy Of Tables

Aug 21, 2014

I have a code module in my interface with 3 functions that run via separate forms with user inputs. Each function is similar in design, exporting data from a generated query to excel for input into external software.

The database is split into an interface FE (Access 2007) with linked tables BE (Access 2000). I have successfully tested each of the 3 functions in my code module with a set of tables containing fake test data. However, I have discovered that if I link to a different copy of the .mdb tables, 2 of the export functions no longer work and the third one still works fine.

What is causing the problem but the part that is not working correctly is the loop.

Here is my code module:

Option Compare Database
Option Explicit
Public Function CreateQCStaticChartsforReports() As Boolean
'Define variables for Static Chart creation
Dim qdf As DAO.QueryDef
Dim strSQLStatic As String

[Code] .....

View 2 Replies View Related

General :: Importing Txt File With Large Number

Feb 11, 2014

I am importing a delimited .txt file that has a number field. A value for a record coming in is 36,767 and Access is not accepting it. If I redefine the field as long integer or as double, I can manually update the record, but as soon as the file containing the record is imported, the field reverts back to integer.

How do I format the field with VBA so that Access will accept the value and not revert to integer?

View 3 Replies View Related

General :: Searching Through And Displaying A Large Number Of PDF

Sep 4, 2012

I'm wanting to create a way of searching through and displaying a large number of pdfs. These will be of different lengths and most will have images embedded in them. Each pdf will be categorised using a variety of fields to enable fairly sophisticated searches. I then want to link this database to a Joomla CMS website.

View 6 Replies View Related

General :: Get The Next Large Number In Access Field

Feb 19, 2013

I have a data where I want to create a query fulfilling the below conditions. Suppose I have two table: Table 1 and Table 2 If a value ex.98 (Table1) matches with the value with 98(Table 2),it should pick up my second higher value 103. suppose 103 is the next high value of 98 . Please see the data value.

misprepaid.asmvalue from Table2 Required Result Con 989898 then 103
if value of table1=98 then 103 from table 2 (next large number) 103103103 then 149
if value of table1=103 then 149 from table 2 (next large number) 149149149 then 175
if value of table1=149 then 175 from table 2 (next large number) 175175175 then 198
if value of table1=175 then 198 from table 2 (next large number) 198198198 then 199

[Code] .....

View 9 Replies View Related

Table Structure For Large List Of Number Fields

Jul 30, 2015

Have a look at this screenshot from excel.

Basically I would like to capture the quantity in stock for the above list of phones at many stores.

I started out by adding each phone model as a numeric field in tblStock, because I need to obtain the quantity value for each and every model, for each and every store.

Is there a better way to do this? I was thinking of creating just 2 fields, Model and Quantity, then adding each model as a record, then using that record as a sort of template. I wander what would be the drawbacks of this, since with the first method, if a user needs to add a phone not on the list he would have to modify the table design.

View 9 Replies View Related

Modules & VBA :: Copy Data From Excel Sheet To Access Table And Then Compare Two Tables

Dec 2, 2014

I have a form where when the user clicks on the browse button then excel workbook filepath gets stored in the textbox as below:

Code:
Private Sub CommandButton1_Click()
ChooseFile
End Sub
Sub ChooseFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)

[Code] .....

Please see attached the excel workbook. Everytime the user will select Excel workbook using Browse button. Now in that file , the first sheetname will always be "Summary". I want to perform the following steps:

1. So now I want VBA code to copy the data from columns "Withdrawn","Obsolete","Updated","LitRef" from Summary sheet to the Access table named tblSummary.

2. When the data gets copied in Access table then write So VBA code that will check if the data in field LitRef in table "tblSummary" is present in field "Reference" of Access table "tblliterature" . if its present then check in the tblSummary , which corresponding fields out of "Withdrawn","Obsolete" and "Updated" stores "Y" .
3. If "Withdrawn" field value is "Y" then change the status of corresponding record of tblliteraure to "Withdrawn"
4. If "Obsolete" field value is "Y" then change the status of corresponding record of tblliteraure to "Obsolete"
5. If "Updated" field value is "Y" then change the status of corresponding record of tblliteraure to "Updated" .

View 14 Replies View Related

Send A Large Number Of Reports (actually 1 Page Invoices) As Faxes?

Aug 8, 2011

I need to send a large number of reports (actually 1 page invoices) as faxes. A few years ago I used to use a version of WinFax Pro with command line parameters to accomplish this. I would actually print each invoice to the WinFax Printer with a command line that contained the fax number for that client and using this method I was able to send each invoice to a different fax number (customer).WinFax is no longer available.

View 1 Replies View Related

Convert Large Number To Binary For Use In TCPIP Socket Project

Apr 8, 2013

I am working on a project that requires data transfer using TCPIP sockets. I am using a 3rd party Library (Ostrosoft) that handles the wsock32.dll api calls.

The project calls for the creation of a header that logs into a user account.

The first part of the header is a marker, with a value of 4,275,878,552. It is to be supplied in a Binary format of length 4.

My quesion is "How do I create this marker".

What I have tried so far is a string variable - strMarker thus:

strMarker = ChrB(&HFE) & ChrB(&HDC) & ChrB(&HBA) & ChrB(&H98)

Using the ChrB function to convert the Hex version of that number (FEDCBA98)

View 5 Replies View Related

Tables :: How To Merge Large Data From Two Different Tables

Jan 1, 2014

So I have two tables,

table1: (company_name, company_code, year_month, rating)
table2: (company_name, company_code, year_month, asset, debt, equity,...)

What I would like to do is to call up all the data in table1, and then call up (asset, debt, equity,...) from table2 where the company_code and year-month are equal. What do you think is the best way to do this? Keep in mind a few things:

1. I'm dealing with hundreds of thousands of data lines
2. I may need to get more data similar to table2 in the future and call it up in the same way

I thought I should create a primary key "company_code-year_month"for both tables, but that method doesn't seem like a good one, and if I were to have another table, will have to create the same primary key for that table as well?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved