RunCode SQL statement

[Bopsgtir]

Sql Statement Syntax error?????

Hi all id like to be able to enter a record in my fuelcardtable when i click a button on a form, im using the below code, which is run with the RunCode part of a macro.

The object doesn't contain the automation object 'clsFuelCard.'

You tried to run a Visual Basic procedure to set a property or method for an object, However, the component doesn't make the property or method available for the automation operations.

Check the component's documentation for information on the properties and methods it makes available for automation operations


Option Compare Database

Dim sSQL As String

sSQL = "INSERT INTO TblFuelCard (DateOrdered,OrderReason) values ('Date()','New Starter')"

DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True

End Sub

 

[CBrighton]

I don't think Date() should be surrounded by 's.

I also thought RunCode could only run functions within their own modules, interesting to see you are using it on a sub.

 

[vbaInet]

There's no Private Sub line.

Go back to your property sheet, click the elipsis button of your button's On Click event, select Code Builder and paste the right code between the Private Sub and End Sub lines.

 

[Bopsgtir]

ok thank you, ive now got it working with

Private Sub NewStarterSubmit_Click()

Dim sSQL As String

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SendObject acForm, "frmNewStarterForm", "Excel97-Excel2003Workbook(*.xls)", "deanrobinson@comex2000uk.com", "", "", "new start", "new", True, ""
        If (Me.Fuel_Card_Required = True) Then
            DoCmd.SendObject acForm, "frmNewStarterForm", "Excel97-Excel2003Workbook(*.xls)", "deanrobinson@comex2000uk.com", "", "", "fuel card", "please order", True, ""
            
sSQL = "INSERT INTO TblFuelCard (DateOrdered,OrderReason) values (Date(),'New Starter')"

    DoCmd.SetWarnings False
    DoCmd.RunSQL sSQL
    DoCmd.SetWarnings True
        
        End If
            DoCmd.GoToRecord acForm, "frmNewStarterForm", acNewRec
    End Sub

 

[vbaInet]

Good job!

 

[Bopsgtir]

Sorry one more question, how can i concatenate two fields from the form into one of the value fields of the sql statement.

in the field order reason, i want it to say Card Ordered For New Starter & "FirstName" & "Surname"

 

[vbaInet]

"Card Ordered For New Starter " & [FirstName] & " " & [Surname]

 

[Bopsgtir]

ive still managed to do it wrong??

sSQL = "INSERT INTO TblFuelCard (DateOrdered,OrderReason) values (Date(),""Card Ordered For New Starter " & [FirstName] & " " & [Surname]

Im getting a syntax error

 

[vbaInet]

Well, I thought you were doing that for display purposes only. Calculated values should not be stored.

 

[bparkinson]

vbaInet is right. However, in the case of concatenating a name, depending on your app, it can be a good idea. I do just this in my commercial app, because the lastname, firstname is how the users think about the 30 or so people that the deal with.

That said, I use SQL Server as the BE, so it's nothing to have a ON UPDATE trigger, so that if their name changes from anywhere or any form, it's automatically updated in the derived column, and the performance hit is nil.

In Access, I don't know an elegant way to do this other than to put code in a class for reuse, and call that class's "UpdateFullName" method in the AfterUpdate event of every form that could update the person's name, which could be a lot of forms.

So I guess I've nullified my own argument.

 

[bparkinson]

Then there are Data Macros in Access 2010 that look interesting.

http://blogs.office.com/b/microsoft...ess-2010-data-macros-similar-to-triggers.aspx