Need help with VBA to Insert/Update Date from Subform (1 Viewer)

GeminiG · Mar 1, 2018

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:

Code:

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 · Mar 1, 2018

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 · Mar 1, 2018

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

pbaldy · Mar 2, 2018

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 · Mar 2, 2018

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

Code:

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

pbaldy · Mar 2, 2018

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 · Mar 2, 2018

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 · Mar 2, 2018

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

pbaldy · Mar 2, 2018

No problem!

Need help with VBA to Insert/Update Date from Subform (1 Viewer)

GeminiG

New member

Attachments

pbaldy

Wino Moderator

GeminiG

New member

pbaldy

Wino Moderator

GeminiG

New member

pbaldy

Wino Moderator

GeminiG

New member

GeminiG

New member

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread