I have a table with employees’ information. I want to copy all records in this table to another existing table “WorkTimes”. I do not want to edit my employee table, so therefore I want to copy all records to another table before editing/adding information in other fields. How I can do that with a macro or module?
Thanks in advance.
I have a need to copy a table structure from an existing linked table in the backend
i have done this by
a) copying the table structure into a new table in the fromt ebd
b) exporting the new table into the backend
c) dropping the table in the front end
just wondered if there was an easy way to do this directly in the backend?
I'm trying to copy records from another base into existing table in current base by:
Code:
DoCmd.RunSQL ("INSERT INTO pivot (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) SELECT (RFO_CLIENT_ID, FOLDER_DATE_CREATE, start_time, end_time) FROM svod IN 'Z:NPSNPS - Operator - 1.accdb' ")
But it doesn't run. Says insert into syntax error.
I have a form with a sub form. when a record is choosen in a combo box the sub form is filled out with a record.
what I am trying to do is have a button that will copy that record to a history table then delete it off the the main table.
I cheated by using the wizard to get the code to delete the record but I am having troubles modifying the code to copy that record to the history table. Here is the code below. I have tried to insert code in several places but it just errors out.
'------------------------------------------------------------
' Master_tbl_sub_fm
'
'------------------------------------------------------------
Function Master_tbl_sub_fm()
On Error GoTo Master_tbl_sub_fm_Err
With CodeContextObject
On Error Resume Next
[Code] ....
hey guys,
how can i make a query to copy records from one table to the other, and leave out one record. the record has corrupted somewhere and is causing havoc on some forms.
cheers
I have the employee data record... I want copy of the same record to insert in table but with different employee id and Site through forms. StaffNo is the primary key.
For example
existing record
StaffNo, Site, Name, hiring date, etc
AKA-111, 10, John, 25-10-13
new record
StaffNo, Site, Name, hiring date, etc
ALM-123, 14, John, 25-10-13
I am very new to VBA and I have been self-learning VBA from two month and have been assigned to new project Work Authorization & Lock Out Tag Out.I have table called tblWA & tblLOTO.Basically most of the LOTO/s are associated with WA #, following example
WA # in tblWA
Associated LOTO/s in tblLOTOs
25258
123456
123457
123458
123459
25259
123410
123411
123412
123413
However, both tables are in relationship right now..I have form called WA Switch Board where I have Datasheet View form with all WA records, fields are WA#, WAStatus, WAIssuedDate, IssuedBy, CompletionDate and etc but I have dropdown with with WAStatus with (In Field Today, On-Hold, In Approval, Cancelled, Close).Now from the main switch board when authorized people try to change the status of permit to Closed I want recordset or count to loop through tblLOTO and give me a message box if associated LOTO/s status not equal to Close.In other word, if the associated LOTO/s are not close then the selected WA # in WA Switch Board cant close.Here is the code I have been playing with no success
Private Sub PermitStatus_AfterUpdate()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
[code]....
Hi Everyone
I wonder if anyone can help me with code that i can use to copy a single record from table1 to table2
that i can use in a subroutine.
Thanks in advance to anyone that may offer some help
Kindest regards
Tony
Is it possible to copy the current record on a form to a different table?
Example:
Form Name = Training Orders bound to a table with the same name.
2nd Table Name = History
I need to export certin fields from the Training Orders Form into the History Table. Below is the way I am trying to make it happen, but it does not work.
With Me.RecordsetClone
.AddNew
![Forms]![History]![Last Name] = Me.[lastname]
.Update
Me.Bookmark = .LastModified
End With
End Sub
Is it possible to send a copy of a record to another table already created? if so what is the sql statements to do so or any other ideas that you might have.
THANKS!!!!!!!!!!!!
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.
I was wondering if deleted records to be copied to a new table?
View 2 Replies View RelatedI 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 RelatedAccess 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.
I have a form with 2 subforms, each based on their own table. One displays categories of invoices (e.g. rent, electricity, etc.) including some details like monthly costs.
I would like users to be able to select one of those categories and copy this to another table, after which they can enter on what date the invoice was paid, to make a history of payments.
At first I based fields in the history table on fields in the categories table so that you could simply pick a category from a combobox. I had an after update event on that combobox that also automatically set the 'costs' combobox to the matching price.
Worked fine, but had one snag: if I update the costs of a category in the categories table (e.g. the rent goes up) then all the costs in the history table was also updated because the fields got their info from that table.
So now I no longer have field from the history table based on the other table, and want to use 'set value' to copy values from one table to the other. I'd like the user to be able to somehow select a category with a single click on a button, and getting the info for that category copied. However, how can I get access to know from which record I want the fields copied?
Or is there a completely different way to get a history of payments that works much better?
I created an unbound text box on a form that automatically pulls the current logged in user by using this:
Private Sub Txtuser_Click()
Me.Txtuser = Environ("Username")
The form grabbs the logged in user with no problems, however, I ultimately want this information to also end up in my table. So the form has three boxes (to keep it simple). The user will type their first name and last name manually on the form which the record source is this "table" where their name goes to the table last name =Field 1 and first name=Field 2 respectively. I want the unbound box from the form to place the logged in user in Field 3 for the current record.
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 one DB that is used for creating/storing customer ID's, and another DB that is used for creating/storing job information for customers.I have linked the table from the customer DB to the job DB.
There is a table in the job database that holds customer name and ID, and some VBA that generates unique job codes.
Is it possible to have the data from the linked table automatically update into the existing table?
Hoping someone can help me with this DELETE query. I have a Main table that's being updated by a Temp table that's an exact copy of the Main table but with a subset of records.
1) Insert records from Temp table NOT found in the Main table - this query I have worked out below - not tested, but the results look correct.
Need Help Here...
2) Delete Records from the Main that are not found in Temp table with an exception...only DELETE records where certain key fields are matching. i.e. If S.CAD_NAME, lngStoreNumber are a match to what's in the Main table. While
Temp table:
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a
1 - "STK" - a
2 - "CHK" - a
Main table
lngStoreNumber - CAD_NAME - lngcomponentSerial
1 - "CHK" - a - LEAVE (EXISTS In Both Tables)
1 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found /lngcomponentSerial NOT Found in Temp)
1 - "STK" - a - LEAVE (EXISTS In Both Tables)
1 - "RMM" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "STK" - a - LEAVE (lngStoreNumber & CAD_NAME NOT Found in Temp)
2 - "CHK" - b - DELETE (lngStoreNumber & CAD_NAME composite Found/lngcomponentSerial NOT Found in Temp)
3 - "CHK" - a - LEAVE (lngStoreNumber = 3 Not in Temp table Subset)
Rule: Only delete the records for a particular CAD_NAME and lngStoreNumber from the Main table leaving all other CAD_NAME/lngStoreNumbers.
I'm running these updates in batches of lngStoreNumber. So the Temp table will only contain subsets of what's to be deleted from the Main table thus the need to link on the key fields only NOT to delete a Subset of lngStoreNumber/CAD_NAME. I think I've tried every possible query that doesn't work.
Here is query #1 to insert records missing from the Main table that exist in the Temp table. I think what I need is a variation of this???
SELECT D.*
FROM Main AS S RIGHT JOIN Temp AS D ON (S.CAD_NAME=D.CAD_NAME) AND (S.lngcomponentSerial=D.lngcomponentSerial) AND (S.lngStoreNumber=D.lngStoreNumber)
WHERE S.lngcomponentSerial is null AND S.CAD_NAME is null AND S.lngStoreNumber is null;
THANKS.
I am working from an existing database which is just two table. The main table has a massive amount of redundancy and duplication and needs splitting into, at first glance, 5 tables.
After I have run my various make table queries and added a Primary Key and FK field to the new tables how do I populate the FK with the Parent PK.
I thought I could simply add all the fields from the new table and then create an adhoc join in an update query to populate the PK to the FK. When I do this however I get "You are about to update 0 records"
I have tried the table analyzer but it doesn't give the correct options to split the table the way I need.
I have an MS Access accdb with linked SQL Server 2012 ODBC tables. I am working on a procedure to copy data from local tables to these linked tables (identical schema). I did a simple
Code:
DoCmd.RunSQL "INSERT INTO linkedTable SELECT * FROM localTable"
This works, but is very slow. Way too slow. (INSERT copies the data one record at a time).
I would like to copy the data in a bulk operation, or operations that I can execute programmatically.
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code:
Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database
' error handle
On Error GoTo F_Error
[Code] .....
My 2nd post, and I am very new to DB and Access. I have a problem that I want to get help on. I want to set up a main form that is used to enter and delete all data for my table. I wish to add either a button or to make it automatically happen when a record is deleted, that it is first copied to a separate table with the same fields, except it also has a closed date that would be the date that the record was copied over. I know zilch about VB, VBA or any other language other than AutoIt, so assume I am what I am, an ignorant beginner.
I did look into the event somethihng like upondeletion or something... while trying to find help on this in the access and VBA parts of Office, but I do not know how to utilize the event with Basic or SQL, which I know none of either.
Any help or examples are very much appreciated.
**EDIT**
I do not require all fields to be recorded to the secondary DB (History), so if someone can just give me an example of how I would move two fields to a separate DB, I can hopefully learn enough from it to do more.
Thanks a Bunch!
EXAMPLE**
Current Loans (Table 1):
CustomerID
Name
Address
City
State
Phone
Customer History (Table 2):
CustomerID
Name
Phone
That gives an example to help understand what I need. I want to store the CustomerID, Name and Phone values of the record being deleted, to the History Table, which I am using as a closed account table for later look up.
-Microsoft Access 2010
-Existing Access Database contains tables with 1-2 million records
I would like to add a field[dol] to an existing table[rei]. I need this new field[rei].[dol] to be populated with existing data from another table[main] based on the associated field[main].[account1] or [main].[account2] or [main].[account3].
Table1[main]
...account1
...account2
...account3
...dol
[code]...
In the [main] table. There is always data in [dol]. But there is NOT always data in the [account] fields. Sometimes there is multiple account numbers per [dol] but not always.
Customers will ALWAYS have at least 1 [account] number and [dol]. Some will have multiple [account] numbers and [dol]. Sometimes these [account] numbers are the same in multiple fields[account1] [account2] [account3].I just need to do a lookup or something to find the [account#] and pull in its [dol] from the [main] table and populate it in [rei].[dol].
I have a user who wants to automate copying a table from our main database. Basically, he is naming the new table with the original table name and the current date. For example, Part_Table_9-3-04, Part_Table_9-4-04, Part_Table_9-5-04 etc for each day of the month.
He wants me to write a macro, module, or vb code that automates the steps.
I have DoCmd.CopyObject,"Table1",AcTable,"Table2" I want to concatenate the date function (now) or (today) with the new table name but can't seem to get this to work.
Any suggestions?
Thanks for helping,
Jeff
How to copy a column from one table and insert it into another table in the same database
Hi, All,
I have two tables (old and new) sitting in the same database. The new table is the result of 'data cleansing' done by an external company. In the process (export and import via excel) two memo type colums in the table were truncated in excel.
To make the new table usable, I must therefore now copy/insert the two memo columns from the old table into the new table.
Both tables are already Access tables and sit in the same database. Both tables, of course, have the same number of rows.
I tried to high-light one column in the old table, clicked copy, then high-lighted a blank column in the target table, then clicked Paste, but got error msg: "This text is too long for this field. Try copying a shorter text", as if I had wanted to copy the whole column into one cell rather than one column into another column of equal length.
What is the best way to proceed?
Thanks for your help.
Adrian