Updating certain records


I am kinda of at square one again.  I am trying to update a field (remarks) for only a few records.  The records that are in my query.  My query is not updatable.  In my form I am using that query in the main form and a another query based off the whole table.  I am only pulling up the records that are in my main form query by linking them with the Parent/Child link.  

The problem is when I update a field in my subform..it updates all the records in my table as my subform is based off the table.  Please Help!!!  Either I need to figure out how to make my query updatable or figure out how to make my subform only update those records and not the entire table.
Jass SainiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

James BaileyProject PlannerCommented:
Try the UPDATE statement in SQL

Dim sql as String
sql = "UPDATE"
sql= sql & vbCrLf & The Name Of the Table to Update"
sql = sql & vbCrLf & "SET"
sql= sql & vbCrLf FieldName = "' Replace(NewUpdatedValue,"'","''") & "'"
sql = sql & vbCrLf "WHERE"
sql = sql & vbCrLf & "City = 'Evansville'
sql = sql & vbCrLf & "AND"
sql = sql & vbCrLf "State = 'CA'"
CurrentProject.Connection.Execute sql

Try that on for size
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'd suggest you fix the Subform, instead of trying to work around the issue. First fix your query to be updateable. There are many reasons a query is not updateable, but if you could post the SQL of that query perhaps we could find the troubles.

Also, a typical bound subform should not update all records when you modify a single record. If it is, then either (a) you've got something wrong with the subform or (b) you've got some serious issues with your database, or with your installation of Windows/Office.

If you're doing something other than using bound forms, then you'll need to give us much more detail about how you're handling the process.

If you MUST do this, then a much simpler way to write that query is this:

Dim sql As String
sql = "UPDATE YourTable SET YourField='" & Me.SomeTextbox & "' WHERE Some IDField=" & Me.YourIDField
CurrentProject.Connection.Execute sql

You don't need carriage returns or linefeeds in SQL statements. They are for human readability only, and could potentially cause issues with your sql (since including them in your code would insert those ASCII characters into the string).
James BaileyProject PlannerCommented:
a much simpler way to write that query is this.

In almost every case, in any situation, there is a simpler way to do the job at hand.

However,  the simplest way, may produce readability issues when it comes time to support or enhance the application
And concerning your assertion that
You don't need carriage returns or linefeeds in SQL statements,

Well you are obviously correct,  and following the same line of reasoning,  Moby Dickcould have been written been written without the same.  While we're at it, Do we really need those pesky commas and rules of Grammer.
Where's the harm in ending a sentence with a preposition.  

I prefer the Road Less Traveled. How might a Carriage return cause issue s within an SQL statement?
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
However,  the simplest way, may produce readability issues when it comes time to support or enhance the application
For me, your code is far less readable than mine, but that's personal preference - so your point is somewhat moot.

Moby Dick is a novel, not an SQL statement, so I don't really think the analogy is valid. A novel is intended to be properly formatted for human readability. A SQL statement is intended to be formatted properly for the parsing engine first, and for human readability second. And, as previously mentioned, your concept of "properly formatted for human readability" may be quite different from my own, or from others.

How might a Carriage return cause issue s within an SQL statement
I'll repeat what I wrote earlier:

"since including them in your code would insert those ASCII characters into the string"

There is simply no good reason to introduce extraneous characters to a SQL string. While you and I may not have issues, other users who might be using different language packs on their machine very well could.

Finally, one of the goals here at EE is to provide users with the generally accepted method for creating their code. I'm not implying that mine is more generally accepted, but I doubt you'd be able to find many professional developers (or power users) who would prefer your syntax over mine. I could be wrong about that, of course, but a quick Google search seems to prove that out to a great degree.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
If you like your SQL formatted, leave it in a querydef that you only work with in SQL View.  As long as you don't switch to QBE view, Access won't reformat it on you.  The added advantage is that Access won't have to calculate a new execution plan every time you run the query.
James BaileyProject PlannerCommented:
I knew I'd be in the minority.  I'm on the Road Less Traveled. I'm most curious, though,  to learn if any of this was helpful to the author of the original question.

I'm not crusading to persuade folks to agree with me. I imagine none of us is out to persuade others to agree with our concept of what is best. I do things the way I do them,  because my way works the way my brain works.  I yield too your concept of best for the sake of this discussion.  My concept is only best for me, and I knew that going in.

I do, however, feel strongly that Access execution plans have such a minor impact on performance they can be pretty much ignored.  I do believe that the best way to deal with dynamic SQL is in code. This discussion has been invigorating ... well, to me, i mean.  THANK YOU.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.