CODEWITHSUNDEEP
sqlms-accessms-access-2007vba
ackdaddy
ackdaddy

Reputation: 131

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

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

Upvotes: 0

Views: 2041

Answers (2)

ackdaddy
ackdaddy

Reputation: 131

I use straight SQL. I created multiple INSERT INTO queries since Access 2007 will not let you UNION ALL multiple fields within the same query.

Thanks for all the help.

Upvotes: 0

pteranodon
pteranodon

Reputation: 2059

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.

Upvotes: 1

Related Questions