In Access using a Data Macro to update a second table when value is entered

Guessing that you have a similar case with a combobox in the After Update event

Dim strSQL as String
strSQL = "UPDATE tblservicelineitem SET EndOfLife = #" & Me.EndOfLifeDate & "#  WHERE HWSWreference = " & Me.HWSWreference 
Currentdb.execute strSQL,dbFailOnError

Select allOpen in new window

Please correct the tag...since this is a Microsoft Access question....i don't think Golang has anything to do here...

John I am sure you are right but I am getting compile errors and such.  I will work on it.

Maybe the nature of the data...
In an SQL alike string you need to match against the correct datatype...since i don't have insight view of what your data are i went with the usual...check the control names and their datatype
Check if this alternate version works

strSQL = "UPDATE tblservicelineitem SET EndOfLife = #" & Me.EndOfLifeDate & "#  WHERE HWSWreference = '" & Me.HWSWreference & "'"

Select allOpen in new window

If you still have issues post a sample

John

I stripped my database and just gave you the two tables.  I should have done that in the first place.  I also gave you the form I am using.  I know it has something to do with the way the original designer named the tables.  Maybe this will help.  Thanks again.
Database5.accdb

Nice so easy when you know... can you put one more check in if someone deletes a date in that field is give me a error.  Like I put it on the wrong part number

The example was perfect and he was very responsive

From what I see, it is wrong to add endoflifedate to the second table.  This is what normalization is all about.  Each piece of data has one and only one home.  The only data that appears in multiple places is the foreign keys that are used to connect tables so  HWSW Reference is the primary key for tblManufacturePartNumber and is the foreign key in tblServiceLineItem.  When you need to know data from the manufacture table simply join to it.

If you insist on doing this even though it violates normalization rules, use a data macro. Do NOT use the update method previously suggested.

PS - object names should not include embedded spaces or ANY special characters.  The only allowed non letter, non number character is the underscore.  So HWSW_Reference is valid but not HWSW Reference.  My preference is HWSWReference (which is called CamelCase) because I don't like the underscores.  I find them jarring and use them only when I want a sharp separation in an object name.

I just noticed that you already selected John's reply as the "answer".  I disagree.  Duplicating data is wrong and duplicating it using an update query rather than a data macro is even more wrong if that is possible.

Change the After Update event like this to handle the Delete

Private Sub Endoflifedate_AfterUpdate()
Dim strSQL As String
If Len(Me.endoflifedate) > 0 Then

strSQL = vbNullString
strSQL = "UPDATE tblservicelineitem SET endoflifedate = #" & Me.endoflifedate & "# WHERE [HWSW Refernce] = '" & Me.HWSW_Reference & "'"
CurrentDb.Execute strSQL, dbFailOnError
End If
End Sub

Select allOpen in new window

Pat... Very interesting.. I can see what you are referring too.  I marked it as my solution because it did what I wanted it to do .  I don't know how to do it any other way.  Because I inherited the database I have to live with lots of mistakes.  I understand I am duplicating data but at this point that is how the other linked forms are grabbing it.  As I rebuild the database I will make other changes to it to get it the way it should be.  This should work for me now.  I do appreciate the feedback.

As for Normalization..yes it does has some design issues...
From a quick glance it would take some work to move everything in the right place....
As Pat mentioned ...everything needs to occur once and only once...so only once we input the EndOfDate ...but its not the first time i have seen ghosts of the past...somehow something started with just a couple a tables to record some data just instead of Excel and before you know it you have a mini Erp in your hands (or it could be a fully one...:)  )

As you can see by John's additional post, there are many holes in this method and the updated solution doesn't fix them.  If you MUST duplicate the data at least use a data macro.  That only does the update when the source is actually changed and it does it when the source is changed by ANY form or query not just the one where the current code runs.  And this includes changes from outside of Access although I doubt that you have this issue.  When you use a Data macro, that macro will run REGARDLESS of how the table gets updated.  If the BE were shared between a web page and your FE and the change came from the ASP code, the Data macro would STILL ensure that the second table also got updated.  Personally, I wouldn't do this but as I said, if you are going to do it, at least do it safely since Access now supports Data macros.

understand I am duplicating data but at this point that is how the other linked forms are grabbing it

Not if it doesn't already exist.  You are just making a bad situation worse.  You are going to have to modify forms and reports and queries to pick up the additional piece of data, there is nothing to prevent you from doing that correctly as you make the change.  Making a correct change shouldn't be any more onerous than making an incorrect change.

Pat ...

Can you do a update sql string on a data macro, I know you can do it but I was lost?  Believe it or not that's where I was starting but I was not sure how to do it.  I can see the action catalog but I did not know where to go after that...

Since you've already accepted an answer to this thread, please start a new one asking how to create a data macro and specify exactly what it needs to do.  I'm not sure I can help.  As I said, I would never do this but I'll see if I can figure it out.

Okay