AfterUpdate Move Value to a Table (1 Viewer)

[KatieinLA]

I have a form with a combox control that is bound to a table. It is a status field. When the status changes, I would like to take the OldValue of both the status combobox and the status date and move them to a table that when accessed will show a history of the prior status and corresponding dates.

I have been searching for over a week - I cannot sipher throught the auditlog code by Mr. Brown and I am really frustrated. Would apprecate any offer of help. I am using Access 2010. Not sure when to use an Event Procedure, macro, module or code.

 

[KatieinLA]

Hopeful that someone might shed some light and help out soon...?

 

[CJ_London]

What do you mean by status date? is that 'today'? i.e. when the status changed? So what you are storing is the status and the last day it was 'relevant'

 

[KatieinLA]

Status date defaults to today(). This field is in the tblappraisalorders table. This table is bound to the form. the currentstatus combo box on the form has a set list from the statuslist table. Once selected, the status is saved in the tblappraisalorder.current status and the current date is saved to tblappraisalorders.currentstatusdate. The date stored should be the date that the order went into that status. when the status changes, it should be the new date of the new status.

i have set up a history table that can accept the old status value and the old status date value.

i have looked up Allen Browne's audit table but i do not know how to apply it
or even if it is applicable.

 

[CJ_London]

in your form before update event put the following code:

if currentstatus.oldvalue<>currentstatus then
    currentdb.execute("INSERT INTO tblHistory (Status,DateChange) Values(" & currentstatus.oldvalue & ", " & currentstatusdate & ")")
end if

You will need to change names to suit.

 

[KatieinLA]

I got a runtime 424 error. I will post again in the morning. Thank you for your help on this.

 

[KatieinLA]

I have had some success thanks to your help and another one of your posts!

I used the following:

Private Sub Current_StatusCombo_BeforeUpdate(Cancel As Integer)
If CurrentStatusCombo <> CurrentStatus Then
CurrentDb.Execute "INSERT INTO[tblStatusHistory]([oldstatus],[oldstatusdate])" & "values ('" & Me.CurrentStatus & "','" & Me.CurrentStatus_Date & "');"
End If
End Sub

It captures the existing data in these two fields perfectly. Where I am a little confused is in the tblStatusHistory there is a field called AppraisalOrderID which is has a one-to-many relationship with the tblAppraisalOrders.AppraisalID (the tblAppraisalOrders being the one and the tblStatusHistory.AppraisalOrderID being the many. I have the the aforementioned relationship set to cascade Updates/Deletes. The issue is that the AppraisalOrderID in the tblStatusHistory field is blank.

I will need to be able to retrieve these statuses for each appraisal order. If there is no ID populating how will it be able to retrieve based on this number that is to correspond with the main order?

 

[KatieinLA]

I also have another question. I just noticed that it is capturing the changed value rather than the prior value. I see that there is something called .OldValue. I am working in Access 2010 - can I apply .OldValue in the code above?

 

[CJ_London]

I see that there is something called .OldValue

- yes that is in my post

....Values(" & currentstatus.oldvalue .....

The issue is that the AppraisalOrderID in the tblStatusHistory field is blank.

You have never mentioned this field before
try modifying you code to the following

if currentstatus.oldvalue<>currentstatus then
    currentdb.execute("INSERT INTO tblHistory (AppraisalOrderID, Status, DateChange) Values(" & AppraisalOrderID & ", " & currentstatus.oldvalue & ", " & currentstatusdate & ")")
end if

 

[KatieinLA]

Private Sub Current_StatusCombo_BeforeUpdate(Cancel As Integer)
If CurrentStatusCombo <> CurrentStatus Then
CurrentDb.Execute "INSERT INTO[tblStatusHistory]([AppraisalorderId],[oldstatus],[oldstatusdate])" & "values ('" & Me.AppraisalID & "','" & Me.CurrentStatus.OldValue & "','" & Me.CurrentStatus_Date & "');"
End If
End Sub



I made the change but received a 'Compile Error: Methos or data member not found'.

AppraisalId is an autonumber and a primary key in tblAppraisalOrder - Is that why I am receiving an error?

 

[CJ_London]

Not sure if it is the way it has posted but you are missing spaces and have extraneous quotation marks and semi colons - compare your code

CurrentDb.[COLOR=red]Execute "[/COLOR]INSERT INT[COLOR=red]O[[/COLOR]tblStatusHistory[COLOR=red]]([/COLOR][AppraisalorderId],[oldstatus],[oldstatusdate][COLOR=red])" & "[/COLOR][COLOR=red]v[/COLOR]alues ([COLOR=lime]'[/COLOR]" & Me.AppraisalID & "[COLOR=lime]'[/COLOR],[COLOR=lime]'[/COLOR]" & Me.CurrentStatus.OldValue & [COLOR=red]"[COLOR=lime]'[/COLOR],[COLOR=lime]'[/COLOR]"[/COLOR] & Me.CurrentStatus_Date & [COLOR=red]"[COLOR=lime]'[/COLOR]);"[/COLOR]

with what I posted

currentdb.[COLOR=red]execute("[/COLOR]INSERT INT[COLOR=red]O t[/COLOR]blHistor[COLOR=red]y ([/COLOR][COLOR=black]A[/COLOR]ppraisalOrderID, Status, DateChange[COLOR=red]) V[/COLOR]alues(" & AppraisalOrderID & ", " & currentstatus.oldvalue & [COLOR=red]", "[/COLOR] & currentstatusdate &[COLOR=red] ")")[/COLOR]

Once you have corrected for these, you need to look at your use of the single quotation marks - highlighted in your code in green. They are only required if the field in question is text - if not, leave them out - certianly you don not need them around AppraisalID if it is an autonumber.

 

[KatieinLA]

I resolved the AppriasalID issue: I read somewhere that primary/autonumber fields could not be copied from one table to another - I created a calculated field in the table called StatusID which copies the AppraisalId in the main table. The tblStatusHistory.appraisalorderID are linked. So...I think that issue is resolved.


Still having trouble with the .OldValue


I need to capture the old value before the new value is saved - I receive compile errors for anything with a .oldvalue

 

[KatieinLA]

I went from kind of working to not working at all :banghead:

 

[KatieinLA]

I was able to put it back together somewhat - it isn't doing what I need it to do. It is saving the new current status rather than the OldValue. Old value is not an available option after I enter me.currentstatus. ... after I enter the period in the builder it gives a little drop down - only Value is available.

Now there are other problems - using the record arrows to go from record to record - it doesn't move sits on the same record then jumps to another record - I think that may be happening because it is only showing those records that have status history entries. Need to see them all.

I see that sometimes others have uploaded dbs. I am to the point of just having a table with all of the possible statuses and a date field - I can just have a screen that has all the statuses listed and the corresponding date - this is the lazy way out and I would rather not but I just cannot seem to get the hang of it.

I tried to upload the db but got an error regarding a security token?? got the same error trying to email the admin.

 

[CJ_London]

it might be an idea to post your db with the problem form, code and tables. We seem to have drifted off from where you want to be. I suspect you have naming issues.

Have a look at the attached - this is how you should be getting to oldvalue

 

[KatieinLA]

You are a prince - I have tried however to upload twice and got a security token error - tried to email the admin - got a security token error

 

[CJ_London]

I'm about to hit the sack, so will respond tomorrow but try zipping the file. If that doesn't work, I can message you with my personal email

 

[KatieinLA]

I did a search for the error - cleared my cache. I tried again - got the same error. If you PM me your email - I will send it over zipped.

 

[CJ_London]

I’ve had a look and the basic answer is simple – you need to put the code into the form before update event rather than the combo before update event. Also the oldvalue relates to the control so you need to use the control name rather than the field name and there are some other things as well – this is what you should have

[FONT=Calibri][COLOR=#1f497d][COLOR=#1f497d][FONT=Calibri]Private Sub Form_BeforeUpdate(Cancel As Integer)[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]If Me.Current_StatusCombo.OldValue <> Me.Current_StatusCombo Then[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]   CurrentDb.Execute "INSERT INTO [tblStatusHistory] ([AppraisalOrderId],[oldstatus],[oldstatusdate])" & "values (" & Me.AppraisalID & ",'" & Me.Current_StatusCombo.OldValue & "','" & Me.CurrentStatus_Date.OldValue & "')"[/FONT][/COLOR]
 
[COLOR=#1f497d][FONT=Calibri]End If[/FONT][/COLOR]
[COLOR=#1f497d][FONT=Calibri]End Sub[/FONT][/COLOR]
[/COLOR][/FONT]

However just need to check to ensure this is right:

· StatusID and appraisalID seem to be getting mixed up – why do you have the StatusID field? you are not actually calculating anything (and using calculated fields is a bad idea!)

Whilst on bad ideas – it is also a bad idea to use spaces in names – makes life much more complicated: you’ll note when autocompleting (e.g. start to type me. and a list of options are presented) that vba replaces the space with an underscore

 

[KatieinLA]

Your recommendations worked like a charm - I will be finished with this by the week end thanks to you. CJ - you are terrific at what you do. I could not have done this without you - I learned quite a bit from your help. You are absolutely an asset to the forum. Thank you again for your help.