Add Query to VBA (Runtime Error 3067)
I am pulling seven values from unbound text boxes on a form into variables. Five variables are a string type, two are two. Then I use sql to add data to the table using a where clause and a global variable that contains a foreign key that I used from another table since I didn't know how to use openargs with a browser ...
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 about error # 3067: "Query input must contain at least one table or query."
I have no idea what I am doing. I tried using debug.print but didn't see anything right away. Then again I work on this database all day, so I might forget something very simple.
PS I also tried replacing the variables with Me.CAP_ROW_N (textbox names), but not dice.
source to share
It is not clear what you are trying to do here, but the operator INSERT INTO ... VALUES ()
does not accept the offer WHERE
. Error 3067: "The query input must contain at least one table or query." You are probably seeing this error because you have included the WHERE clause, but you are not selecting existing values from the 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 questions here as well. I just listed them and gave you additional guidance from Google:
- You should use the
.Execute
DAO method insteadDoCmd.RunSQL
because it allows for better error handling, especially when used with an optiondbFailOnError
. - You will end up with the problem of using single quotes on unbound inputs. For example,
WHERE LastName = 'O'Malley'
- You seem 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 receive quotation marks.
source to share
Do not assign field names to table names in the field list.
SentenceA is WHERE
not related to an expression INSERT ... VALUES
; get rid of it.
This is a smaller scale example of the template I think you want:
appendIt = "INSERT INTO CAPITAL " & _ "([CAP_ROW_N], [CAP_ROW_R]) " & _ "VALUES ('" & rowN & "','" & rowR & "');"
However, I suggest you solve this problem with a parameter request.
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 textbox values for the parameter values directly --- instead of declaring variables to store the textbox values.
Also, notice one of the advantages of parameter queries: you don't have to worry about delimiters for values: quotes for text; or #
for dates.
source to share