I see my link above didn't work. How about...

[b][URL=https://599cd.com/blog/display-article.asp?ID=1339]Tracking Changes[/url][/b]

I give up. If someone could show me how to post URL's (If i can), i'd appreciate that, cheers

Dan,

You can't put links in the initial post, only in the replies beneath.

Track Changes

The error message is saying there was no value sent to the expression.
Also, why are you running the same SQL statement in both the Form_AfterUpdate and the Form_BeforeUpdate events?

So it isn't picking up the "WIPID", which is odd because it was working and i haven't made any changes....

I'm running on both before and after so i get the old data and replaced data - in case its changed further, easier to analyse (I know it'll double up the data, that's why i've put the table in a file of its own

Just checking  :P

<a href="https://www.599cd.com">Visit 599CD.com</a>

Dan -
Just so you know - the addition of links is a Moderator function.  It's that way to prevent people from posting to nefarious sites and to prevent virus infections.  You can post the url but it won't show up as a link.

I see. Thanks

Could the WIPID control have been renamed on the Form?

The control on the form is call Text_EntryID. Control Source is WIPID which is the field in the Sales Table. I haven't changed either of these recently.

Your picture mentions WIPID= but the code you supplied doesn't have that.

Which Sub expects WIPID?

Hi Alex, sorry that's my bad. I (Badly) attempted to simplify by replacing the field names in the code. There is no "SALEID", its "WIPID".

Theres 2 Subs doing the exact same thing - one for before update and one for after update. This way i get two entries in the log showing the old data and new data.

Actual Code without Translation:

=====================

Private Sub Form_AfterUpdate()

DoCmd.RunSQL "INSERT INTO ChangeLogSalesCIMST SELECT * FROM SalesCIMST " & _
        "WHERE WIPID=" & WIPID

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    DoCmd.RunSQL "INSERT INTO ChangeLogSalesCIMST SELECT * FROM SalesCIMST " & _
        "WHERE WIPID=" & WIPID
End Sub

======================

I hope this helps. I'm sure its having problems pulling the ID but that's all i know....

Cheers

So you need to reference the name of the control on the form and not the record source

Sorry, but just to be sure...

=======

DoCmd.RunSQL "INSERT INTO ChangeLogSalesCIMST SELECT * FROM SalesCIMST " & _
        "WHERE ***FORM CONTROL HERE***=" & WIPID

Nope other way round

"SELECT FieldName FROM Table WHERE FieldName = 1"

"SELECT FieldName FROM Table WHERE FieldName =" & "1"

Where is 1?

"WHERE ***FORM CONTROL HERE***=" & WIPID

"WHERE WIPID=" & WIPID

"WHERE Text_EntryID=" & WIPID

"WHERE WIPID=" & Text_EntryID

Please excuse dum-dum here, i've been staring at your post for 10 minutes and just cant make sense of it.

When you say "Where is 1", are you asking where the form control is? If so, its
[Forms]![SalesIndividualViewCIMSF]![Text_EntryID]

I tried
"WHERE WIPID=" & Text_EntryID
but didn't work.

Breaking this down...
DoCmd.RunSQL "INSERT INTO ChangeLogSalesCIMST - This is telling it to INSERT into the table
SELECT * FROM SalesCIMST " - This is saying "Copy everything from SalesCIMST"


If you jump to 4.00 minutes on the tracking changes video, Richard references
"WHERE CustomerID=" & CustomerID  (My CustomerID is WIPID)

WIPID is a number. Correct?

Autonumber - its my P Key

I just checked - my target table's WIPID field IS Number of Long Integer

If you MsgBox or Debug.Print Text_EntryID what do you get?

Dan -
First I REALLY suggest you consider the SQL Seminar 1 & 2.  It really is a help.

I have found that one way to learn the SQL is to let Access teach you.
Create a regular query and then change it into an append query.  Don't run it but change into SQL View and copy out the SQL to a text editor like Notepad or, my favorite, Notepad++. Just put in known values and then you can work toward changing to your variables later.

Is your ChangeLogSalesCIMST an exact copy of the table you are copying from?  The only difference is in the ChangeLogSalesCIMST the WIPID should NOT be an AutoNumber and it would need it's own AutoNumber id field.  You can't insert into an AutoNumber field, It would have to be just a Number of type LongInteger.

Where are you getting the WIPID value to use as your criteria?  Texbox on your form?
If so your WHERE clause would be  WHERE WIPID = & WIPID (assuming the textbox is named WIPID)

I know you said you changed the names to try and help here but I think that actually confused the issue.  Especially when we see screenshots of errors that don't have anything to do with what we're talking about.

I think it may be time for some screen caps of your forms and properties as well as the queries your working with.

Solved! Scotts prompt of "Is your ChangeLogSalesCIMST an exact copy of the table you are copying from?" did it.

Fixed by re-copying the original table, adding LogID field and modifying WIPID to PKey from FKey. I've added a .... somewhat strong.... reminder note to the top of the code asking if the original table has changed (Too many swear words to post here!!!)

@Scott, I will defo do the SQL seminar at some point but with the silver membership and MyOLP, i'm maxed on budget currently. Also need to finish expert at least before getting into that.

In future, i'll use ***PLACEHOLDER(Actual Name)*** when demoing, i think this'll help clear it up.

@Scott, @Alex and @Kevin - Thanks so much for all your time. I hope some day be able to return the favour by helping others on here.

Dan I appreciate your kudos to myself and the gang.  Definitely a group effort on this one.

I totally understand the budget deal.  I like my pizza all to well!
The first 2 SQL I would move to the top of the list when you are able.  That is something that will carry forward in all most all that you do in Access.  It was very valuable to me any way.

Will do, cheers

Also note that if you're going to use

INSERT INTO Table SELECT * FROM OtherTable

Then your field names have to match exactly, one-for-one with each table. Otherwise, you need a list of field names.