append query in VBA (run-time error 3067)

Question

I'm pulling seven values from unbound text boxes on a form into variables. Five of the variables are string type, two are double. I'm then using sql to append the data to a table using a where statement and a global variable which contains a foreign key I used from another table, since I was unsure how to use openargs with browseto...

Option Compare Database
Private Sub Form_Load()

Dim rowN, rowR, mat, crew, perCom As String
Dim budEst, curBud As Double
End Sub

Private Sub btnCapSubmit_Click()
rowN = Me.CAP_ROW_N
rowR = Me.CAP_ROW_R
mat = Me.CAP_MAT
crew = Me.CAP_CREW
perCom = Me.CAP_PER
budEst = Me.CAP_BUD_EST
curBud = Me.CAP_BUD_CUR

Dim appendIt As String
appendIt = "INSERT INTO CAPITAL " & _
    "([CAPITAL].[CAP_ROW_N], CAPITAL.[CAP_ROW_R], [CAPITAL].[CAP_MAT], [CAPITAL].[CAP_CREW], [CAPITAL].[CAP_PER], [CAPITAL].[CAP_BUD_EST], [CAPITAL].[CAP_BUD_CUR]) " & _
    "VALUES ('" & rowN & "','" & rowR & "','" & mat & "','" & crew & "','" & perCom & "','" & budEst & "','" & curBud & "') WHERE [PRO_ID] = '" & gblFind & "';"
Debug.Print appendIt
DoCmd.RunSQL appendIt
DoCmd.BrowseTo acBrowseToForm, "frmSearchEdit", "NavForm.NavigationSubform", , , acFormEdit
End Sub

Access complains with error #3067, "Query input must contain at least one table or query."

I have no idea what I'm doing. I tried using debug.print but didn't see anything right off the bat. Then again I've been working on this database all day, so I could be overlooking something really easy.

P.S. I also tried replacing the variables with Me.CAP_ROW_N (textbox names), but no dice.

How the appendIt looks like? Did you test it in the database directly? — gembird
– gembird, Commented Dec 30, 2014 at 22:00
I'd say you want to update data instead of insert. Am i right? — Maciej Los
– Maciej Los, Commented Dec 30, 2014 at 22:38
You would be wise to add Option Explicit to the Declarations section of your form's code module (actually you should include it in every module) and then run Debug->Compile from the VB Editor's main menu. Fix the first compiler complaint and run Compile again. Repeat until no more compiler complaints. You will likely uncover additional issues which have not been addressed by the current answers. — HansUp
– HansUp, Commented Dec 30, 2014 at 22:44
Your variable declarations don't work the way you think they are. In Form_Load, rowN, rowR, mat, and crew are all declared as Variant rather than String, and budEst is also a Variant. Multiple declarations on a single line all need to be individually typed. Further, in your btnCapSubmit_Click routine, all the variables are Variant, as they are not declared within the scope of the procedure. — citizenkong
– citizenkong, Commented Dec 31, 2014 at 9:24

mwolfe02 · Accepted Answer · 2014-12-30 22:15:55Z

6

It's unclear what you are trying to do here, but an INSERT INTO ... VALUES () statement does not take a WHERE clause. Error 3067 is "Query input must contain at least one table or query." You are likely seeing this error because you have included a WHERE clause but you are not selecting existing values from a table.

Try this instead:

appendIt = "INSERT INTO CAPITAL " & _
"([CAPITAL].[CAP_ROW_N], CAPITAL.[CAP_ROW_R], [CAPITAL].[CAP_MAT], [CAPITAL].[CAP_CREW], [CAPITAL].[CAP_PER], [CAPITAL].[CAP_BUD_EST], [CAPITAL].[CAP_BUD_CUR]) " & _
"VALUES ('" & rowN & "','" & rowR & "','" & mat & "','" & crew & "','" & perCom & "','" & budEst & "','" & curBud & "');"

There are several other issues here as well. I will just list them and let you Google for more guidance:

You should use the .Execute DAO method instead of DoCmd.RunSQL because it allows for better error handling, especially when used with the dbFailOnError option.
You will eventually run into trouble using single-quotes on unescaped inputs. For example, WHERE LastName = 'O'Malley'
You appear to be treating all seven values as text by wrapping them in quotes, even though you said two of your values were numeric (double). Numeric values do not get quotes.

edited Dec 30, 2014 at 22:15

answered Dec 30, 2014 at 22:09

mwolfe02

24.3k10 gold badges102 silver badges174 bronze badges

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

3 Comments

Marc Over a year ago

Oh, gotcha. I guess I need to clarify then, the previous form inserts a primary key into the CAPITAL table, but leaves the other columns blank. I'm trying to make this form fill in the missing columns in the row with the matching PK (PRO_ID). How do I specify what row these values go into?

mwolfe02 Over a year ago

You would use an UPDATE statement, not an INSERT INTO.

Marc Over a year ago

Haha, alright, I feel pretty dumb now. Thanks for your help!

HansUp · Accepted Answer · 2014-12-30 22:21:45Z

Do not qualify the field names with the table name in your field list.

A WHERE clause doesn't belong in an INSERT ... VALUES statement; get rid of that.

This is a smaller-scale example of the pattern I think you want:

appendIt = "INSERT INTO CAPITAL " & _
    "([CAP_ROW_N], [CAP_ROW_R]) " & _
    "VALUES ('" & rowN & "','" & rowR & "');"

However, I suggest you tackle this with a parameter query.

appendIt = "INSERT INTO CAPITAL " & _
    "(CAP_ROW_N, CAP_ROW_R) " & _
    "VALUES (pCAP_ROW_N, pCAP_ROW_R);"

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, appendIt)
qdf.Parameters("pCAP_ROW_N") = Me.CAP_ROW_N.Value
qdf.Parameters("pCAP_ROW_R") = Me.CAP_ROW_R.Value
qdf.Execute dbFailOnError

Note I used the text box values for the parameter values directly --- instead of declaring variables to hold the text box values.

Also notice one of the benefits of parameter queries is you needn't bother with delimiters for the values: quotes for text; or # for dates.

Collectives™ on Stack Overflow

append query in VBA (run-time error 3067)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related