Using VBA and SQL - mismatch error

[ImoBase]

Hi,

When I run this code (from a button on a form, in relation to a subform record) I receive this error:

Run-time error '13':
Type mismatch

When I debug.print the SQL and put it into a blank query, the query runs and appends a new record to the appropriate table.

I am perplexed. Anyone advise on why this might be happening?


--------------
VBA
--------------

Dim mySQL As String
Dim lngContact As Long
Dim lngDiscount As Long
Dim strMessage As String
Dim strUsername As String
Dim dtDateNow As Date

lngContact = Me!ContactID
dtDateNow = Now()
lngDiscount = 4
strMessage = "New member Discount"

strUsername = fOSUserName()

mySQL = "INSERT INTO tblCONTACTDISCOUNTINDEX ( ContactID, DiscountID, Description, AddedBy, DateAdded ) "
mySQL = mySQL & "SELECT " & lngContact & " AS Expr1, " & lngDiscount & " AS Expr2, "
mySQL = mySQL & "'" & strMessage & "'" & " AS Expr3, " & "'" & strUsername & "'" & " AS Expr4, " & "'" & dtDateNow & "'" & " AS Expr5"

Debug.Print mySQL

DoCmd.SetWarnings False
DoCmd.RunCommand mySQL
DoCmd.SetWarnings True


-----------------------
working SQL from Debug.print
-----------------------
INSERT INTO tblCONTACTDISCOUNTINDEX ( ContactID, DiscountID, Description, AddedBy, DateAdded ) SELECT 14433 AS Expr1, 4 AS Expr2, 'New member Discount' AS Expr3, 'testUser' AS Expr4, '24/09/2012 09:44:01' AS Expr5

 

[mdlueck]

Two suggestions:

1) Do you have error handling enabled in your code? If not, please consider adding such and perhaps it will reveal more about the root of the complaint.

Example of Stock Error Handling in VBA
http://www.access-programmers.co.uk/forums/showthread.php?p=1179931#post1179931

Example of Centralized Error Logging
http://www.access-programmers.co.uk/forums/showthread.php?t=230291#post1175164

2) Why not use VALUES in place of SELECT in your INSERT?

Example of SQL INSERT / UPDATE using ADODB.Command and ADODB.Parameters objects to Access tables
http://www.access-programmers.co.uk/forums/showthread.php?t=219149

 

[Rat1sully]

shouldn't it be DoCmd.RunSQL mySQL

 

[ImoBase]

Thanks both. Rat1Sully, you are correct. The code worked after this amend, remarkable that I missed it. Appreciated.

mdlueck, thanks for the links. Using values - the code isn't completed, I am building it up, albeit slowly, to do something more than it is doing now and the values will be variable, based on user input.

 

[Rat1sully]

glad to help the little mistakes like that can be the most infuriating