# Solved: Access SQL Update using Variables from the VBA



## Chris_E (Feb 16, 2006)

This is probably a real snip for many here but I've been trying and failing to pass a variable from my VBA to an SQL Update.

In a nutshell, imagine that on click of an Access Form button the code then updates a number of fields in the table "MasterTable" where a field "MainValue" is equal to a simple value called LoopValue calculated in the VBA

A stripped down version of my code is:


```
Dim SQLMasterUpdate As String
Dim LoopValue As Double

For LoopValue = 1 to 10

SQLMasterUpdate = "UPDATE MasterTable SET " & _
                "MasterTable.mstrDateAccess = Now(), " & _
                "MasterTable.mstrWhoAccess = GetUser(), " & _
                "WHERE ((MasterTable.MainValue) = [B][COLOR="Red"]LoopValue [/COLOR][/B]);"
DoCmd.RunSQL SQLMasterUpdate

Next LoopValue
...
...
```
I've tried quotes, braces etc around "Loopvalue" but simply can't get the code to update the table. It either updates "0" records or I'm prompted for "Loopvale" in a parameter style box.

Any ideas where I am going wrong?

Or is there an easier or slicker way to do this (I'll eventually be updating several thousand records in this way.....)

Thanks

Chris


----------



## Rollin_Again (Sep 4, 2003)

Have you tried


```
"WHERE ((MasterTable.MainValue) = " & LoopValue & " );"
```
Regards,
Rollin


----------



## Chris_E (Feb 16, 2006)

Rollin

Faster than a speeding bullet!!!

It worked first time.

You are a genius. Please take a payrise!

Many thanks indeed.

Chris


----------



## Chris_E (Feb 16, 2006)

One question Rollin...

Is it possible and faster to do this?

I know I can use .Add but how about .Edit?


```
Set db = Access.Application.CurrentDb
     Set rstKnown = db.OpenRecordset("Known090", dbOpenDynaset, dbSeeChanges)

For LoopValue = 1 to 10

    With rstKnown
      [B][COLOR="Red"].Edit[/COLOR][/B]
        ![mstrDateAccess ] = Now() 
        ![mstrWhoAccess ] = GetUser()
        ![MainValue] = LoopValue 
    .Update
    End With
Next LoopValue

  Set rstKnown = Nothing
  Set db = Nothing
```
Chris


----------



## cristobal03 (Aug 5, 2005)

Conceptually the model you suggest is a little faster than your original, but your code is slightly off. If you look at it closely, you'll see you haven't supplied any mechanism for crawling through the recordset. In your original construct, you built a control structure that executes a SQL query 10 times. I _think_ what you're trying to do here is update 10 records by moving through them iteratively.

Here's a closer (airware) code model:


```
Set db = Access.Application.CurrentDb
  Set rstKnown = db.OpenRecordset("Known090", dbOpenDynaset, dbSeeChanges)

  With rstKnown
    // First, check to make sure the recordset isn't empty
    If Not .EOF Then
      // Make sure older records are included in the iteration
      .MoveLast
      .MoveFirst

      For LoopValue = 1 to 10
        .Edit
          ![mstrDateAccess ] = Now() 
          ![mstrWhoAccess ] = GetUser()
          ![MainValue] = LoopValue 
        .Update

        // [b]MoveNext[/b] steps to the next record in the returned set
        .MoveNext
      Next LoopValue
    End If
  End With

  Set rstKnown = Nothing
  Set db = Nothing
```
I haven't written Access VBA in a year though, so I might have some syntax errors. Also, this (as well as your previous) depends on the recordset being pre-ordered if you're populating records with values dependent upon their order.

HTH

chris.


----------



## Chris_E (Feb 16, 2006)

Hello Cristobal

Many thanks for the feedback.

I like your coding sample and it may well work. The snag you mention to the end of your email may be a roadblock for me though and I think, under the circumstances the SQL mode will be more sensitive to how the records are found in the table (i.e. on sequenced).

My next 'oh my - why isn't that working' issue is where I subsequently need to update that second table (Known090) through SQL, using Rollin's earlier example...

In the second table, the field named [Known090.FullNumber] is a TEXT field (yeah - I know, but its due to an external app that reads the value of this field as text to speech and it gets a bit stupid when it sees a number)...

See code chunk below


```
For LoopingValue = 1 to 10

SQLKnown090 = "UPDATE Known090 SET " & _
                "Known090.LastEdit = [forms]![EditRangeNumbers]![NowDateTime], " & _
                "Known090.LastEditBy = [forms]![EditRangeNumbers]![Editby], " & _
                "Known090.Description = [forms]![EditRangeNumbers]![Tab_txtReadOutMessage] " & _
                "WHERE " & _
                "((Known090.FullNumber) = " & LoopingValue & " );"

DoCmd.RunSQL SQLKnown090  
  
    Next LoopingValue
```
With FullNumber as a Text field, I get the error "Data Type Mismatch in Criteria Expression".

So I did this:


```
For LoopingValue = 1 to 10
        LoopingTxtValue = Str(LoopingValue)

SQLKnown090 = "UPDATE Known090 SET " & _
                "Known090.LastEdit = [forms]![EditRangeNumbers]![NowDateTime], " & _
                "Known090.LastEditBy = [forms]![EditRangeNumbers]![Editby], " & _
                "Known090.Description = [forms]![EditRangeNumbers]![Tab_txtReadOutMessage] " & _
                "WHERE " & _
                "((Known090.FullNumber) = " & LoopingTxtValue & " );"

DoCmd.RunSQL SQLKnown090  
  
    Next LoopingValue
```
Again, With FullNumber as a Text field, I STILL get the error "Data Type Mismatch in Criteria Expression".

What am I missing this time?

At the moment I'm 99% there - if I can sort this one out, I think I'm nearl finished..

Hope you are able to guide me.

By the way - where did you learn all of these syntax rules and structures?

Chris


----------



## cristobal03 (Aug 5, 2005)

I don't think the type mismatch is a product of the *FullNumber* field. I'm guessing the issue is with the *LastEdit* field. My assumption is that it's a Date/Time data type. What exactly is the *NowDateTime* form control?

Again, I haven't used Access in a really long time, and I don't know where this code exists in your modules, _and_ I never really used the *Forms* object reference the way you are, but something like this might work:


```
With Forms("EditRangeNumbers")
  SQLKnown090 = "UPDATE Known090 SET " & _
                "Known090.LastEdit = " & !NowDateTime.Value  & ", " & _
                "Known090.LastEditBy = " & !Editby.Value & ", " & _
                "Known090.Description = " & !Tab_txtReadOutMessage.Value & " " & _
                "WHERE " & _
                "((Known090.FullNumber) = " & LoopingTxtValue & " );"
End With
```
Again, I'm only suggesting that because that's what I'm most familiar with. And that won't fix the problem if your *NowDateTime* control value is a String data type and its ControlSource is a Date/Time data type; you'll have to use a format function in that case.

As far as learning good code techniques, I used to be active in a database forum called MDBMakers. I also used Access VBA's help documentation extensively, and I bought The Access VBA Handbook vols. 1 and 2...at least I think that's the name of it. Mostly I learned by example and taught myself the rest.

HTH

chris.

[edit]
Since when are URLs not blue? That reference to MDBMakers is a hyperlink.
[/edit]


----------



## Chris_E (Feb 16, 2006)

Almost almost almost there...



> And that won't fix the problem if your NowDateTime control value is a String data type and its ControlSource is a Date/Time data type; you'll have to use a format function in that case.


Already thought of that one Cristobal. The NowDateTime control is a Time/Date control, format - General Date (its control source is =Now(), so I may as well use:


```
"Known090.LastEdit = Current_Timestamp" & _
```
in my statement.

The gremlin NOW is an error as follows :

"Syntax error (missing operator) in query expression "This number is charged at 10 pence per call".

Now, this is the beginning content of the "Me.Tab_txtReadOutMessage.Value" control and the error is likely because the WHOLE string is "This number is charged at 10 pence per call*, * and..... blah blah blah"

Note the comma after the word "call"....

I suspect the comma in the string is messing things up here. There are often 2 or more commas in these strings used to help the Text to Speech reader engine make the sentence read out more plausible.

So, I think if there is help about this string comma thing, it will conclude this issue.

Chris


----------



## cristobal03 (Aug 5, 2005)

Yeah, I don't know why I didn't spot this. You should be putting quotes around your string statements in the query. Try this:


```
With Forms("EditRangeNumbers")
  SQLKnown090 = "UPDATE Known090 SET " & _
                "Known090.LastEdit = " & !NowDateTime.Value  & ", " & _
                "Known090.LastEditBy = '" & !Editby.Value & "', " & _
                "Known090.Description = '" & !Tab_txtReadOutMessage.Value & "' " & _
                "WHERE " & _
                "((Known090.FullNumber) = '" & LoopingTxtValue & "' );"
End With
```
The difference is difficult to see here, but I added single quotes around each parameter that expects a string data type (everything except the timestamp). Hopefully that will get you squared away.

chris.


----------



## Chris_E (Feb 16, 2006)

Bingo!

Absolute genius. And all for the want of a few measly single quote things

Thanks Cristobal

Chris


----------



## cristobal03 (Aug 5, 2005)

No problem :up:

I'd say to mark it solved, but that's been done for a while now lol. Glad to help.

chris.

[edit]
By the way, you'll see problems again if there is ever an apostrophe or somesuch in one of those control values (*EditBy* and *Tab_txtReadOutMessage*). Escaping quotes is a real hassle in VBA and is one of the biggest problems with dynamically storing free-text content. I guess I'm just saying, if you see errors in the future and you notice there are single quotes or apostrophes in one of those control values, don't be alarmed because it will cause errors.
[/edit]


----------



## Rollin_Again (Sep 4, 2003)

Nice job Cristobal :up: 

Regards,
Rollin


----------

