Append Query SQL Using a Variable? 3

[MrKABC]

Hello all:

I'm trying to include a variable (form value integer) in my SQL statement and I'm having difficulty with the syntax. I tried using VALUES without success. My code (removed extraneous VBA stuff):

I am trying to insert "strInteger" into TableB in the "IntegerID" slot.

dim strInteger as Integer
[indent]
strInteger = Me.FormField.Value

    strSQL = "INSERT INTO TableB ( Item, CategoryCode, SelectorCode, Description, ScoringAmount, IntegerID ) " _
    & "SELECT TableA.ID, TableA.CategoryCode, TableA.SelectorCode, TableA.Description, TableA.ScoringAmount,('&strInteger&') " _
    & "FROM TableA; "

 CurrentDb.Execute strSQL, dbFailOnError

'etc., etc.

[/indent]

I *know* I am not getting the syntax right to insert the variable. (the error messages are a hint) However, I am stuck. Does anyone know how I might do this?

Much appreciated!

A.

 

[randy700]

Not tested, but I think this should work:

& "SELECT TableA.ID, TableA.CategoryCode, TableA.SelectorCode, TableA.Description, TableA.ScoringAmount,[b][red]" & strInteger & "[/red][/b] " _

Randy

 

[Andrzejek]

The best (in my opinion) way to know what you are getting is this:

strSQL = "INSERT INTO TableB ( Item, CategoryCode, SelectorCode, Description, ScoringAmount, IntegerID ) " _
& "SELECT TableA.ID, TableA.CategoryCode, TableA.SelectorCode, TableA.Description, TableA.ScoringAmount, " & &strInteger& & ") " _
& "FROM TableA; "
[blue]
Debug.Print strSQL
[/blue]
 CurrentDb.Execute strSQL, dbFailOnError

And see it in your Immediate Winndow.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

Andy had a couple small issues with his code left over from copy and paste. There were two extra ampersands and one extra ). Using his recommendation to use debug.print should easily help identify any issues. I rarely execute a SQL statement from code until after I have used debug.print to verify the statement.

strSQL = "INSERT INTO TableB ( Item, CategoryCode, SelectorCode, Description, ScoringAmount, IntegerID ) " _
& "SELECT ID, CategoryCode, SelectorCode, Description, ScoringAmount, " & strInteger & " " _
& "FROM TableA; "

Duane
Hook'D on Access
MS Access MVP

 

[MrKABC]

Randy, Andy, and Duane, thank you for your answers, it now works like a charm!

Also, I didn't know about the debug.print/Immediate Window feature. I have learned something new!

Thanks guys, you saved me! Cheers!

 

[Andrzejek]

MrKABC, even if you do not have Debug.Print in your code, you can always put a break point on this line of code:

[tt]CurrentDb.Execute strSQL, dbFailOnError
[/tt]
and in Immediate Window type:
[tt]
? strSQL
[/tt]
and hit Enter. You get your strSQL, too.
I work in a goup of other developers and often I have to access code where I only have read-only file, so I cannot add to the code. This way I can always check the value of the variable.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.