CODEWITHSUNDEEP
ms-accessvbams-access-2010
Marc
Marc

Reputation: 33

append query in VBA (run-time error 3067)

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.

Upvotes: 3

Views: 1251

Answers (2)

HansUp
HansUp

Reputation: 97101

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.

Upvotes: 3

mwolfe02
mwolfe02

Reputation: 24207

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.

Upvotes: 6

Related Questions