Results 1 to 2 of 2
Thread: History Table (A2k)
2008-11-09, 20:43 #1
- Join Date
- May 2002
- Patagonia Region Chile
- Thanked 0 Times in 0 Posts
History Table (A2k)
Edited by HansV to provide link to post - see <!help=19>Help 19<!/help>
I have a table whose autonum field becomes the control number for a piece of furniture we create.
WE print a ROUTING document on paper with the CONTROL number (autonum field) so the whole shop depends on that number staying the same for that piece of furniture...
That table includes a field called ITEM which ties the record to an order which ties to a customer.
Occasionly we have to change that ITEM number because the piece of furniture ends up going to a new customer or being returned etc.
I would like to be able to change that field (and other fields) in the record, but just before the change, append the pre-changed record to a history table.
I searched the forums before asking this question and found this post <post#=428970>post 428970</post#>
I did not understand it all and I may be needing something different than what was discussed.
Still, I am almost embarrased to ask about a history table solution after reading it.. But here goes. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
Because the autonum field provides a control number that is sequential and used constantly....keeping the history in the same table would be VERY confusing for the users.
Is there a clean/smart solution such as a way to append the records to a history table with a before update event..?
2008-11-09, 20:59 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 Posts
Re: History Table (A2k)
1) Create the history table:
Select the table in the Database window and copy it (Ctrl+C).
Then paste it (Ctrl+V) and select the option to copy the structure only, not the records.
Name the copy tblHistory (for example).
2) Modify the history table:
Open tblHistory in design view and change the type of the control number field from AutoNum to Number.
If the item number field can be changed more than once, make sure that the control number field is not the primary key of tblHistory, for over time, there could be multiple records with the same control number.
Close and save tblHistory.
3) Write code to fill the history table:
Open the form used for data entry in design view (you *must* use a form for this; users shouldn't edit data directly in the table or in a query based on the table).
Activate the Event tab of the Properties window.
Click in the Before Update event and select [Event Procedure] from the dropdown list.
Click the ... to the right of the dropdown arrow, this will create the event procedure.
Make it look like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strSQL As String
strSQL = "INSERT INTO tblHistory SELECT * FROM [NameOfTable] WHERE [ControlNumber]=" & Me.[ControlNumber]
DoCmd.SetWarnings False ' optional
where NameOfTable is the name of your original table and ControlNumber is the name of the control number field.