The [SVHCtemp] can also be changed to yes/no, since it is a new field. I do not know what is preferable.
Whatever you want/need the field type to be..
It would really help to see your dB, to see how you have this set up.
So, if you have a control named "CASNo" on a form, add this code to the after update event of the control.
Code:
Option Compare Database 'should be the first line of every module
Option Explicit 'should be the second line of every module
'requires a reference to Microsoft DAO 3.6 Object Library
Private Sub CASNo_AfterUpdate()
Dim dB As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String
Set dB = CurrentDb
sSQL = "SELECT Substance.CASNo"
sSQL = sSQL & " FROM Substance INNER JOIN ListofSVHCs ON Substance.CASNo = ListofSVHCs.CASnumber"
sSQL = sSQL & " WHERE Substance.CASNo = '" & Me.CASNo & "';"
Set rs = dB.OpenRecordset(sSQL)
If rs.BOF And rs.EOF Then
sSQL = "UPDATE Substance SET Substance.SVHCtemp = 'No'"
Else
sSQL = "UPDATE Substance SET Substance.SVHCtemp = 'Yes'"
End If
sSQL = sSQL & " WHERE Substance.CASNo = '" & Me.CASNo & "';"
dB.Execute sSQL, dbFailOnError
rs.Close
Set rs = Nothing
Set dB = Nothing
End Sub
This will update Substance.SVHCtemp in the table. If there is a control on the form bound to SVHCtemp, the value might not show up without re-querying the form. It would be better to modify the code to push the value (Yes/No) into the control.