SQL Insert statement VBA (1 Viewer)

colossus02 · Jul 16, 2018

I'm having a very tough time trying to use a insert SQL statement in VBA. I built the query in query design so I know it works, I just can figure out the problem in VBA. I believe it has something to do with the textbox value I'm bringing into the query. Any help would be greatly appreciated.

Code:

DoCmd.RunSQL ("INSERT INTO tbl_WP_Acquisitions.WPID, tbl_WP_Acquisitions.AQID, tbl_WP_Acquisitions.ShortTitle " _
                    & "Select (" & Me.txtCurrentID.Value & "), tmptbl_Acquisitions.AQID, tmptbl_Acquisitions.ShortTitle FROM tmptbl_Acquisitions " _
                    & "WHERE tmptbl_Acquisitions.Selected = True")

plog · Jul 16, 2018

You might have used design view to build a generic version of the query, but you need to find out what the specific SQL is of the query you are building is.

So, create a string variable (strSQL), build the SQL string inside of it, spit it out to see what SQL you are actually using.

JHB · Jul 16, 2018

Is WPID field a number or a text field type, (if text surround it by ' ) ?

Code:

([COLOR=red][B]'[/B][/COLOR]" & Me.txtCurrentID & "[B][COLOR=red]'[/COLOR][/B])

colossus02 · Jul 16, 2018

JHB said:
Is WPID field a number or a text field type, (if text surround it by ' ) ?

Code:

([COLOR=red][B]'[/B][/COLOR]" & Me.txtCurrentID & "[B][COLOR=red]'[/COLOR][/B])

No it not text.

colossus02 · Jul 16, 2018

plog said:
You might have used design view to build a generic version of the query, but you need to find out what the specific SQL is of the query you are building is.

So, create a string variable (strSQL), build the SQL string inside of it, spit it out to see what SQL you are actually using.

To be honest, I don't really understand how to split it out. I set the SQL as a variable like you stated and below is the output:

Code:

INSERT INTO tbl_WP_Acquisitions.WPID, tbl_WP_Acquisitions.AQID, tbl_WP_Acquisitions.ShortTitle Select '3393', tmptbl_Acquisitions.AQID, tmptbl_Acquisitions.ShortTitle FROM tmptbl_Acquisitions WHERE tmptbl_Acquisitions.Selected = True

I really want to understand this.

Mark_ · Jul 16, 2018

Maybe this will make it a little clearer?

Code:

DIM asSQL as STRING

'We build up the string one piece at a time, adding the next to the end of the previous.
asSQL = INSERT INTO tbl_WP_Acquisitions.WPID, tbl_WP_Acquisitions.AQID, tbl_WP_Acquisitions.ShortTitle "
asSQL = asSQL & "Select (" & Me.txtCurrentID.Value & "), tmptbl_Acquisitions.AQID, tmptbl_Acquisitions.ShortTitle " 
asSQL = asSQL & "FROM tmptbl_Acquisitions "
asSQL = asSQL & "WHERE tmptbl_Acquisitions.Selected = True"

'The following is for testing so you can SEE what is being passed.
msgbox "The SQL to run is: " & asSQL

DoCmd.RunSQL asSQL

Then you can look at the SQL you are using. Others will suggest using debug.print, but there are several ways of doing this and each of us has our ow preference.

JHB · Jul 17, 2018

I think you're a missing (), (and you don't need the Me.txtCurrentID.Value, Value is default)

Code:

DoCmd.RunSQL ("INSERT INTO[B][COLOR=red] ([/COLOR][/B]tbl_WP_Acquisitions.WPID, tbl_WP_Acquisitions.AQID, tbl_WP_Acquisitions.ShortTitle[B][COLOR=Red])[/COLOR][/B] " _  
& "Select (" & Me.txtCurrentID & "), tmptbl_Acquisitions.AQID, tmptbl_Acquisitions.ShortTitle FROM tmptbl_Acquisitions " _   
& "WHERE tmptbl_Acquisitions.Selected = True")

GinaWhipp · Jul 17, 2018

This should work...

Code:

    Dim strSQL As String
    
    strSQL = "INSERT INTO tbl_WP_Acquisitions (WPID, AQID, ShortTitle) " & _
                "SELECT " & Me.txtCurrentID.Value & ", AQID, ShortTitle " & _
                    "FROM tmptbl_Acquisitions " & _
                        "WHERE tmptbl_Acquisitions.Selected = True"
              CurrentDb.Execute strSQL, dbFailOnError

SQL Insert statement VBA (1 Viewer)

colossus02

New member

plog

Banishment Pending

JHB

Have been here a while

colossus02

New member

colossus02

New member

Mark_

Longboard on the internet

JHB

Have been here a while

GinaWhipp

AWF VIP

Similar threads

Users who are viewing this thread