Append records to table but use field name as an entry

[tagteam]

I have a list of tests that i record the date they were done. When i update it the due date becomes 10 years from the last date tested. I would like to move the old dates to an historical table but the problem is the dates are all in separate fields in the main database but I want to just archive them as a name/date combo in the history table.

For example these would be fields in the main database
Car Number 1 SafetyValveTest Date Done: 1/1/2000 Date Due: 1/1/2010
Car Number 1 BottomValveTest Date Done: 1/1/2000 Date Due: 1/1/2010

When i save it in the history table I would like it to look like this (except car numbers would be whatever car it was)
Car TestName Test Date


1 SafetyValve 1/1/2000
7 BottomValve 1/1/2000
3 SafetyValve 1/1/2010
1 BottomValve 1/10/2000

So basically I am looking for a way to use and append query but use the field name and put it in as a field value in the table I wish to update. I am thinking of using a check box and for each box they check it will update the history table and then i can assign whatever name value I want to the check boxes.

[June7]

"Dates in separate fields" sounds like a non-normalized data structure. If data structure was normalized, this 'archiving' process should not be necessary.

The DateDue should not have to be saved to table. The value can be calculated.

[tagteam]

Yes, the database is not normalized.

1. Dates are entered in field because they are used for searching for data in filter by form so I dont think they can be calculated. Can you use filter by form on calculated fields?
2. Dates are not always 10 years. If a group of cars is going in for repair the date may be manually udpated

I have a check box beside each field and here is what I would like to do:
step 1
If box is checked append static value (check box name) to field TestName in history table and use the value in testdone field to be date value in history table

step 2
Take value in unbound field and update current value in field testdone if that check box has been selected ( will probably have to create variables for this and do in vba)

step 3
Take value in test done and add 10 years and then go to 12/31/xx of that year and enter that value in the date due field

[June7]

1. If the form RecordSource is a query that has that calculated field, then probably should work - I've never tested with 'filter by form', have you?

2. okay, save date

Those steps can all be done with code. I use only VBA.

VBA can run an INSERT sql action.
CurrentDb.Execute "INSERT INTO History(TestName, TestDate) VALUES('" & Me.tbxTestName & "', #" & Me.tbxTestDate & "#)"

Change values in fields.
Me!TestDate = Date()

Me!DateDue = DateAdd("y",10,Me.tbxTestDate)

The really trick is figuring out what event to put the code in and making sure not accidentally repeated.

[tagteam]

I plan to put it in the on click event. I will have a button and then it will run the code when clicked. I will start building it today and check back in a few days with any questions. Thanks

[tagteam]

Ok, here is what I have done and it seems to be working for me. I decided to make it easier and not let the user click a check box but just custom code 5 buttons.

CurrentDb.Execute "INSERT INTO tbl_TestHistory(RailcarID, Test, DateCompleted) VALUES('" & Me.RailcarID & "', 1, #" & Me.TankQualificationDone & "#)"
Me!TankQualificationDone = Me.ActualTestDate
Me!TankQualificationDue = DateAdd("y", 10, Me.ActualTestDate)

However, I am not sure about the last part. Is it just adding 10 to the months. What I need it to do is go to 12/31 of any year that I put. so if the last test date was 6/7/2014 the test due field would be updated to 12/31/2024

[June7]

Sorry, needs to be "yyyy".

Might want to study and get more familiar with data manipulation functions.

[tagteam]

I see but I think that just adds one year to it.

What I need it to do is take the current value and go to the end of that year. Would it be easier to just use VB to pull out the year and then hard code 12/31 and add back the year? Maybe using something like left to get the last for digits and then just build the date from that.

[tagteam]

I am thinking

Dim Year as string
Year = Right (Me.ActualTestDate, 4)

Then later I use Me.TankQualificationDue = "12/31/Year"

[ssanfu]

Look at the DateSerial() function.....

[June7]

DateAdd("yyyy", 10, Me.ActualTestDate)

will add 10 years to the given date.

[tagteam]

I dont want to add 10 years, I want to add 10 years and then go to the end of that year

I am trying this but it isnt working
Dim TestYear as sting
TestYear = Right(Me.ActualTestDate, 4)

Me.TankQualificationDue = "12/31/" & "TestYear"

However I keep getting an errror. I am sure I have the concatenation wrong but now sure how to do it

[tagteam]

Ok, for those interested I got it working with this. Thanks for the help

Private Sub UpdateTankQualification_Click()
Dim TestYear As String
Dim NextTestDate As String
NextTestDate = DateAdd("yyyy", 10, ActualTestDate)
TestYear = Right(NextTestDate, 4)

CurrentDb.Execute "INSERT INTO tbl_TestHistory(RailcarID, Test, DateCompleted) VALUES('" & Me.RailcarID & "', 1, #" & Me.TankQualificationDone & "#)"
Me.TankQualificationDone = Me.ActualTestDate
Me.TankQualificationDue = "12/31/" & TestYear
Me.Refresh
End Sub