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 Replies


ADVERTISEMENT

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

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

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

Modules & VBA :: How To Get Autonumber ID Before Update / Save Record

Mar 27, 2015

I wanted to get the autonumber ID before a record was saved to the table. My fields are on a form that is linked to the table. Maybe my solution is not the most elegant but it seems to work.

I messed around and came up with this solution: it creates the next record and captures the autoID then increments it and creates the record we will actually use. Since we know the current autoID we know 100% the next will be the current+1

Code:

' Code by Witchcraftz
' Button event to add new record
Private Sub cmdAddRecord_Click()
Dim strID As String

[code]...

View 2 Replies View Related

Forms :: Save User Name That Update Record For Multiple Yes / No Box

Sep 6, 2014

I am creating a form that has a bunch of yes/no box for daily task. Is there a way for access to record which user selects yes/no.

For example

yes/no box1 - user 1 completed this task
yes/no box2 - user 2 completed this task
and so on

So when i look back i can see which user complete which task. I did a bit of research and saw that i can save a user that update a record. But since this is a daily task, each new record will be a new day.

View 3 Replies View Related

Tables :: Update And Save To Another Table

Dec 26, 2012

Warehouse inventory system in access 2003. I have set up two primary tbls: Equipment and ETO (Equipment transfer order) both have an autofill primary key and the equipmentID is part of the ETO tble. There is a one-to many realationship from the equipment to the ETO. There are other tables (4) in the system but they are used as look-up tables and i'm not concerned about those.

Equipment Table layout: EquipmentID, ModelNumber, SerialNumber, Mfg, ProductName, CurrentLocation, Category. This table is used as the repository for all of the equipment that needs to be tracked and inventoried. It will not change much with except the CurrentLocation changing as the stuff is shipped around (in Store, In Warehouse) and of course when new equipment is being added.

ETO table layout: ETOID, ETONumber, FromStore, ToStore, OriginStore, ETODate, ModelNumber, SerialNumber, MfgID, ProductName, LocationID, CategoryID, EquipmentID. This table will be used (I hope) to basicly track the equipment as it moves from store to store to warehouse and back out again.

Process: Locate a peice of equipment by either SerialNumber or ModelNumber in the Equipment Table, change the location depending on it's status (coming in, going out) then i need to somehow update the ETO table with that ModelNumber, SerialNumber, Mfg, category, etc..But put in the FromStore, Tostore, OriginStore, ETODate, etc..

I have tried both the update query and append query from both tables but I can't get the results i need.

View 14 Replies View Related

General :: Update Record ID To Another Record ID In Same Table And Update Related Records

Aug 22, 2013

I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.

In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.

The problem is when both companies already have existing records in the table.

So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?

I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?

View 6 Replies View Related

Need To Retain History And Update Records Based On Condtion

Nov 26, 2007

I have this scenerio that I am trying to work through and find a proper solution:

Main table stores a part # and an index (Primary Key) along with a bunch of other data. I want to store with the part # the current active version of firmware (aka software) associated with that particular part# / index. Part # may be a duplicate, so for this reason the PK is the index, so that each index / part # will have which firmware has been used with it.

I would like the ability to retain a history of firmware versions used for a particular part # / index so that if we need to go back to a previous version of software we will know which ones have been used.

I have been able to create the relationship to show all previous versions of the firmware used for a particular part # / index, but cannot figure out how to only have one 'current' and update the others as 'archived'. I would like to either use a boolean or selectable lookup (ie Current vs. Archived). What I want is that when I select one firmware to be ‘current’ all others related to the particular part # / index are updated to ‘archived’ leaving the other records not related to that part # / index alone. Also note that I am trying to make the update of the firmware status in the table view without having to go through a form.

Main Table
ID (PK)
Part No
Product

Firmware History
FirmwareVerIndex (PK)
PartNo
Firmware
ChangeDate
ChangeName
FirmwareStatus

Firmware Table (lookup table)
FirmwareIndex (PK)
Firmware
FirmwareFileName

FirmwareStatus (lookup table)
ID (PK)
FirmwareStatus

View 1 Replies View Related

Save Record Only If Same Exists In Another Table

Jan 7, 2013

table A has 10 records where 1 field is set as REQUIRED/UNIQUE. the table B does not have any record but have a same field like table A. what I want is that if user insert the record in table B it should first check whether record is already present in table A or not, if already exists it should not accept insertion. and if not already there in table A it should insert record in table B.

View 3 Replies View Related

Forms :: Log History Of Inputs - Using ComboBox To Update Memo Field

Jun 25, 2013

I am currently using the column history to log the history of inputs into a memo field.

But i now need to swap how my database runs and now require a combo box to have the same history function, as this is for a status updates and i require users to only input certain status's.

I no that I cant use the columnhistory command with a combo box.

When selecting a status from the combo box it automatically update the memo field (which will be hidden on the form) so the column history function works.

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

Forms :: Save Preliminary Version Of A Specific Record Of Table

Oct 4, 2013

How to let the user save a preliminary version of a record in a form? I have a table with records representing airplane status. Sometimes, the user wants to save a preliminary version of the status and create some other versions and save all of them for that specific airplane, but this does not happen for all the airplanes. How would I do that? Is it possible at all?

View 1 Replies View Related

Modules & VBA :: Recordset Adds New Record Into Subform But On Save Nothing Added To Table

Nov 5, 2014

I am adding new record into subform via recordsetclone method. The problem is that record is added but on save it does not appear in the table. If add this record manual using subform everything works. When record added manually update of the record works fine.

C
'Add Wastage value to flooring area section
Private Sub Wastage_AfterUpdate()
Dim rsFlArea As DAO.Recordset
Dim Wastage As Double
Dim Item As String
Set rsFlArea = Me.OrderFloorAreaEdit.Form.RecordsetClone

[Code] .....

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

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

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

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

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

Forms :: Save Form Record On Move To Next Field In Record?

Jan 15, 2014

After I enter data into one field in a record I would like the form to save the record when I move to another field in the same record. It seems that the record is only saved when I exit it entirely. Is there a way to save a record when moving between fields in that record? Can this be done without using an Event Procedure for each field?

View 8 Replies View Related

General :: Save Record On Data Entry And Generate ID For That Record

Feb 1, 2014

I am working on a database and i have notice after making a form that when i enter a record using a form if i enter incomplete data on the form access automatically save that record and generate a id for that record.

For example I have a table that contain

StudentID,StdName,FatherName,DOB,Adress,Phone

And I have created a form for that table that also containing these fields.

Here I want to do that on the form I want a "Save" button , and the purpose of this button that when I click on this button then MS Access Save the record and then generate the ID for that record and if i close my form without pressing "SAVE" button access do not save that incomplete record.

View 10 Replies View Related

Noob - Update Record In Table

Sep 13, 2005

Hi all,

First of all....my first access database and I'm still a VB newbie...so my apologies if this is a stupid question. I tried searching the web and this forum but could'nt find what I was looking for.

Basically, I am developing an authentication script for a training tutorial. The user either logs in with there existing info, or prior creates a new record. When they login they also select a team (eg Team 1, 2, 3, 4, admin and so on). This is selected from a combo box - the teams are on their own table.

I have another table storing the user info - name, password, the users team...and so on. When they log in and the team selected does not match the team in 'tblusers' - I want access to change the record to reflect the new team.

Can anyone help me with the code to make this happen? So far I have -

If Me.defineteam.Value <> DLookup("Team", "tblusers") Then
MsgBox "You have changed team. This will now be updated", vbOKOnly, "Required Data"
GoTo verifypassword:
End If

This authenticates the team....I just don't know how to replace the one in the record with the value in the combobox.

Thanks for your help.....I'm losing hair!

cheers

Sd

View 7 Replies View Related

Update Two Records In One Table From One Record In Another

Oct 14, 2004

Hi,

I am creating a league table for my soccer team.

I have a database with these tables:

1. teams
2. league_table
3. fixtures

The Fixtures Table has a lookup where it pulls in the opposing teams - home_team & away_team. There is also Home_goals & away_goals. How can I get the fixtures table to update each teams goals in the league_table table where these details are held.

Anyone any ideas on how this is done?

Thanks in advance.

View 1 Replies View Related







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