Runtime 3078 While Using INSERT from a Recordset

[dccjr3927]

Good Evening:

I have been in non-VBA mode for the past year, and cannot seem to see thing that are probably obvious to others. I am getting a Runtime 3078 (cannot find the input table or query "INSERT INTO...") Below is the sub. I would really appreciate it if someone could lend a second pair of eyes. I have double checked that table and field names are correct.

Private Sub cmdLogVol_Click()
    Dim strInsertSQL, strQuerySQL As String
    Dim dteVolDate As Date
    Dim db As DAO.Database
    Dim rsInsert As Recordset
    dteVolDate = Format(Date, "Short Date")
    Set db = CurrentDb
    strQuerySQL = "SELECT Company, SKU, InvVolume FROM Inventory WHERE Company = 'Contract - Company1' OR Company = 'Contract - Company2';"
    Set rsInsert = db.OpenRecordset(strQuerySQL)
    strInsertSQL = "'INSERT INTO InvVolData (Company, SKU, InvVolume, VolDate) VALUES (' & rs!Company & ',' & rs!SKU & ',' & rs!InvVolume & ',' & dteVolDate & ';'"
    db.Execute strInsertSQL, dbFailOnError
End Sub

Thanks in advance for any insight.

[ranman256]

Instead of SQL,use a query.
it won't get the syntax wrong. Then just run it: docmd.openquery "qaInsertQry"

[dccjr3927]

Hi ranman256:

Not really sure what you mean. Do you mean build a query with the query builder/wizard? To be perfectly honest, I have never built a query that way.

[CJ_London]

you are missing a closing bracket in your VALUES part

you are also missing double quotes between each rs! value

suggest put debug.print strInsertSQL after you have created it to see what it actually looks like before you execute it

dates need to be in the format mm/dd/yyyy and surrounded with the # character

not clear what your data types are but text needs to be surrounded with single quotes, numbers do not

other issues - you are not checking that rs is populated - perhaps there are no records that meet that criteria?

[dccjr3927]

Thank you very much. With your changes, it works perfectly.

Here is the corrected code for others reference:

Private Sub cmdLogVol_Click()
    Dim strInsertSQL, strQuerySQL As String
    Dim dteVolDate As Date
    Dim db As DAO.Database
    Dim rsInsert As Recordset
    
    dteVolDate = Format(Date, "Short Date")
    Set db = CurrentDb
    strQuerySQL = "SELECT Company, SKU, InvVolume FROM Inventory WHERE Company = 'Contract - Company1' OR Company = 'Contract - Company2';"
    Set rsInsert = db.OpenRecordset(strQuerySQL)
    
    If Not rsInsert.EOF Then
        Do Until rsInsert.EOF
        strInsertSQL = "INSERT INTO InvVolData (Company, SKU, InvVolume, VolDate) VALUES ('" & rsInsert!Company & "','" & rsInsert!SKU & "'," & rsInsert!InvVolume & ",#" & dteVolDate & "#);"
        db.Execute strInsertSQL, dbFailOnError
        rsInsert.MoveNext
        Loop
    End If
    
End Sub

[Micron]

Wondering if you realize that strInsertSQL is a variant because you didn't specify a data type for it? That is

Dim strInsertSQL As String, strQuerySQL As String