Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    24

    Question Unanswered: Update table using VBA in Access 2007

    Hi,

    I'm fairly new to Access and SQL but have been using VBA in Excel for years so have a fairly good understanding of forms etc.

    My problem is that I have a table (LeadSource) with 3 columns (ID (unique integer from 1 to 37, Primary Key), Lead Source (text) & Quantity (integer)).

    The first 2 columns are already filled in as fixed data, what I want to do is add a value (that has been calculated elsewhere) to each line in the Quantity column.

    Based on my limited knowledge and what I have found in the helps files I have come up with this so far:

    Code:
    Private Sub CmdExport_Click()
    
    For i = 1 To 37
        
        StrQLeadSource = CBoxLeadSourceLS.ItemData(i)
        
        CBoxLeadSourceLS.Value = StrQLeadSource
        
        LCtLeadSource = DCount("[Lead Source]", "Leads", "[Lead Source] = CBoxLeadSourceLS.Value")
        
        Call DoSQL
        
    Next i
    
    End Sub
    
    Public Sub DoSQL()
    
        Dim SQL As String
        
        SQL = "UPDATE LeadSource " & _
              "SET [Quantity] = " & LCtLeadSource & _
              "WHERE [ID] = " & i
    
        DoCmd****nSQL SQL
        
    End Sub
    (CBoxLeadSourceLS is a combo box I have on a form that has a list of all the values in the 'Lead Source' column. This is so the user can select an individual item to get the stats for just that item)

    When I try running this I get this error:

    Code:
    Run-time error '3705':
    
    syntax error (missing operator) in query expression '0WHERE [ID] = 1'.
    I've been trying to solve this for a while so any help you can give would be greatly appreciated

    Hope that's enough (also not too much) info for you to use, if not then let me know.

    Thanks,
    Gav

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
        SQL = "UPDATE LeadSource " & _
              "SET [Quantity] = " & LCtLeadSource & _
              " WHERE [ID] = " & i
    or:
    Code:
        SQL = "UPDATE LeadSource " & _
              "SET [Quantity] = " & LCtLeadSource & " " &  _
              "WHERE [ID] = " & i
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    24
    Quote Originally Posted by Sinndho View Post
    Try:
    Code:
        SQL = "UPDATE LeadSource " & _
              "SET [Quantity] = " & LCtLeadSource & _
              " WHERE [ID] = " & i
    or:
    Code:
        SQL = "UPDATE LeadSource " & _
              "SET [Quantity] = " & LCtLeadSource & " " &  _
              "WHERE [ID] = " & i
    That second option worked brilliantly thanks.

    The only problem now is that I get an "Are you sure?" message box for every row.
    Is there anyway I can stop that appearing? The end users aren't very tech savy and it would probably scare them to have this keep coming up

    Thanks,
    Gav

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    You need to switch off warnings before the update statement is run, and switch them on again afterwards.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Apr 2013
    Posts
    24
    Quote Originally Posted by weejas View Post
    You need to switch off warnings before the update statement is run, and switch them on again afterwards.
    Ok thanks,

    I didn't realise there was a code for this, I've found it now that I know what to look for.

    In case any other newbies are wanting to know I used:
    Code:
        DoCmd.SetWarnings False  
      
        Call DoSQL
        
        DoCmd.SetWarnings True
    Thanks,
    Gav

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •