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 Replies


ADVERTISEMENT

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

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

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

Help Creating A History...

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

Need Help Creating A Form Change History

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

Forms :: Creating A New Record From Another Form And Passing Values?

May 20, 2014

Have a form that contains fields AssociatedProject, AssociatedRelease and then the user hits a button to create a new record and a new form opens to display this newly created record which sets the fields and also sets a field called Type. There is no issue with setting the field values and the new record is created. However, when the form opens to display the record it displays another record instead (usually the one before it). I think it could be related to the fact that the db resides on a shared drive which periodically has slow connectivity. However, I'm looking for a solution, perhaps modifying the code?

Here's the code:

Private Sub Command17_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
On Error GoTo Err_Command17_Click
Set db = CurrentDb()
Set rs = db.OpenRecordset("Tbl_Main", dbOpenDynaset, dbAppendOnly)

[code]....

View 6 Replies View Related

Forms :: Creating Record From Subform - Use One Of Values In Main Form

Apr 5, 2013

I'm trying to create a record for a table that has 2 attributes:

ContainsTracks
+album
+tracks

I have my main form, where the album is determined by a TextBox. My subform has a textbox that lists the tracks (in datasheet view). I have this set up and working fine.At the moment if I try to add a new record by typing a new entry into the subform I get the error: "Index or primary key can not contain a null value". This is because both album and tracks make the composite primary key of ContainsTracks.

My question is, how can I tell the subform to grab the value from TextBox1 in the main form as the +album when I create a new record.I have provided two screenshot that perhaps explain my predicament a bit better

View 2 Replies View Related

General :: Creating Unique Ordered Number Field With Auto Shifting Values

May 31, 2013

I'm creating a database using existing data from an excel file full of contact details. What I need to add is a queue type system where each contact in the database has a "Place in Queue" number which is unique obviously.

Lets say Alan is number 1, Bob is 2 and Chris is 3. They have these corresponding numbers in the queue field for their entries.

Now what I need to have, through use of a form, is a way of changing Chris from number 3 in the queue to number 1 and thus have Alan automatically shift down to number 2 and Bob to 3.

View 4 Replies View Related

Forms :: Showing Transaction History In A Form With Foreign Key Values

Aug 8, 2015

I have 7 fields in a listbox (1st is the ID, hidden) from the single table. 3 of these fields are foreign keys. How do I get them to display their related values instead of the foreign key value?

Background:I'm purpose-building my db to essentially track individually cataloged items, somewhat like a library system would. I have four main tables: tblMediaItems, tblUsers, tblTransactions, and tblLocations. Users wills be spending 90% of their time on the Details form for the particular media item record they're viewing.

I'm trying to show an item's transaction history on the main form. I added a listbox (because I liked that compact presentation style) and got the desired fields to show up from tblTransactions. I figured out how to accomplish this for a single column combo box with the wizard, but so far I don't see how I would do this with multiple fields in a listbox.

View 2 Replies View Related

General :: Creating New Record After Update

Mar 13, 2014

I have a table called [Tasks] which has the following relevant fields:

[Task ID]
[Location]

I have a form that people use to create new tasks and update current ones. Location on the form is a combo box where people can pick from 5-6 different locations.

What I want:

After someone selects a new location from the combo box and changes the task location, I want to create a new record in a table called [Task Progress] with the [Task ID] of the task whose location was changed, the new [Location], and the [Date] and [Time] it was changed. It would look like

Code:
[Progress ID] [Task ID] [Location] [Date] [Time]
1 5 Station 1 1/1/2011 12:13:01
2 8 Station 2 1/3/2011 01:53:29
3 5 Station 2 1/5/2011 11:13:05
4 5 Station 3 1/6/2011 12:35:22

What should I put in the AfterUpdate event for the [Location] combobox to make the above happen?

Note: All my tables are ODBC linked to SQL Server (can't use data macros).

View 1 Replies View Related

Creating A Form To Append A Table With Values From Other Tables

Oct 12, 2006

Hi,

I have built an Access DB containing 3 tables: dimensions, time, companies. The tables are not linked and are to be used to look up values for the new form. The goal is to create an Access form that would allow the user to select distinct values from all 3 tables, enter some own data and then execute an append query to add the record to the main table.

Something like this:

Initially I have 3 tables:

Prepopulated Dimensions table with fields:
dimension
....

Prepopulated Time table with fields:
Date
Day
Month
Year

Prepopulated Companies table with fields:
Company
....


My form is to be able to select distinct values (combobox) from all three fields:
Company
Dimension
Day
Month
Year
Value (data entered by user)

The record then is appended to the Main table containing:
Company
Dimension
Day
Month
Year
Value (data entered by user)

Thanks a lot for your time and help!
Polar

View 3 Replies View Related

General :: Creating Individual Calendar For Each Record

Jul 16, 2014

Not sure if it's possible but I'm trying to create individual calendar's for each staff member and client to put availability and bookings on to them. I've tried using the active x control but it's not really what I want, not sure how else to approach this as my access skills aren't that great.

View 3 Replies View Related

Queries :: Counting Values And Creating A Table For Each Defined Count

Apr 14, 2014

I am using an existing database which allows my company to track claims information. One report my supervisor has asked for is a table which shows the counts of each kind of event occurring at a district level by department. For example, the classifications are injury classes like FSA, so I need to design a report which says facility a has 3 fsa's in the Wireline department in a table format. I have been looking into union queries as suggested by other sites but nothing seems to work. My labels are District, Analysis Code and Department.

View 3 Replies View Related

How Do I Keep History Log Of Record Before It Is Overwritten?

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

General :: Searching History Of Fields In Database

Jun 13, 2013

I have a database and would like to search history of some fields but they are autonumber and it won't let me search them.

The fields in question are on the form Workorder:

WorkorderID
CustomerID

View 10 Replies View Related

General :: Keeping History Of Notes / Comments

Mar 26, 2014

How to extract the comment system from the Contacts Web Database template found in Access 2010? I can upload it if you like.

I would like to use it in a standard desktop database to track notes and comments in my records.

I am able to convert it from web to standard, but it still appears to use data macros or something, and my knowledge on macros is limited.

I just want to keep a history of my notes for each record, with the most recent always listed, and the comment system in the Contacts Web Database template seems perfect for that. I just don't know how to implement it into my desktop database.

View 1 Replies View Related

Modules & VBA :: How To Record Username In History Comments

Nov 26, 2013

I have a field that records comments call house comments i wonder is it possible to record which user leaves what comment?

Code:
=ColumnHistory([RecordSource],"Comments","[ID]=" & Nz([txtID],0))

View 1 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

Creating New Record From New Data In A Different Table

Oct 12, 2012

I have an access database that I use to manage my car dealership, part of it holds data of a stock vehicles, another aspect is invoicing.As part of the invoicing form, I have a part exchange vehicle section which creates a new record in the part exchange table.

What I would like to be able to do is when a new vehicle is added to the part exchange vehicle table it automatically adds it to the main vehicle table so it shows as a stock vehicle.

View 1 Replies View Related

General :: Comment History Field To Appear On A Form - Change Sort Order Of Results

Aug 28, 2013

I have after much stress gotten my comments history field to appear on a form.

The following code gives me what I needed

Code:
=ColumnHistory([RecordSource],"LastUpdateBy","[ID]=" & Nz([ID],0))

I have only one simple thing left, that is perplexing to say the least.

Some comment fields will be many short notes, spread over time.

My desire is to change the sort order of the results so the most recent (rather than the initial) comment is at the top.

View 5 Replies View Related

General :: Sum Values From Date Range From Specific Record

Mar 11, 2013

I'm working on a loan database for my personal use and obviously I'm new to access

See attachment ...

View 5 Replies View Related

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 14 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

Tables :: Series Of Checklists - Creating New Record In Each Table

Sep 6, 2014

My application is a series of checklists, represented each by a table. They all have a common "Job ID" which is entered by the user in the first form/table. This "Job ID" is a common value with all other tables.

What I want, is that the user doesn't have to enter the "Job ID" every in every form. I just want him to enter it once in the first menu, and then it's copied automatically to all the other tables (creating a new record in each table).

View 2 Replies View Related







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