?
Solved

INSERT statement syntax - VBA

Posted on 2003-03-13
8
Medium Priority
?
466 Views
Last Modified: 2006-11-17
This code gives me a missing semicolon error.

    SQLString = "INSERT INTO tblAccountDetailData " _
                & "VALUES ('[Etad]','[MainAcc]','','[MainPoints]','earned','0') " _
                & "WHERE AccountNum = MainAcc;"
    DoCmd.RunSQL SQLString

Help please and thanks for any suggestions.
0
Comment
Question by:design2odd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 18

Expert Comment

by:1William
ID: 8131068
I'll make a suggestion.  Try to create the query in a query grid window.  The problem lies in your Where clause.  You are updating a table with values, not from a table (that might contain the field MainAcc).
0
 
LVL 1

Expert Comment

by:SQLDBA
ID: 8131348
Looks like the problem is:

AccountNum = MainAcc

You need single or double quotes around the value, like this:

AccountNum = "MainAcc"
or
AccountNum = 'MainAcc'


If MainAcc is a user entered value then you need to break it out of your string, like this:


SQLString = "INSERT INTO tblAccountDetailData " _
& "VALUES ('" & Etad & "','" & MainAcc "','','" & MainPoints & "','earned','0') " _
& "WHERE AccountNum = '" & MainAcc & "'"
DoCmd.RunSQL SQLString




Hope that helps... :)
0
 

Author Comment

by:design2odd
ID: 8131875
It's still giving the same error, this is the Sub thanks:)

Private Sub Combine_Click()
'Declare Variables -------------------------
    Dim MainAcc As Double
    Dim MainPoints As Double
    Dim Com1 As Double
    Dim Com1Points As Double
    Dim Com2 As Double
    Dim Com2Points As Double
    Dim Com3 As Double
    Dim Com3Points As Double
    Dim Com4 As Double
    Dim Com4Points As Double
    Dim Com5 As Double
    Dim Com5Points As Double
    Dim Etad As Integer
    Dim PointsEarned As Double
    Dim TransType As String
    Dim BonusPoints As Integer
    Dim SQLString As String
    Dim SQLStringUpdateNotes As String
'Set Variables------------------------------
    MainAcc = Me.MainAcc.Value
    MainPoints = Me.MainPoints.Value
    'Com1 = Me.Com1.Value
    'Com1Points = Me.Com1Points.Value
    Eatd = Me.Etad.Value
    Me.Form.Caption = MainPoints
'Update Notes Field-------------------------
    SQLStringUpdateNotes = "UPDATE tblAccountHeaderData " _
                & "SET tblAccountHeaderData.Notes = [MainAcc]" _
                & "WHERE (((tblAccountHeaderData.AccountNum)=" & MainAcc & "));"
    DoCmd.RunSQL SQLStringUpdateNotes
'insert into account detail table the points from the other account and delete the points in the combined account and add note explaining what happened
    SQLString = "INSERT INTO tblAccountDetailData " _
                & "VALUES ('" & Etad & "','" & MainAcc & "','','" & MainPoints & "','earned','0') " _
                & "WHERE (((tblAccountDetailData.AccountNum)=" & MainAcc & "));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLString
    DoCmd.SetWarnings True
End Sub






This is where I am at.
tblAccountDetailData has 6 fields
Data, AccountNum, Reference, PointsEarned, TransType, BonusPoints,

You are up to date now.
Thanks for the help from all so far
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 1

Expert Comment

by:SQLDBA
ID: 8131986
SQLStringUpdateNotes = "UPDATE tblAccountHeaderData " _
& "SET tblAccountHeaderData.Notes = [MainAcc]" _
& "WHERE (((tblAccountHeaderData.AccountNum)=" & MainAcc & "));"



What is this value in your update query?: [MainAcc]

If this is the variable MainAcc then you need to bring it out as well...
0
 
LVL 1

Accepted Solution

by:
SQLDBA earned 200 total points
ID: 8132037
Actually I see the problem!

SQLString = "INSERT INTO tblAccountDetailData " _
& "VALUES ('" & Etad & "','" & MainAcc & "','','" & MainPoints & "','earned','0') " _
& "WHERE (((tblAccountDetailData.AccountNum)=" & MainAcc & "));"


This insert statement has a "Where clause"...

Change this to an update statement and you're fixed!
0
 
LVL 1

Expert Comment

by:SQLDBA
ID: 8132068
To explain a bit more:

INSERT INTO [TABLENAME]
Values(1,2,3)

cannot have a where clause because you are inserting a new record.

If you change the query to an UPDATE statement then you are updating an existing record and you can use the where clause to specify which existing records meet your criteria for the update...

Does that help?
0
 

Author Comment

by:design2odd
ID: 8132096
Thanks, good catch, logic escapes me sometimes.
0
 

Author Comment

by:design2odd
ID: 8132182
It's still giving the same error, this is the Sub thanks:)

Private Sub Combine_Click()
'Declare Variables -------------------------
    Dim MainAcc As Double
    Dim MainPoints As Double
    Dim Com1 As Double
    Dim Com1Points As Double
    Dim Com2 As Double
    Dim Com2Points As Double
    Dim Com3 As Double
    Dim Com3Points As Double
    Dim Com4 As Double
    Dim Com4Points As Double
    Dim Com5 As Double
    Dim Com5Points As Double
    Dim Etad As Integer
    Dim PointsEarned As Double
    Dim TransType As String
    Dim BonusPoints As Integer
    Dim SQLString As String
    Dim SQLStringUpdateNotes As String
'Set Variables------------------------------
    MainAcc = Me.MainAcc.Value
    MainPoints = Me.MainPoints.Value
    'Com1 = Me.Com1.Value
    'Com1Points = Me.Com1Points.Value
    Eatd = Me.Etad.Value
    Me.Form.Caption = MainPoints
'Update Notes Field-------------------------
    SQLStringUpdateNotes = "UPDATE tblAccountHeaderData " _
                & "SET tblAccountHeaderData.Notes = [MainAcc]" _
                & "WHERE (((tblAccountHeaderData.AccountNum)=" & MainAcc & "));"
    DoCmd.RunSQL SQLStringUpdateNotes
'insert into account detail table the points from the other account and delete the points in the combined account and add note explaining what happened
    SQLString = "INSERT INTO tblAccountDetailData " _
                & "VALUES ('" & Etad & "','" & MainAcc & "','','" & MainPoints & "','earned','0') " _
                & "WHERE (((tblAccountDetailData.AccountNum)=" & MainAcc & "));"
    DoCmd.SetWarnings False
    DoCmd.RunSQL SQLString
    DoCmd.SetWarnings True
End Sub






This is where I am at.
tblAccountDetailData has 6 fields
Data, AccountNum, Reference, PointsEarned, TransType, BonusPoints,

You are up to date now.
Thanks for the help from all so far
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question