Update existing values incrementally w/UPDATE SQL

  • Thread starter Thread starter Robert5833
  • Start date Start date
R

Robert5833

Good day all;

Access 2007, 1 Gb RAM

I have looked for but cannot find an example of an UPDATE SQL string for a
VBA procedure with a WHERE clause. If I can’t use a WHERE clause in an UPDATE
SQL then I’ll have to devise a different solution.

Otherwise, here is what I’m trying to do:

I have two tables; one to hold unique items, and one to hold records that
relate to the unique item.

Table
1) tblMotor (unique item; no duplicates; Primary Key fldMotorID)

Fields
fldMotorID (autonumber; no duplicates)
fldMotorName (text; yes duplicates)
fldMotorHours (number; double, represents current accumulated motor hours,
yes duplicates)

Table
2) tblPart (related to tblMotor thru foreign key; duplicates allowed;
Primary Key fldPartID)

Fields
fldPartID (autonumber; no duplicates)
fldPartName (text; yes duplicates)
fldPartHours (number; double; represents current accumulated hours, yes
duplicates)

Forms (one only)
frmMotorStatus; Record Source, tblMotor
Text box; tboMotorID, bound on fldMotorID
Text box; tboMotorHours, bound on fldMotorHours
Unbound text box; tboAddMotorHours, a data entry point for “new†current
Motor Hours

When I enter a new current motor hour value on the form I want the After
Update event to execute the VBA SQL to subtract the difference between the
tboAddMotorHours and tboMotorHours, and add (presumably "UPDATE") the result
as an incremental to the existing value in all of the related records in
tblPart, using the tblMotorID as the unique record identifier.

The reason for not simply making current part hours the same as the current
motor hours is that each record in tblPart can be on a different hour level
when part is installed or replaced. Once installed, part hours are accrued on
the basis of the motor accumulated time.

I hope this description is clear and accurate enough; and I don’t have any
code written for this piece of my puzzle yet.

Can this even be done with a VBA SQL and UPDATE with WHERE clause in the
string?

As always, I appreciate any help or suggestions on building this SQL, or a
better approach than what I’ve dreamt up…

Thank you in advance,
RL
 
Yes, UPDATE statements can have WHERE clauses. I'm afraid I don't follow
your descriptioin well enough to be able to suggest the exact SQL you need,
but an example that reduces reduces the UnitPrice for all non-discontinued
products supplied by the company contained in a text box txtCompanyName by 5
percent (where the Products and Suppliers tables have a many-to-one
relationship) would be:

CurrentDb.Execute, "UPDATE Suppliers INNER JOIN Products " & _
"ON Suppliers.SupplierID = Products.SupplierID " & _
"SET UnitPrice = UnitPrice * .95 " & _
"WHERE CompanyName = '" & Me!txtCompanyName & "' " & _
"AND Discontinued = False", dbFailOnError
 
Hi Doug,
Thank you for the quick reply. I've read lots of your posts, and been to
your referenced site(s) many times. I'm grateful for the support that you and
other MVP's have offered up to folks like me.

I believe I can make a variation of the SQL example you offered work for my
needs, as long as I can use the value in a control to replace the hard coded
value increment shown. Functionally, this is exactly what I was looking for.
I may use a hidden text box on the form to do the math, and then reference it
in the string.

I'll try something out with the example and reply again as to the outcome.
(Chances are I won't be able to get there on my own :-)

Again, many thanks for your post here, and the many other posts from which
I've learned valuable techniques and time saving code.

Best regards,
Robert L.
 
Hi Doug,

Here's what I've got so far...and I can't seem to break the Syntax Error /
Missing Operator...

Ugh! I've read so many posts on that, and it's almost always the use of
quotation marks. But it's got me beat again. I also got a little confused on
the use of double vs. single quotes in the sample code you provided. As much
as I've read and studied on that point, I still don't get it. Maybe this
exercise will help me learn...finally.

Private Sub cmdLoadValues_Click()
Dim tboDoMath As Control
Dim AddNewHR As Integer
CurrentDb.Execute "UPDATE tblPart INNER JOIN tblMotor " & _
"ON tblPart.fldMotorID = tblMotor.fldMotorID " & _
"SET AddNewHR = AddNewHR " & _
"Form!frmMotor!tboDoMath " & _
"WHERE fldMotorID = Me!tboMotorID ", dbFailOnError

The Run-time error dialog box (err. #3075) says: Syntax error (missing
operator) in query expression ‘AddNewHR Form!frmMotor!tboDoMath’.

Honestly, I have very little knowledge of, and the proper use of the Dim
statements, so in this case I don’t know whether they are required or not.

In break mode, the Dim AddNewHR is “0â€, but presumably that is due to the
fact the SQL string is broken.

I’ve looked for more examples where similar code to the sample you provided
is shown so I could “reverse engineer†the SET clause, but haven’t found
anything thus far. And also, if you would comment on the need for, or not, of
a space (_) following some of the quotes? I did find that as I worked through
the string, where first I had several pieces of the string shown in the error
dialog box, moving some of the quotes one space cleared some of them up.

Would you be so kind as to expound on that piece? Specifically; where I have
defined AddNewHR, would that have to be declared as a variable, or is it a
reference to a control (possibly a control that relates to the sample code
you provided)?

I thank you again for your time and assistance; and for your patience...as I
openly admit to you that I am yet another one of those poor folks struggling
to learn and use the basic constructs of VBA and SQL syntax.

Best regards,
Robert
 
What is AddNewHR Form!frmMotor!tboDoMath supposed to be?

You've declared AddNewHR as an Integer variable, yet you're giving it a
value.

If what you're trying to do is assign the value that's in tboDoMath to the
field in the table, it's

CurrentDb.Execute "UPDATE tblPart INNER JOIN tblMotor " & _
"ON tblPart.fldMotorID = tblMotor.fldMotorID " & _
"SET AddNewHR = " & Form!frmMotor!tboDoMath & " " & _
"WHERE fldMotorID = " & Me!tboMotorID , dbFailOnError

References to controls on forms have to be outside of the quotes. However,
if they're a text value, you have to put quote delimiters around them. For
instance, if AddNewHR was a text field, you'd use

"SET AddNewHR = '" & Form!frmMotor!tboDoMath & "' " & _

where, exagerated for clarity, that's


"SET AddNewHR = ' " & Form!frmMotor!tboDoMath & " ' " & _

_ isn't a space: it's the underscore character (preceded by a space). That's
the line continuation character in VBA. In order to avoid problems with
word-wrap in these newsgroup posts, I try to limit how wide each line of
code is, and use the line continuation character.
 
Hi Robert,

Here's a few basics I go by with the Dim and double vs single quotes that
may help out
Honestly, I have very little knowledge of, and the proper use of the Dim
statements, so in this case I don’t know whether they are required or not.
Click to expand...

I think it would be safe to say, use the Dim statement any time you are
declaring a variable inside a procedure. In a little over two years working
with vba on and off, I don't think I've really strayed from this rule. I
suppose an exception would be arrays, but they are not generally something
beginning programmers play around with, so it shouldn't be an issue here.

Dim basically tells vba that you want to initiate a new variable. As a
general rule, the only time you wouldn't use dim inside a procedure is if the
variable has already been passed but an argument. For example:

Private Sub MyProcedure(Variable As Integer)
'Variable is already declared at this point, so there's
'no use in using the Dim statement (it will only throw an error)
'Dim any other variables you are going to use in this procedure
Dim VariableA As Integer
Dim VariableB As Integer
VariableA = Variable + VariableB
End Sub


As far as quotes go, this was one of the trickiest (yet extremely important)
concepts for me when I first started. I gather that you've already read up a
lot on this, but I'll try a simple example anyway. Keep in mind that there
are many ways to code quotes in vba, but the only one I know (or have ever
needed), is using doubles and singles, as in Dougs example. When I learned,
it seemed far less confusing than other methods.

Consider vba's interpretation of a string:
You have: strVar = "Goodbye cruel world!"
VBA sees: Goodbye cruel world!
(notice the lack of quotes in the 'vba' version)

When VBA sees a double quote, it waits for another double quote to stop
calculating the string. So, if you have anything other than a double quote,
it goes into the string.

You have: strVar = "this is a string ' with a quotation inside it"
VBA sees: this is a string ' with a quotation inside it

Notice that vba did not close the string when it hit the single quote,
because it waits for that double quote to close it. So you could actually
enter as many single quotes as you want in a string:

You have: strVar = "string'string'string'string"
VBA sees: string'string'string'string

So, knowing that vba constructs a string only from opening/closing double
quotes, you now have a way to enter single quotes in a string.

NOW, when an SQL is processed (I use an SQL example because it is the most
common), it reads the 'VBA sees' part of your string. Any single quotes
inside your 'VBA sees' part is processed as... well, quotations.

You have: "This string 'insert your string here' is a string with quotes"
VBA sees: This string 'insert your string here' is a string with quotes

Based on the fact that vba processes single quotes in a string, vba actually
sees 'insert your string here' as a separate string, except its inside the
string you supply.

Confusing?? Absolutely. One last step... consider an SQL statement where
you need to compare a string field to a string value. SQL needs to know that
what you are telling it to find is a string (it has to be in quotes the way
vba processes it). Thats the key to using the single quotes. Consider the
following example where statement:

strWhere = "MyStringData = ' " & Me.txtString & " ' "

(I've added a space between doubles & singles for ease of reading). Lets
pretend that Me.txtString is a control on your form, or the string datatype.
You cant put Me.txtString inside the double quotes, or VBA just reads it as a
string, instead of getting the value for you. See below

You have "MyStringData = Me.txtString"
VBA Sees: MyStringData = Me.txtString

Now, lets pretend that the value of your control txtString is equal to
"YAYYY!!"

You have: "MyStringData = ' " & Me.txtString & " ' "
VBA sees: MyStringData = ' YAYYY!! '

So, if you pass this through an SQL statement, now the SQL is legitimately
looking for a string data that is equal to YAYY!!

Got it? Maybe after you read it a few more times... this is never easy lol.

If you think you have it, try to apply it to Dougs line (pretending that
txtCompanyName is GeneralMotors):

Doug Says: "WHERE CompanyName = '" & Me!txtCompanyName & "' "
VBA sees: WHERE CompanyName = 'GeneralMotors'

Hopefully that helps some.



--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Sorry, the second sentence should have been "You've declared AddNewHR as an
Integer variable, yet you're not giving it a value."
 
Hello Jack,
Thank you so much for that explanation! That does help a lot! I've read and
read and read on the subject, in the many posts in this group, and from the
MS site, and in the book; Access 2007 VBA Programmer's Reference. But still I
struggle...

Your description is very good, and I'll print it and re-read it until it
sinks in.

Thanks again!

Robert
 
Glad to help!

I did notice a typo in my post that could lead to some confusion, so i'll
verify here



This line:
should read

pretend that Me.txtString is a control on your form, OF the string datatype.

Of, not or (they put those little key things waaay to close together)

happy coding
--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery
 
Hi Doug,

Thanks for staying with me on this.

Please accept my advance apology for this being such a long post; but I
thought it might help if I provided clear examples of what I’m doing, and the
results I’m seeing. Although it probably doesn’t qualify as good
troubleshooting since I’m just taking a stab in the dark, maybe it will help.
(And hopefully I don't get banned from the discussion group for going on
about this ad nauseam.)

I inserted your original code example, and pasted the results below. I
should point out too that the “AddNewHR†reference I provided you was a
blunder on my part. That reference was what I thought would be a declared
variable in the expression not the name of a control. (And I've commented out
the Dim declaration.) In the second set of code, I put the name of the
control instead but got the same result. Then I tried a couple of variations
of the syntax, changing the SET statement reference of the [Form] to the Me!
and had different results there as well.

And, I have since realized that the names of the controls I gave initially
weren’t correct, so in the last pasted procedure, I’ve referenced those that
are used to do the factoring. (Sorry for not knowing enough about my own
questions…)

Also, in my second reply to your post I asked about the spaces after a value
in the expression, but I misstated the question and also posted the (_). What
I was referring to was the space preceding the quotation mark after a named
reference; e.g., tblMotor “ vs. tblMotorâ€. I noticed that without the space
preceding the quotation mark, each piece of the string where the space was
missing was listed in the error report? Do I dare ask that you humor me
enough to expound a bit on what function or effect that space has on the
expression?.

Original code:
Yields; Error 2465, “db can't find field 'frmMotor' referred to in your
expressionâ€.
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET AddNewHR = " & Form!frmMotor!tboDoMath & " " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError

Changing the code to include the actual control being referenced (in SET
statement):
Yields; Error 2465, “db can't find field 'frmMotor' referred to in your
expressionâ€.
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET fldCurrentHour = " & Form!frmMotor!tboDoMath & " "
& _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError

Changing the code to include the “Me†reference to the control (in SET
statement):
Yields; Error 3079, “the specified field ‘fldCurrentHour’ could refer to
more than one table listed in the FROM clause of your SQL statementâ€.
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET fldCurrentHour = " & Me!tboDoMath & " " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError

Changing the code to include the “Me†reference to the control (in SET
statement) and adding the comma (“, “) in the third line:
Yields; Error 3144, “Syntax error in UPDATE statementâ€.
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET fldCurrentHour = " & Me!tboDoMath & ", " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError

To clarify (I hope) what I’m attempting to do with this procedure:

For a motor part in tblPart, which is installed (related) to a motor
assembly in tblMotor linked on the Primary Key of tblMotor, and with each
part in tblPart possibly having different hours of operation (fldCurrentHour)
than the motor itself, following an adjustment (update) of the current motor
hours of operation (fldCurrentHour), add the difference between “was†and
“is†current operating hours to all the related records in tblPart
(tblPart.fldCurrentHour).

Note: both the tblMotor, and tblPart have a field titled; fldCurrentHour,
and both are numbers; double.

With the modified statement below, dropping the JOIN and WHERE clauses I
don’t receive the 3144 syntax error, and adjusting the math equation for the
names of the actual controls I need to do the math, the procedure does update
ALL the fields in tblPart, but the value the table’s fields update to is the
value in the tboAddNewCurntHR control, rather than the sum of
tboAddNewCurntHR and tboCurrentHour (which is my goal).

CurrentDb.Execute "UPDATE tblPart " & _
"SET fldCurrentHour = " & (Me!tboCurrentHour + (Me!tboAddNewCurntHR -
Me.tboCurrentHour)), dbFailOnError

Note: in break mode, the SET portion of the string is returning the proper
values for the named controls in the math equation and for the motor ID in
the WHERE clause.

So, in my lack of understanding how this should flow, here is the code that
*looks* like what I need, but throws the 3144 syntax error:

CurrentDb.Execute "UPDATE tblPart " & _
"ON tblPart.fldAircraftID = tblAircraft.fldAircraftID " & _
"SET fldCurrentHour = " & (Me!tboCurrentHour + (Me!tboAddNewCurntHR -
Me.tboCurrentHour)) & ", " & _
"WHERE fldAircraftID = " & Me!tboAircraftID, dbFailOnError

Note: the line continuation or break above is correct in the code, not in my
Word draft…

The equation and the procedure should do the following:

Subtract the value of the form’s unbound text box named tboAddNewCurntHR
from the value in the form’s bound text box named tboCurrentHour (bound to
tblMotor), and add the result to all of the related fields in tblPart, field
name fldCurrentHour that are keyed to tblMotor.fldMotorID.

I’ve scoured the discussion posts yet again, but I can’t find anything that
resembles what it is I think I need, and I’ve tried every variation and
combination of the above statements. My brain is mush about now…

Thank you in advance for your continued support and patience.

Best regards,
Robert
 
Robert5833 said:
Original code:
Yields; Error 2465, "db can't find field 'frmMotor' referred to in your
expression".
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET AddNewHR = " & Form!frmMotor!tboDoMath & " " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError
Click to expand...

While it's possible to join a table to itself, when you do you must provide
aliases to disambiguate which table is which

CurrentDb.Execute "UPDATE tblMotor AS A INNER JOIN tblMotor AS A " & _
"ON A.fldMotorID = A.fldMotorID " & _
"SET A.AddNewHR = " & Forms!frmMotor!tboDoMath & " " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError

Note, too that it's Forms!frmMotor!tboDoMath, not Form!frmMotor!tboDoMath

Changing the code to include the actual control being referenced (in SET
statement):
Yields; Error 2465, "db can't find field 'frmMotor' referred to in your
expression".
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET fldCurrentHour = " & Form!frmMotor!tboDoMath & " "
& _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError
Click to expand...

Other than the fact that you change AddNewHR to fldCurrentHour in this
statement, it seems identical to the previous one (and therefore has the
same errors)
Changing the code to include the "Me" reference to the control (in SET
statement):
Yields; Error 3079, "the specified field 'fldCurrentHour' could refer to
more than one table listed in the FROM clause of your SQL statement".
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET fldCurrentHour = " & Me!tboDoMath & " " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError
Click to expand...

This is why the Alias is required.

CurrentDb.Execute "UPDATE tblMotor AS A INNER JOIN tblMotor AS A " & _
"ON A.fldMotorID = A.fldMotorID " & _
"SET A.fldCurrentHour = " & Forms!frmMotor!tboDoMath & "
" & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError
Changing the code to include the "Me" reference to the control (in SET
statement) and adding the comma (", ") in the third line:
Yields; Error 3144, "Syntax error in UPDATE statement".
CurrentDb.Execute "UPDATE tblMotor INNER JOIN tblMotor " & _
"ON tblMotor.fldMotorID = tblMotor.fldMotorID " & _
"SET fldCurrentHour = " & Me!tboDoMath & ", " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError
Click to expand...

You'd only have a comma there if you had additional fields to update (and no
comma after the last field)

For a motor part in tblPart, which is installed (related) to a motor
assembly in tblMotor linked on the Primary Key of tblMotor, and with each
part in tblPart possibly having different hours of operation
(fldCurrentHour)
than the motor itself, following an adjustment (update) of the current
motor
hours of operation (fldCurrentHour), add the difference between "was" and
"is" current operating hours to all the related records in tblPart
(tblPart.fldCurrentHour).

Note: both the tblMotor, and tblPart have a field titled; fldCurrentHour,
and both are numbers; double.

With the modified statement below, dropping the JOIN and WHERE clauses I
don't receive the 3144 syntax error, and adjusting the math equation for
the
names of the actual controls I need to do the math, the procedure does
update
ALL the fields in tblPart, but the value the table's fields update to is
the
value in the tboAddNewCurntHR control, rather than the sum of
tboAddNewCurntHR and tboCurrentHour (which is my goal).

CurrentDb.Execute "UPDATE tblPart " & _
"SET fldCurrentHour = " & (Me!tboCurrentHour + (Me!tboAddNewCurntHR -
Me.tboCurrentHour)), dbFailOnError

Note: in break mode, the SET portion of the string is returning the proper
values for the named controls in the math equation and for the motor ID in
the WHERE clause.

So, in my lack of understanding how this should flow, here is the code
that
*looks* like what I need, but throws the 3144 syntax error:

CurrentDb.Execute "UPDATE tblPart " & _
"ON tblPart.fldAircraftID = tblAircraft.fldAircraftID " & _
"SET fldCurrentHour = " & (Me!tboCurrentHour + (Me!tboAddNewCurntHR -
Me.tboCurrentHour)) & ", " & _
"WHERE fldAircraftID = " & Me!tboAircraftID, dbFailOnError
Click to expand...

You're missing "INNER JOIN tblAircraft" (between tblPart and ON)
Note: the line continuation or break above is correct in the code, not in
my
Word draft.

The equation and the procedure should do the following:

Subtract the value of the form's unbound text box named tboAddNewCurntHR
from the value in the form's bound text box named tboCurrentHour (bound to
tblMotor), and add the result to all of the related fields in tblPart,
field
name fldCurrentHour that are keyed to tblMotor.fldMotorID.
Click to expand...

Not sure I really understand this. You were using tblAircraft above: was
that an error? How does tblMotor enter into this?
 
Oops, just noticed my aliases are incorrect!

CurrentDb.Execute "UPDATE tblMotor AS A INNER JOIN tblMotor AS B " & _
"ON A.fldMotorID = B.fldMotorID " & _
"SET A.AddNewHR = " & Forms!frmMotor!tboDoMath & " " & _
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError

CurrentDb.Execute "UPDATE tblMotor AS A INNER JOIN tblMotor AS B " & _
"ON A.fldMotorID = B.fldMotorID " & _
"SET A.fldCurrentHour = " & Forms!frmMotor!tboDoMath & " " &
_
"WHERE fldMotorID = " & Me!tboMotorID, dbFailOnError

etc.

Obviously the alias names have to be different, or they don't accomplish
anything!
 
Hi Doug; et al,

Thank you so much for the great replies and help. I got it all working
wonderfully now, and I'm so much smarter thanks to you all!

I would have replied sooner but I’ve been down hard with the flu.

I have to apologize for my last post with all that code, because I screwed
up royally in some of the referenced controls and tables. Because of my
bungle we got into the alias discussion and the solution you provided to
resolve it.

Right or wrong; I use a generic database as a sandbox tool, and then cut and
paste into my project db and do a Find/Replace for the proper control and
object references. Part of what I posted was from my project db, and part was
from my generic db, and then I messed up on the Find/Replace action; making
what I sent look like I was trying to join a table to itself when I wasn't
really. So again, my apologies for running you through a loop on that.

On the upside though; I did end up with some ambiguous references after all,
and with the alias solution you provided I was able to resolve all of those
issues.

I did spend quite a few hours with that one procedure before I could get it
to work in my circumstances, but rather than bother you again with more
questions, I felt it was better for me to struggle on my own so that I would
learn it more clearly and I think I did.

Thank you again for all your help on this!

Best regards,
Robert
 
Back
Top