Modules & VBA :: Copy Record To History Table And Then Delete It From Main Table
Jul 9, 2014
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
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.
I have a parent table (tblLabels) and a child table (tblRevision) where the revision history for the parent table is kept.
The parent table is populated via an excel import and may have several records imported at once. Instead of having the user manually enter a new record note in the child table for each record imported into the parent table, I've created a form that collects the necessary data (date, person who added the record, person who authorized the record, and notes) and then creates a revision history for each new record.
This is what I have so far:
Code:
Private Sub cmdAddNotes_Click() Dim strSQL As String Dim RevisionDate As String Dim RevisionRevisedBy As String Dim RevisionDesc As String
[Code] ....
When I run the code nothing happens. No error, no new records create, etc. My suspicion is that I may need to use an INSERT INTO query instead of an UPDATE query, but I'm not sure how to go about matching up the foreign keys if that's the case.
I have created a form and need to be able to delete employees from a table. I built a command button using the wizard and this is the current vb code-
Code: Private Sub cmdDeleteEmployee_Click() On Error GoTo Err_cmdDeleteEmployee_Click
DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_cmdDeleteEmployee_Click: Exit Sub Err_cmdDeleteEmployee_Click: MsgBox Err.Description Resume Exit_cmdDeleteEmployee_Click End Sub
But when I click on the button I get a message saying it would create duplicate data in the table.
Where Status 1 = Open, 2 = Allocated and 3 = Closed. ---------------------------------------------------------------------------------------------- I want to get the count of number of queries which are not closed (outstanding) at any point in time.
Example: (with a time parameter) Input | Result 22/10/2007 17:00 | 1 22/10/2007 16:05 | 0 22/10/2007 14:00 | 2
I want to achieve this with just 1 query (not by using one query within the other) b,coz I want to further use this query from Excel VBA (write through Excel VBA and not store the query within Access)
Hello! I am trying to update the current status of an asset, when it was returned or checked out. At the same time, save the changes in a history table to record all the changes in past. I can do individually from different tables and different forms, but I would like to do from one form and one record entry. Is it possible? If so can anybody help? Thanks JVirk
I have a table that deals with current data (as in member rentals of items). The normalized tables that we have been given include a rental history table. The idea is that, when the member has returned the product a history entry is made in the completed rental table.
I wish to have a button which is clicked to triggers this event. Would I program a macro to do this? In other words, would I create an event that passes those values to the relevant fields in the other table (Name, date issued, return date etc)?
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
I have an employee and asset database. If an employee gets fired, I need remove them from the general employee records, but I want to save a record of that employee. Is there a way to delete an employee from one table and have it automatically added to another table?
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?
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.
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
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.
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:
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 have a main form that has some sub forms on it linked by master child on PK. When I select a new record on the sub forms I want to take a date value from the main form and populate the value into a field in the sub form. How do I do this as I cannot see a new record event on the sub form?
I have a database with two tables, and a form that is bound to the 1st table. I have a duplicate record button on the form. One of the functions I want to happen is when the duplicate button is pressed, one value is copied from the form and added to the 2nd table. The tables are basically set up the same with an ID column which is the key and an autonumber, and the second value is a number. So I want to copy the number to the other table and add a record. What is the best method? Can I use an insert into and just copy from a variable, or is the better method with a recordset. Either way, I have not been able to figure this silly thing out and it is preventing me from moving forward with this database.
Is it possible to delete a record from a table whilst viewing that table through a form. The awkward part though is because I have another table linked to it.
Basically the main table is Orders and the other table is Items (these items make up an order). So when I go to simply click on delete an error message appears saying that I cannot delete the record but there are records attached to it in another table. This is fair enough but is there a smooth way off deleting off the items?
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.
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.
