AfterUpdate Event to INSERT new Record in 2nd table

[Keegan]

In MS Access 2010, I am using the after update event on a date field in the primary form to populate/insert a new record in the subform. But my SQL code is not working; please help.




Private Sub Date_AfterUpdate()


Dim strSQL As String


strSQL = "INSERT INTO Comm_Poll_Data ([LCC]) VALUES('A') WHERE Mission.MissionID = Comm_Poll_Data.[Mission ID] AND Comm_Poll_Data.[LCC] IS NULL;"


DoCmd.RunSQL strSQL


End Sub

[pbaldy]

There are two valid formats:

INSERT INTO...
VALUES...

and

INSERT INTO...
SELECT...
FROM...
WHERE...

You've combined the two, which you can't do.

[Keegan]

Understood.
strSQL = "INSERT INTO Comm_Poll_Data ([LCC]) VALUES('A');"
When I try above code it still does not work.

[pbaldy]

What does "does not work" mean? Do you get an error, and if so what is it? What happens if you paste that SQL into a new query and try to run it? You'll often get a more descriptive error. Are there any required fields in the table that aren't being populated?

[Keegan]

I mean I cannot even get the AfterUpdate to fire. Its as if the code isn't even there.
You stated to paste the SQL statement into a new query and try to run it; how can I do this for the AfterUpdate?

[pbaldy]

2010 requires that either the db be in a Trusted Location or that you explicitly enable code. Have you done that? Is any code at all running? This is the after update event of a textbox? If so, are you trying to fire it by typing in a value and pressing enter?

[Keegan]

I have explicitly enabled the code and no, its not executing at all. The AfterUpdate event resides in a date box -- after user enters the date or chooses it from the pop-up calendar, it should fire the event but I am not getting anything not even the debugger. I even entered a button to execute the same code, but that isn't working either. I am at a lost, but I greatly appreciate your help pbaldy.

[pbaldy]

Can you post the db here?