Re: Update existing values incrementally w/UPDATE SQL

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

"dymondjack" wrote:

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.

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


"Robert5833" wrote:

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






"Douglas J. Steele" wrote:

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


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Robert5833" <Robert5833@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:98C29FB7-140B-4C34-9F88-A546E5A4EFB8@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Update existing values incrementally w/UPDATE SQL
    ... I think it would be safe to say, use the Dim statement any time you are ... Dim basically tells vba that you want to initiate a new variable. ... As far as quotes go, this was one of the trickiest ... Consider vba's interpretation of a string: ...
    (microsoft.public.access.modulesdaovba)
  • Re: Update existing values incrementally w/UPDATE SQL
    ... pretend that Me.txtString is a control on your form, ... You cant put Me.txtString inside the double quotes, or VBA just reads it as a ... and in the book; Access 2007 VBA Programmer's Reference. ... when an SQL is processed (I use an SQL example because it is the most ...
    (microsoft.public.access.modulesdaovba)
  • RE: SPL problem
    ... In VBA and .NET, I've noticed that when I build the variable load for an sql ... a table and another that fetches the SQL string from the table, executes it, ... quotes stripped from them, though they were there in the original SQL string ...
    (comp.databases.informix)
  • Re: Data type mismatch in criteria expression
    ... Applying those, the SQL ... > to the strSQLDeleteStd variable as such (string). ... > quotes denote String. ... > where you would normally have double quotes in the query SQL. ...
    (microsoft.public.access.queries)
  • Re: Invalid Procedure Call
    ... This is done with about 9 SQL strings total, ... My problem is occuring in the SQL string that takes the raw ... Fields: tag, pointid, date ... This SQL statement is put into a string and executed in VBA. ...
    (microsoft.public.access.queries)