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
– Elias, Commented Jan 22, 2014 at 17:04
Please do not use set warnings: stackoverflow.com/questions/11213892/… — Fionnuala
– Fionnuala, Commented Jan 22, 2014 at 17:06
This tblNormaAppend.[SORT SCORE].Value is not defined. Try Me.[SORT SCORE].Value if you are running in a form's module. — Fionnuala
– Fionnuala, Commented Jan 22, 2014 at 17:06
@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
– ackdaddy, Commented Jan 22, 2014 at 18:04

pteranodon · Accepted Answer · 2014-01-22 21:35:26Z

1

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.

answered Jan 22, 2014 at 21:35

pteranodon

2,0591 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ackdaddy Over a year ago

Thanks for the input. The end goal is to create a radar chart based off of data from two specific fields in tblNormaAppend which houses scores from an audit. I have switched to trying to use straight SQL for this.

INSERT INTO tblNormaRadar (ATTRIBUTE, SCORE)     SELECT [SORT], [AvgOfSort Total]     FROM qryFilterMFG     WHERE GROUP_LEADER = [PUBLICREPORTS].[COMBO0]     UNION ALL SELECT [SUSTAIN], [AvgOfSUSTAIN Total]     FROM qryFilterMFG     WHERE GROUP_LEADER = "Acevedo, Norma";

ackdaddy · Accepted Answer · 2014-01-23 18:29:50Z

0

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.

answered Jan 23, 2014 at 18:29

ackdaddy

1313 gold badges5 silver badges16 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related