Syntext Error

Question

I've created a transaction form in Accesss, and I'm using VBA to assist in building the record.  My fields most of my fields are depended upon receiving information from two other tables fields.  This record has to be created.  I'm pulling my organization code from the Organization Table and putting it in the Transaction record.  Also, I'm setting the creation date to the current date.  Thi is what I want to do.  But, I'm having 1. Syntext problems and second I'm getting an "End of Statement error on the Transaction table called "Auxiliary Transaction."  Below I put the exemple.

Private Sub Church_Organizations_BeforeUpdate(Cancel As Integer)
    INSERT INTO [Auxilary Transactions] ([Organization], [Creation Date]) & _
                    SELECT Organization FROM [Organization Table], VALUES (Date$);
    
End Sub

Answer 1

Private Sub Church_Organizations_BeforeUpdate(Cancel As Integer)

    INSERT INTO [Auxilary Transactions] ([Organization], [Creation Date]) & _
                    SELECT Organization FROM [Organization Table], VALUES (Date$);
    
End Sub

You can't execute a SQL statement in quite that way.  You need to specify the statement as a character string and then execute it using either the CurrentDb.Execute method or the DoCmd.RunSQL method. Something like this:

Private Sub Church_Organizations_BeforeUpdate(Cancel As Integer)

    CurrentDb.Execute _
        "INSERT INTO [Auxilary Transactions] (Organization, [Creation Date]) " & _
            "SELECT Organization, Date() FROM [Organization Table]", _
        dbFailOnError;
    
End Sub

However, that SQL statement as written will insert a record into table [Auxilary Transaction] for every record in table [Organization Table].  Is that really what you want to do?  It seems to me most likely that you either want to select a specific record from [Organization Table] -- which you can do by specifying a WHERE clause as part of the SQL string -- or else you already have the Organization value on your form, and you can build that literal value into a SQL string that uses the VALUES clause instead of a SELECT clause.

---

Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html