Object Required Error when executing SQL "Insert Into" Command in Access 2007 VBA

Question

I have used this method countless times, but I cannot for the life of me get this to work. I keep tripping a run-time error "424". Any help is appreciated as tblNormaAppend.[SORT SCORE].Value shows as "empty" and so does tblNormaAppend.SORT.Value. Both have values within the tables. Thank you so much for any help.

Private Sub Command9_Click()
DoCmd.SetWarnings False


DoCmd.OpenQuery "qryDeleteNormaRadar"
DoCmd.OpenQuery "qryDeleteNormaAppend"
DoCmd.OpenQuery "qryFilterMFG"
DoCmd.OpenQuery "qryNormaAppend"


Dim strSQL As String
    strSQL = "INSERT INTO tblNormaRadar (Attribute, Score) VALUES (" & tblNormaAppend.[SORT SCORE].Value & ", '" & tblNormaAppend.SORT.Value & "');"
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
End Sub

Does it have dbo_ in front of it in the table tab of the object explorer? — Elias
Please do not use set warnings: stackoverflow.com/questions/11213892/… — Fionnuala
This tblNormaAppend.[SORT SCORE].Value is not defined. Try Me.[SORT SCORE].Value if you are running in a form's module. — Fionnuala
@Elias, I'm not quite sure what you mean. @Remou, I removed set warnings, however there were no issues in the preceding lines of code anyway. Also, I cannot use me. as I am not pulling from a form field. I just do not understand why the objects are showing as "empty". — ackdaddy

pteranodon pteranodon · Accepted Answer · 2014-01-22T21:35:26

It is unclear to me what tblNormaAppend is. In the context of a command button's code, it looks like a form control, but it sounds like you are referring to fields from a table, not values in the current record of the form.

If you just want to insert all of the values from one table into another, you can write straight (non-concatenated) SQL for this:

'add one record to tblNormaRadar for each record in tblNormaAppend
strSQL = "INSERT INTO tblNormaRadar (Attribute, Score) SELECT [SORT SCORE], [Sort] FROM tblNormaAppend;"
Dim db As DAO.Database
Set db = CurrentDB
db.Execute strSQL, dbFailOnError

If you are aiming for something else, please describe more about tblNormaAppend and which values you want added to tblNormaRadar.

Object Required Error when executing SQL "Insert Into" Command in Access 2007 VBA

2 Answers