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 Replies
ADVERTISEMENT
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
Apr 27, 2007
Hi,
I am trying to create a historical record of Employees at our business. What I would like to do is have a query that will create a begin date and create that date for each day starting w/ like 1/1/06 and continue thru Date()-1. For example:
Jane Doe and John Doe started employment on 1/1/06. Jane Doe was employed until 1/5/06. So using their "Hire Date" I would like the query to do this..
Created Name Hire Date Termination
1/1/06 Jane Doe 1/1/06 1/5/06
1/1/06 John Doe 1/1/06
1/2/06 Jane Doe 1/1/06 1/5/06
1/2/06 John Doe 1/1/06
1/3/06 Jane Doe 1/1/06 1/5/06
1/3/06 John Doe 1/1/06
1/4/06 Jane Doe 1/1/06 1/5/06
1/4/06 John Doe 1/1/06
1/5/06 Jane Doe 1/1/06 1/5/06
1/5/06 John Doe 1/1/06
1/6/06 John Doe 1/1/06
1/7/06 John Doe 1/1/06
So in this case Jane Doe will show up every day in the "Employee List" until she was terminated. Is it possible to loop a date like this in a query or will I have to make an estranged table with a list of dates? TIA for any help.
View 2 Replies
View Related
Aug 23, 2004
I would like all the changes made to fields on my form to be automatically entered into a history list on the form. I can't seem to get the right code for this. I would like it to have the date and time the change was made also. Can someone help me. I have Access 2003 and Win XP. My form is unbounded.
Thanks,
Lisa
View 4 Replies
View Related
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
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
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 14 Replies
View Related
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
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
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
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
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
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
Sep 14, 2005
How do I create a table that can cross index items in another table. Maybe I am not using the right terms here so let me show a small example.
Say I have a tables of words.
tblWords
numWordID
txtWord
Then I have some entries, all more or less synonyms of each others
fresh
new
clean
Now I want to create a cross-index table, related to the table "tblWords" where I can select synonyms from words already in the table "tblWords", so if I for the word "fresh" add "new" and "clean" as synonyms or entries, if I then go and look at the word "new" it will already have the synonyms "fresh" and "clean", likewise the entry "clean" will then have the synonyms "fresh" and "new".
Kind of a many to many relationship junction table but only with one table!
I hope my explanation have not been to confusing, but let me know if you need a clarification.
Thanks
View 6 Replies
View Related
Nov 1, 2005
Hi,
I currently have a "customer" form (which displays client details from a table - name, address etc) I would like to add history comments for each customer.
Does anyone know how i would go about doing this.
I image i could press a button on the current "customer" form that will open a history form which after typing a comment would be displayed on the main "customer" form, with date, time and comment.
I would obviously be able to add lots of comments to each record.
i would be greatful for any kind of help
Regards
Chris
View 4 Replies
View Related
Mar 19, 2006
I need to write a db for my boss where he can access his patient treatment information. How can I make the db (Access '03) to be able to add new treatment to the same patient and to display all treatments seperately on a form? There are about 500 patients. Appreciate any assistance.
View 1 Replies
View Related
Jul 13, 2006
Hi. I am creating a contact management database. Also I am new to Microsoft Access. How would I create a calling history record for each of the clients in the contact database? What would be the best way and how would I go about it? The calling history ideally would include the call recipient, time, date, subject and notes. Thanks. Take care.
View 1 Replies
View Related
Apr 27, 2008
Hi
I have 2 fields - "Balance" and "Date" - each time data in the "balance" field changes, the "Date" field updates to todays date (thanks to help from Kiwiman in the forum earlier).
I now need to keep a history/log of all data input into balances and the dates.
The new field to store this information is called "Balance History". so an example of what i want to end up with would be
01/01/08 300.00
01/02/08 250.00
12/02/08 210.00
Any help would be greatly appreciated
Thanks
Geoff
View 1 Replies
View Related
Jan 9, 2006
Hi,
In MS Access, form
I want make like this:
1st time:
Company Name: Automobile
2nd time:
Company Name: AuID
If I type A, then it will show out the word, but the cursor is till After A, because if we type another character which will change another word
3rd time:
Company Name: Automobile
4th time:
Company Name: Au[I]ID[I]
What is the keyword to search about it in the forum?
Anyway, does anybody know how to solve it?
Please let me know, thanks.
View 1 Replies
View Related
Sep 11, 2005
ok i didnt know what section to put this in so i put it here. what i wanna do is add a price change history to my database. for example is the price of an item is changed in my database i want to be able to see what the previous price was and when it was last changed, either in forms of reports doesnt matter i just need somewhere to view this info. problem is i have no idea where to start.
any help provided is muchly appreciated thanks.
View 4 Replies
View Related
Mar 24, 2006
Hi Guys
I am pretty new to programming with access but i am trying to create a database system with history logs and i am stuck on how to structure it.
I'll explain a little more, i am trying to setup an application so that if somebody brings an item into myself to purchase i can book it into the system so that it records the customer that it came in with and also the item details. I will then need to resell this product onto another customer, the problem i am having is that once the item has been purchased and sold on i will then need to keep a record of this so that if the customer comes back with the same item he has purchased to resell back to us we can keep all the details in history so that each product that is purchased and re-sold has all its past history from every customer that has purchased it and sold it back to me.
Any help would be much appreciated.
Thanks
Brad
View 1 Replies
View Related
Jul 21, 2006
Is there a way to create or access a history file of all events in a database?
View 1 Replies
View Related
Dec 8, 2007
I am creating a database for a company that sells a product with a variety of options.
They have all their previous orders in a works spreadsheet file. Each customer has their own file with every order for the past 15 years. There are probably about 1.5 million records.
The company wants all those 1.5 million records accessible in their access database.
I've brought in about 20 records for the history and they can run a query to see a customer's past orders by their account number.
My question: Should I put all the history into one gigantic table or would it be best to try and create separate tables for the history? (Maybe history by state.)
The history table then will take the new orders each year and add to that table.
(Eventually, once the database gets done I'm thinking we'll have to step up to SQL but not sure.)
Thanks for any advice.
Melanie
View 7 Replies
View Related
Jan 19, 2008
Hi all, I need some help figuring this problem out. I’ll keep it simple. I have a data entry form with 3 text boxes where you can enter product name, serial number and comments. A save button writes the entry to a table called “Products”. That’s all fine.
I also have a data edit form and this is what I need some help with. The product name will always remain the same but the serial number and comments may change. Should a change be made, I want the existing record being modified to go into a table called History before the Products table is updated with the new serial number and comments. The idea is to keep a full traceability of all the changes made when a report is printed off. What’s the easiest way to this please? thanks
View 1 Replies
View Related
Feb 19, 2008
Is there any way to leave a trail or history everytime a record is changed? We have kids in our database that sometimes go to follow-along when our service will not work for them at the present time. These kids go in and out sometimes several times. Since there is no absolute way of knowing, we have to create a new record everytime they come back to our service. I would like to be able to see a history of status changes along with some dates. Is there anyway to do this? If anyone knows, let me know. If not, oh well. Thanks guys.
View 6 Replies
View Related
Feb 23, 2012
I have a table for my customers with current information.
CustomerID:0001
CustomerName: xxxxx
Address:xxxxxx
Telephone :xxxxxx
etc.....
But some time customers change their information. I want to keep their old information in other table. How it is possible?
View 4 Replies
View Related
