Active Table And History Table, How To Copy On Deletion?

Feb 14, 2006

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.

View Replies


ADVERTISEMENT

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

[Code] ....

View 8 Replies View Related

Modules & VBA :: Parent Table Revision History - Insert Multiple Records Into Child Table

Sep 8, 2014

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.

View 14 Replies View Related

Temp Table Deletion

Mar 28, 2005

Hey all-

I'm trying to create a simple 1 field temp table to populate a combo box with the name of the current user and the word "Company." However, after the user closes the form (or as soon as the Temp table is no longer necessary) I would like to delete the table. I can create the table, the fields, add the data, and populate the combo box just fine, but I'm having problems deleting the table after I'm done. I keep getting the error:

Run-Time Error 3211: The database engine could not lock table 'Temp' because it is already in use by another person or process.

here's my code:

Code:Option Compare DatabaseDim dbRoofing As DAO.Database Private Sub Form_Close()dbRoofing.TableDefs.Delete "Temp" 'where i get caught when i close the formEnd Sub Private Sub Form_Open(Cancel As Integer)Set dbRoofing = CurrentDb Dim tblTemp As TableDefDim rcdTemp As DAO.Recordset Set tblTemp = dbRoofing.CreateTableDef("Temp")tblTemp.Fields.Append tblTemp.CreateField("Owner", dbText)dbRoofing.TableDefs.Append tblTemp Set rcdTemp = dbRoofing.OpenRecordset("Temp", dbOpenDynaset)With rcdTemp.AddNew!Owner = CurrentUser.Update.AddNew!Owner = "Company".Update.CloseEnd WithOwner.RowSource = "SELECT Temp.Owner FROM Temp"End Sub

thanks guys

View 2 Replies View Related

Modules & VBA :: Deletion In Table From Input Box

Mar 1, 2015

I'm having trouble executing a SQL command in VB... I want it to find the the value of the input box in TBL-Purchases and Delete all related values. Here's my code.... I get an error on the line I've highlighted in green...

Private Sub Command31_Click()
Dim Message, Title, Default, MyValue1, MyValue2
Title = "Sell Stocks"
Default = ""
MyValue1 = InputBox("Which stock ticker name would you like to sell?")
MyValue1 = UCase(MyValue1)

[Code] .....

View 9 Replies View Related

Table With Key In Two Fields - Record Deletion

Oct 21, 2015

I have a table whose key is two fields. I am having no luck in selecting a given record from a form and deleting it. No problem in a table with a single field key.

View 14 Replies View Related

General :: Unwanted Deletion Related To Particular Record In Conversation Table

Mar 6, 2013

I have 2 tables.
1- customers table with 2 fields : customername,customerno
2-conversationstable with 4 fields: date,customername,customerno,details

The conversations table is for keeping memo of telephone conversations with the customers.

I built a simple form deriving from the conversations table. And added to it a combobox with 2 columns from customers table to select the customername an customerno for the form.

While deleting the record in the conversations table,through the form, I saw that the customername and customerno in the customers table of that particular customer record are deleted also. I made no links between both tables.

I wonder why that happened.

View 1 Replies View Related

Building History Table...

Mar 7, 2005

Hello.
I'm building a history table to keep track of some changes that occur on one of my forms.
Here is what I'm using to build my history table:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblDateCycleTestingChanges", dbOpenDynaset)
With rs
.AddNew
![CycleTestingDatesID] = Me.CycleTestingDatesID.Value
![Changed] = Now()
![ClientID] = Me.ClientID.Value
![ProjectID] = Me.ProjectID.Value
![TaskID] = Me.TaskID.Value
![TaskStartDt] = Me.TaskStartDt.Value
![TaskEndDt] = Me.TaskEndDt.Value
.Update
End With
Set rs = Nothing
End If
I have 2 questions:
1. I'm not sure where to insert this event. I'm thinking on Before_Update on the updated field? (I don't want to put it into Before_Update for a form event as I have other things that are being filled out/changed and I only want to keep track if certain fields on the forms are updated/changed.
2. I would also like to keep track of Old and New values for those specific fields. Is that at all possible?
Thanks.

View 5 Replies View Related

Creating A History Table

Sep 26, 2006

Hi, I'm new to the forum.

I would like to know how to create a history table that will copy information from a field called "Status" on my form just in case I inadverently erase old information from that field without me being aware of it with my keyboard keys etc. In others words when I'm am interrupted by someone and I didn't notice I had erase the information by mistake and closed out the form and realized when I go back into it, my old information have been erased and I don't have that information documented anywhere else to re-enter that old information into the Status section on my form.

Thanks.

View 1 Replies View Related

Getting Record Status From A History Table

Oct 22, 2007

Dear All,

I have a MAIN table which stores the most recent info of a record with
following details:

Unique_Ref_Num|Status|Dept
1 |6 | 1

and a second table called history which records changes in the main table

HistoryID | Status | DateStamp
1 | 1 | #22/10/2007 09:00#
1 | 2 | #22/10/2007 09:01#
2 | 1 | #22/10/2007 09:05#
2 | 2 | #22/10/2007 09:06#
1 | 2 | #22/10/2007 11:00#
2 | 3 | #22/10/2007 15:00#
1 | 3 | #22/10/2007 16:00#
2 | 2 | #22/10/2007 16:10#


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)

Any help will be greatly appreciated
--

Many Thanks
Baapi
:confused:

View 4 Replies View Related

Update A Record Of One Table And Save The History Of Changes In Another.

Jun 10, 2007

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

View 4 Replies View Related

Access Table Design Help - Franchise History

Oct 27, 2006

I'm struggling with a table design. My problem is how do I keep the history for one sports franchise no matter how many times they move or change their name? I want individual team info as well as franchise team info.

i.e.

Team #1 Brooklyn Dodgers has many transactions

Brooklyn moves to LA

Team #2 is Los Angeles and they have many transactions

etc, etc.

How would I build my tables if I wanted to write SQL statements to give me all transacation from the entire franchise? I only need help with the teams. I already have a junction for the transactions.

ie All Transactions for all teams who started with Brooklyn. This will grow to 5-6 teams eventually.

My initial idea was a self join in the teams table

Code:tblTeamsTeamID PKTeamNameHistoricalTeamID

TeamID = 1
TeamName = Brooklyn
HistoricalTeam ID = 1

TeamID = 2
TeamName = LA
Historical Team ID = 1

etc.

I hope this makes sense. Any suggestions?

Thanks
Rob

View 4 Replies View Related

Append Value To History Table And Update Current Value

May 18, 2015

I have form1 that has the current commodity When i need to change the commodity I want the following to happen when they click a button: open the commodity change formthat form has a drop down to select a new commodity and a date of commodity change date fieldWhen the user submits it will move existing value to history along with the Date of change (separate table)Next it will take the value in the new commodity box and make it the current commodity

My thinking is that when i click the button to open the change form I can save the commodity at that time but I wont have the date yet. Just not sure of they best way to go about this.

View 6 Replies View Related

General :: Creating History Record From Another Table Values?

Jan 20, 2014

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)?

View 9 Replies View Related

Tables :: Setting Up History Table For Contacts And Companies

May 15, 2013

I need setting up a history table for contacts and the companies that they are associated with. I am sure this will be obvious to some of you database veterans but I am fairly new to Access and I can't seem to figure out the best way to accomplish what I am trying to do.

Here is what I need to do:

When a contact's employment status changes, I need to change the contact's current company association but somehow maintain his or her association with the previous company so that s/he can still be associated with past projects.

So, in my contacts table (TBLContacts), I have a foreign key field "CompanyFK" that links to my companies table (TBLCompaniesPK). There is a one to many relationship between TBLCompanies and TBLContacts.

I want the CompanyFK field to be the current company but somehow link the person with past companies too so that the project directories and subforms will continue to show the contact's association with the parent company.

Maybe I don't need a history table but something else?

I have a similar problem with companies that change name, too. How to deal with takeovers, name changes, mergers, etc.

View 5 Replies View Related

General :: Copy Data From Local Access Table To Linked SQL Server Table?

Jan 13, 2014

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.

View 1 Replies View Related

Modules & VBA :: Copy Structure Of A Table To Make A Temp Table

Apr 20, 2015

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] .....

View 3 Replies View Related

Automate Copy Table And Sequence Table Name

Oct 27, 2004

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

View 3 Replies View Related

How To Copy All Record On A Table To Another Existing Table?

Feb 3, 2005

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.

View 3 Replies View Related

How To Copy A Column From One Table And Insert It Into Another Table In The Same Db

Feb 3, 2006

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

View 4 Replies View Related

Copy Linked Table Into A Local Table

Jul 30, 2006

Well, heres the situation. I have a complicated query that refuses to work all the time using a linked table for the data (data is gathered from a FoxPro DB). If I copy the data into a local table in my database then the query will run fine.

The data needs to be updated only 1/month but I don't want to have to do it manually every month. I would like to use VBA to copy and paste the data from the linked table into my local table. Does anyone know of an efficient way to do this? I'm trying to avoid running a VBA loop and adding each record one-by-one (very slow).

View 2 Replies View Related

Copy Table Post And Secondary Table

Jun 26, 2007

I have a problem... I have four tables(but my problem is limited to just two)
The tables are Orders, Customers, Items and OrderedItems
The two more detailed below creates the problem when trying to copy an already existing order to a new since the customers usally order the samethings over and over again.

Orders
IDOrders
IDCustomers
strSalesperson
strWhen
strSent

OrderedItems
IDOrderedItems
IDOrders
IDItems
iNumber
bPacked

So I have made a copy button on the order form. The copy button should do this:
1. First save the old orders IDOrders and IDCustomers (which it does)
2. Create a new order with old data (which doesnt do)
3. Copy all post in OrderedItems with a new IDOrdered using INSERT INTO and using columns

My SQL statement is like follows:
sSQL ="INSERT INTO Orders(IDCustomers, strSalesperson, strWhen, strSent, strComment) VALUES (" & itmpIDCustomers & ",'" & strSalesperson & "', '" & strWhen & "','" & strSent & "');"
But the reply I got is "Could not find output table 'Orders'" but I have checked the names over and over again but it doesnt work.

View 1 Replies View Related

Copy Table Data To New Table Without One Record

Oct 24, 2005

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

View 4 Replies View Related

Queries :: Select Active Record In A Table Using A Query

Nov 30, 2013

Based upon a specific date (varDate), I want to select the record that is active (who drives the lease car).

row 1 user X from 13/11/2013
row 2 user Y from 15/11/2013
row 3 user Z from 17/11/2013

I want to find the active user on a date. So on date 13/11/2013 (=varDate), user X is active, on 14/11/2013 (=varDate), user X is still active, on 15/11/2013 (=varDate), user Y is active, on 16/11/2013 (=varDate), user Y is still active and on 17/11/2013 and later, user Z is active.

View 1 Replies View Related

Tables :: Adding Term Date Or Active Marker To Employee Table

Apr 25, 2014

I have an active database that is used to evaluate employee call performance. When building the database (my first full attempt) I did not include a way to sort out employees who are no longer active.

I know I don't want to delete them.

1) Add a termination date to the employee tableHow do I set my form to ignore all employees with a term date? (I currently have a drop down box that allows us to choose the associate from the employee table by using the Row Source)...

What if the employee were to return to the position (has only happened about 5 times in 15 years) would sorting termed employees out of reports be as simple as adding in the criteria of the query something like "is Null" under the term date?

2) Add a radial button to the employee table to show "active"Same question about how to ignore employees that have been unclicked?

3) Add a Z-to the last name of the (employee name) this will drop them to the bottom of the list.What happens to the associated records under the "old" employee name?

View 8 Replies View Related

How To Copy Records To Different Table

Jun 22, 2006

Hi all

This gonna be a long one.... I've search the whole forum for answer to this problem, but couldn't find the one suitable enough.

I have a quote table with the following field:


QuoteName
QuoteDescription
QuotePrice

and booking Table:


BookingName
BookingDescription
BookingPrice

The real table is much more complex, but this will do for now.

When a client call up for a quote, their request will be added into the "QuoteTable". One name can occur many times depend on how many they ask for quote. Once they decided to proceed with the booking, all the records need to be transfered into "BookingTable".

Currently, I use either one of the following codes found in this forum:


Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset

Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")

Do Until rstQuote.EOF
rstBooking.AddNew
For Each Field In rstQuote.Fields
rstBooking.Fields(Field.Name).Value = _
Nz(rstQuote.Fields(Field.Name).Value, "")
Next Field
rstQuote.MoveNext
rstBooking.Update
Loop


or


Dim lngOuterCounter, lngInnerCounter As Long
Dim dbs As DAO.Database
Dim rstQuote, rstBooking As DAO.Recordset

Set dbs = CurrentDb
Set rstQuote = dbs.OpenRecordset("SELECT * FROM QuoteTable " & _
"WHERE QuoteName = ' " & Name & " ' ")
Set rstClient = dbs.OpenRecordset("BookingTable")

If Not rstQuote.EOF And Not rstQuote.BOF Then
rstBooking.AddNew
For lngInnerCounter = 0 To rstQuote.Fields.Count - 1
rstBooking.Fields(lngInnerCounter) = _
Nz(rstQuote.Fields(lngInnerCounter), "")
Next lngInnerCounter
rstBooking.Update
End If


But none seem to work. Can anyone help?


Cheers
arnodys

View 8 Replies View Related







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