Append New Record Query Without Using "SetWarnings"

Does the table that you are trying to link carries a Primary Key ? its essential.

When the backend is SQL server (where Identity is their name for Autonumber) you need to use the extra dbSeeChanges constant.
In your case it would go here:

CurrentDb.QueryDefs("PowerlineF_PowerlineParticipationAppend").Execute dbFailOnError+dbSeeChanges

Select allOpen in new window

Anders, I got an error:  Too Few Parameters.  Expected 2

Usually there is a problem with your query ...can you try it by putting static values instead of dynamic...just take the SQL of the Query and substitute the Values with static one that match the fields datatype..e.g VALUES ('1','04-23-2019,'Easter')...

If you can run the action query manually (not using VBA code) and it runs o.k., then your code should run it ok too as long as you do what Anders suggested.  No amount of VBA code will fix a bad query that won't run manually.  Get a query that runs manually first, then run it with code.

Also, just checking.  This isn't a pass-thru query you're trying to run is it? (built with T-SQL and has a connection string to the database)

It runs fine manually.  In fact, the original code with the SetWarnings False runs fine.  See original post that I modified to show exact code.  The "Old way" executes nicely.

Then probably you have some Values that need evaluation before passed to the query...just prepare the Values before presenting them to the query and it would run just fine.
e.g.
 Instead of VALUES (Form!CtlSomething....)
Dim value1st as Integer
value1st   = Form!CtlSomething
VALUES (value1st ,....)
Just give it a try ...and if it works ...you can try to implement Eval into the Values
VALUES(Eval(Form!CtlSomething),....)

There is another difference between the 2 methods. Docmd.OpenQuery is the same as running it manually, through access, whereas CurrentDB.Execute is a more direct line to the database engine (Yet not the same as a passthrough).

It is a bit difficult to explain, but when using the query manually or through the use of Docmd, then access will pre-parse the query, and resolve such references as Forms!myForm!Control . This doesn't happen with the Execute syntax as it cannot understand form references.

In that case, you can either continue to use the docmd syntax, or build the query string entirely in VBA, and execute that.

That makes sense...   my query has criteria that refer to combo boxes on a form.  Is that a limitation of the CurrentDB.Execute method?

in addition to Anders comment, if your query contains parameters, you can use syntax similar to:

Dim qdf as dao.querydef
set qdf = currentdb.querdefs("YourQueryName")
qdf.Parameters("ParamName1") = me.someValue
qdf.Parameters("NextParamName") = me.SomeOtherValue
qdf.Execute dbfailonError + dbSeeChanges
set qdf = nothing

Select allOpen in new window

I never use the docmd.OpenQuery method because of the SetWarnings issue.  I cannot tell you how many databases I've had to fix where the users used:

docmd.SetWarnings False

Select allOpen in new window

and either did not get back to:

docmd.SetWarnings True

Select allOpen in new window

or placed that line of code in the wrong spot in their code, so that it did not fire on certain circumstances.   Additionally, when you SetWarnings = False, you not only obscure the warnings that you are about to update, append, or delete X records, you also obscure warnings that warn you that you are violating some rule and that the query didn't execute at all.

If you insist on using docmd.SetWarnings, then I would always use a construct that looks something like:

Private Sub YourProcedure

    'Dim variables here

    On Error goto ProcError

    'some code here

    docmd.setwarnings false

    'some more code here


ProcExit:
    on error resume next
    'close and set objects to nothing
    docmd.setwarnings True
    Exit Sub

ProcError:
    'Handle your error here
    Resume ProcExit

End Sub

Select allOpen in new window

This construct, placing the docmd.setwarnings = True in ProcExit and ensuring you resume ProcExit in your Error handler will ensure that that command always gets executed before you exit a procedure.

@Dale:  If there was a "Like" button, I'd give you one....

@Dale:  Thanks for the information!  I don't want to use docmd.setwarnings, but I haven't been able to find a way around it (as per my previous post).   If you have any additional information relevant to my situation where there are form-based criteria in the append query, I'd love to try it.  Otherwise, I'll try out your ProcExit within the DoCmd.SetWarnings method.

You could always try the Passthrough query solution and get done with it.

@Jason,

Modified from my sample code above:

Dim qdf as dao.querydef
set qdf = currentdb.querdefs("YourQueryName")
qdf.Parameters("SomeDate") = Forms!yourFormName.txt_SomeDate
qdf.Parameters("PONum") = Forms!yourFormName.txt_PONumber
qdf.Execute dbfailonError + dbSeeChanges
set qdf = nothing

Select allOpen in new window

I usually give the parameters meaningful names and actually define them:

PARAMETERS [SomeDate] DateTime, [PONum] Text (15);
INSERT INTO tbl_Numbers ( TestDate, PONumber )
Values ([SomeDate], [PONum])

As previously suggested, have you thought about building your SQL string in code - complete with the criteria pulled in from the form controls - and then putting it into your querydef and running with CurrentDB.Execute, or just running the SQL string with Currendb.Execute and be done with it.  Best of both worlds....

Sorry, guys, I'm self-taught on this and easily get lost in the lingo.  I also get lost easily when attempting to apply an abstract concept to my specific scenerio especially when I'm not sure what's VBA programming code and what values are supposed to be changed to accommodate my circumstance.

@Dale, by parameters so you mean the criteria behind my append query?  Here's my query:  In the PowerlineF form, I'm selecting a Facility through [Forms]![PowerlineF]![cboFacilityID] criteria and selecting a Powerline through [Forms]![PowerlineF]![txtPowerlineUID] criteria.  This append query that is used for the insert is named "PowerlineF_PowerlineParticipationAppend" and activates nicely through the incorrect method discussed previously.  I do believe if I can grasp this concept, I could apply it to other append queries I have.

@Mark, I don't understand what you mean by "SQL string in code".  I'm all for using the most dependable and efficient method, but I have limited knowlege and I carry 100% support of this system so I need to fully understand how to implement.  My named query method with the SetWarnings is VERY simple, but I'd like to learn from your experience and do this the right way.  I'm interested in learning more about how you would specifically code this.

Jason:  The Querydef database objects (the ones you see in the Queries pane of the Navigation Pane), are just graphical "screens" for putting together SQL strings to run.  If you put your query in Design View as you have shown in your screenshot, what you are seeing is the Builder pane of the querydef.  If, however, you put the querydef in the SQL View, then you will see the actual SQL string that the builder pane built.  It is the SQL that is actually executed.  If you have the SQL string, you don't need the querydef object unless you added parameters to it or an ODBC connection string.

However, the Builder pane is limited in what it can "build" as far as creating SQL strings.  Sometimes you need to add an element to your string that the builder just can't do, such as unionizing two queries together:

SELECT [Field1] FROM [Query1]
UNION
SELECT [Field1] FROM [Query2]

 - that can only be built in SQL View.

You can build an SQL query string in code, complete with anything you need to add, and, if the querydef object will accept it, you can set the querydef .SQL property to the SQL string, or just execute the string - don't need the querydef.  For example:

Dim strSQL as string
strSQL="SELECT * FROM [MyTableName] WHERE [Field1]='" & forms("myForm").cboMyCombobox & "'"

Where [Field1] is a text field (string).  If the value in the combobox is "xyz", then when the code runs, you have:
strSQL="SELECT * FROM [MyTableName] WHERE [Field1]='xyz'"

You can then put it into the querydef:

CurrentDB.querydefs("Myquerydefname").SQL = strSQL

and then run the query:

CurrentDB.Execute "Myquerydefname"

or just execute the SQL string.

CurrentDB.Execute strSQL

Hope that helps...

Your issue is part of a bigger issue with Access.  Access has a "low/no code" app building capability that a lot of people first learn and try to use because, well.... that's how every Access how-to book is built - learn the built-in wizards, builder and such so you don't have to code.  

BUT!  These wizards, builders, and such have limited capability when it comes to doing everything you want, so a lot of app builders start getting an itch to start using VBA to be able to do what the built-in stuff can't.  

I didn't start using VBA until I switched from Access to Visual Studio and VB3 early in my career.  When .Net came out, it had some issues with it so I decided to work with Access for awhile until they fixed .Net.  When I got into the VBA IDE, I immediately recognized that it looked a lot like Visual Studio!  So, I thought.... I wonder if I can do here some of the things I did in Visual Studio?  and guess what? I COULD!  That opened up a whole new world to me and I've been able to make a go of VBA development as a career ever since.

If you want an idea of what you can do with VBA that the built-in stuff can't, watch my youtube video for some ideas:https://www.youtube.com/watch?v=-A8fu--aa6k

Wow!  Great points, Mark.  Very helpful!  I'll try to apply some of your instructions to my coding.

I got a compile error when trying to apply your directions.  Please see code below.  Also, how would I change this for integer fields.  Both of my ID fields are integer.

Private Sub txtFacilityID_AfterUpdate()

strSQL = "SELECT * FROM [MyTableName] WHERE [FacilityID]='" & Forms("FacilityProductionDetailedF").txtFacilityID & "'" and [ProjectID] ='" & Forms("FacilityProductionDetailedF").txtProjectID & "'"
CurrentDb.QueryDefs("FacilityProductionDetailedParticipationAppend").sql = strSQL
CurrentDb.Execute "FacilityProductionDetailedParticipationAppend"

'DoCmd.SetWarnings False
'DoCmd.OpenQuery ("FacilityProductionDetailedParticipationAppend")
'DoCmd.SetWarnings True
txtFacilityID = Null
Requery


End Sub

Select allOpen in new window

ok.  First "MyTableName" means "put-your-table-name-here"  Do you actually have a table named "[MyTableName]" in your db?  If not, you get the picture.
Also, the sample query SQL I gave was for example.  I don't know what the SQL is in your query.  The sample SQL I gave is a SELECT query (displays records) that pulls all fields from what ever table name you put in place of "MyTableName".  What does the actual SQL in your querydef look like?  I would need it if you need YOUR SQL shown how to build it as a string SQL.

Tip:  Get use to seeing contributors post sample SQL with "fillers" that take the place of what you would actually use because they don't know what you have.  You are expected to recognize the need for substitution, or include your SQL and other data so the others know what you have and can use it.

Since you are filtering on number fields, you would not put single quote characters at each end of your filter insert.  Instead you would:

WHERE [FacilityID] = " & Forms("FacilityProductionDetailedF").txtFacilityID & " and [ProjectID] = " & Forms("FacilityProductionDetailedF").txtProjectID 

Select allOpen in new window

The single quotes at each end in the sample are if you are using a string to filter a text field.  If it was a date or time data type field, you would use "#" signs as in [MyDateField]=#" & forms("myForm").txtTextbox & "#"

Make sure you don't replace you original SQL in your querydef with a sample SQL unless you have a copy of the original SQL.  Otherwise, you'll be re-creating your query from scratch.

Once you get the hang of it, you'll wonder how you ever got by without it!

Sorry about that.  I didn't even notice MyTableName when using the code.  Thanks for the help.

My query is an "Append" query.  So should I replace the word SELECT with the word APPEND?

I'll repost my actual query and the SQL to help avoid any confusion.

INSERT INTO FacilityProjectParticipationT ( FacilityID, FacilityProjectID )
SELECT FacilityT.FacilityID, FacilityProjectT.ProjectID
FROM FacilityT, FacilityProjectT
WHERE (((FacilityT.FacilityID)=[Forms]![FacilityProductionDetailedF]![txtFacilityID]) AND ((FacilityProjectT.ProjectID)=[Forms]![FacilityProductionDetailedF]![txtProjectID]));

Select allOpen in new window

I get Run-time error '3075'

Syntax error (missing operator) in query expression '*WHERE [FacilityID] = 2221 and [ProjectID] = 12410'.

Row-3 was highlighted.
This is my code:

Private Sub btnADD_Click()
strSQL = "SELECT * WHERE [FacilityID] = " & Forms("FacilityProductionDetailedF").txtFacilityID & " and [ProjectID] = " & Forms("FacilityProductionDetailedF").txtProjectID
CurrentDb.QueryDefs("FacilityProductionDetailedParticipationAppend").sql = strSQL
CurrentDb.Execute "FacilityProductionDetailedParticipationAppend"

'DoCmd.SetWarnings False
'DoCmd.OpenQuery ("FacilityProductionDetailedParticipationAppend")
'DoCmd.SetWarnings True
txtFacilityID = Null
Requery

End Sub

Select allOpen in new window

Your SQL Query is missing the FROM clause:  Its:  "SELECT * FROM [Tablename] WHERE [Field1]=123"

Since you supplied your code, I'll build your SQL string for you:

strSQL = "INSERT INTO FacilityProjectParticipationT ( FacilityID, FacilityProjectID )" _
& vbCrLf & "SELECT FacilityT.FacilityID, FacilityProjectT.ProjectID" _
& vbCrLf & "FROM FacilityT, FacilityProjectT" _
& vbCrLf & "WHERE (  ((FacilityT.FacilityID)=" & [Forms]![FacilityProductionDetailedF]![txtFacilityID] & ") AND ((FacilityProjectT.ProjectID)=" & [Forms]![FacilityProductionDetailedF]![txtProjectID] & "));"

Select allOpen in new window

See if that works...

I noticed that you don't have any links between your two tables, resulting in a "cartesian" query.  Is there any field in one table that you need to match to in the other table?

By the way, Jason, what are you using to learn Access programming and database building?  An Access book? An online course?  Winging it?....

Thanks!  There's nothing between those two queries that's related.  It was the only way I knew to make it work.  It seemed to work well, so I just went with it.

I tried the code you provided.  I didn't get an error, but nothing seemed to have executed.  I checked the table it was supposed to append to and there were no new records.

I learned a basic understanding of Access through AccessLearningZone.com, but I've gotten most of my program built by researching discussion boards.

Here's another tip:  Since you are putting your SQL in a query def object, you can open the querydef and just run the SELECT part to see if you are pulling any records to put in your append table.

With you querydef in Design View, click the table grid button to the left of the red exclamation point at the left-end of the Design ribbon.  That will ignore the INSERT clause and just run the SELECT part of the query.  Do you get any records?

I just thought of something....  If your code ran the DoCmd.SetWarnings False, but threw an error before executing the DoCmd.SetWarning True, then you've turned off warnings and not turned 'em back on.  Add a
DoCmd.SetWarnings True
at the beginning of your code to make sure you have them on.  It won't hurt anything...

Thanks!  I'll try the SELECT tomorrow!

Side note:

strSQL = "INSERT INTO FacilityProjectParticipationT ( FacilityID, FacilityProjectID )" _
& vbCrLf & "SELECT FacilityT.FacilityID, FacilityProjectT.ProjectID" _
& vbCrLf & "FROM FacilityT, FacilityProjectT" _
& vbCrLf & "WHERE (  ((FacilityT.FacilityID)=" & [Forms]![FacilityProductionDetailedF]![txtFacilityID] & ") AND ((FacilityProjectT.ProjectID)=" & [Forms]![FacilityProductionDetailedF]![txtProjectID] & "));"

I do not recommend using the _ (underscore) character within your multiline instructions, for the simple reason that one little mistake within you strings concatenation will toggle the whole instruction (4 lines in this specific case, this is long already) as invalid (it will show up in red in the VBE editor).
And since queries tend to becomes long quickly, this kind of "facility" becomes a nuisance (good luck finding that missing or duplicated quote within soo many lines of invalid text).

Instead, I recommend concatenating with several instructions:

strSQL = vbNullString
strSQL = strSQL & "INSERT INTO FacilityProjectParticipationT ( FacilityID, FacilityProjectID )" & vbcrlf
strSQL = strSQL &  "SELECT FacilityT.FacilityID, FacilityProjectT.ProjectID" & vbcrlf
strSQL = strSQL &  "FROM FacilityT, FacilityProjectT" & vbcrlf
strSQL = strSQL &  "WHERE (  ((FacilityT.FacilityID)=" & [Forms]![FacilityProductionDetailedF]![txtFacilityID] & ") AND ((FacilityProjectT.ProjectID)=" & [Forms]![FacilityProductionDetailedF]![txtProjectID] & "));"

Select allOpen in new window

If there is an invalid instruction somewhere, only one line will show up as invalid (it is easyer to fix one small line, than one spanning over a bunch of lines).

For those of you who might not know how compile errors work in underscored (continued) code lines, here's an example.  If there is a compile error, Access will highlight the first item in the string that violates the compile.  Can you find the offending typo below?
No?  It's the first double-quote character in the second line, which causes the FROM to be interpreted not as a string, but code.

Now if the whole SQL string compiles, but the whole SQL string fails when run, then that's a different issue altogether.  It doesn't matter what string building technique you use, you're still going to have to use the clues in the error message to find the offending part.