Need help with VBA to Insert/Update Date from Subform

[GeminiG]

Hello,
I have a database and I can't figure out how to take a field from a subform and then add that date into an existing table.

The frmEvent has a subform. (The subforms data is based on an un-updateable recordset from a query)
When the user clicks the EDIT button I want the EventDate from the subform to be put into the tblEventException.OrigEventDate field. The EventDate comes from a query. (qryEventDates)

I tried using an Update SQL statement but it's not working correctly::banghead:

Private Sub cmdEdit_Click()
On Error GoTo Err_Handler

    Dim db As DAO.Database
    Dim strSQL As String
    Dim strWhere As String
        strWhere = "(EventID = " & Nz(Me.EventID, 0) & ") AND (InstanceID = " & Nz(Me.InstanceID, 0) & ")"
    
    If IsNull(Me.EventID) Or IsNull(Me.InstanceID) Then
        MsgBox "Unable to determine the entry to edit.", vbExclamation, "Cannot edit."
    Else
        strSQL = "UPDATE tblEventException " _
       & "SET OrigEventDate = #" & Forms!frmEvent!frmEventSub!EventDate & "# " _
       & "WHERE " & strWhere
        Debug.Print strSQL
        CurrentDb.Execute strSQL, dbFailOnError
    
    DoCmd.OpenForm "frmEventException", WhereCondition:=strWhere, WindowMode:=acDialog
    End If
 
Exit_Handler:
    Exit Sub
    
Err_Handler:
    Call LogError(Err.Number, Err.Description, conMod & ".cmdEdit_Click")
    Resume Exit_Handler
End Sub

Maybe I need to use an INSERT statement? If so how would I code that in VBA? I have zero knowledge of using SQL in VBA so i could be missing something. This code was inherited and is based on Allen Browne's recurring activities located at :allenbrowne.com/AppRecur

 

[pbaldy]

What does "not working correctly" mean exactly? An update query will edit an existing record or records. An append (INSERT) query will add a new record or records.

 

[GeminiG]

Ok so then the update won't work because there are no existing records. How do you write the Insert/append statement then?

 

[pbaldy]

For a single record the basic syntax is:

INSERT INTO TableName(Field1, Field2...)
VALUES (Value1, Value2...)

with the same delimiter requirements of quotes around text values and # around dates.

 

[GeminiG]

Ok now I get an error that my syntax is not correct. What am I missing?

strSQL = "INSERT INTO tblEventException " _
       & "VALUES OrigEventDate = #" & Forms!frmEvent!frmEventSub!EventDate & "# " _
       & "WHERE " & strWhere
        Debug.Print strSQL
        CurrentDb.Execute strSQL, dbFailOnError

 

[pbaldy]

What is the result of the debug?

http://www.baldyweb.com/ImmediateWindow.htm

You didn't really pay attention to my example. The parentheses, field names, no WHERE clause.

 

[GeminiG]

Sorry, thanks for the help and your patience.
I changed it to this:
strSQL = "INSERT INTO tblEventException (OrigEventDate) VALUES (#" & Forms!frmEvent!frmEventSub!EventDate & "#);"
The result in the immediate window looks correct, but I get the error unable to save record at this time.

 

[GeminiG]

Ok I see the problem, i need to put this on the new form. Thanks for the help!

 

[pbaldy]

No problem!