Link to home
Start Free TrialLog in
Avatar of Jass Saini
Jass Saini

asked on

Updating certain records

Hello,

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.
Avatar of James Bailey
James Bailey

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
Avatar of Scott McDaniel (EE MVE )
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).
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?
ASKER CERTIFIED SOLUTION
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
James,
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.
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.