Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 490
  • Last Modified:

INSERT statement syntax - VBA

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
design2odd
Asked:
design2odd
  • 4
  • 3
1 Solution
 
1WilliamCommented:
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
 
SQLDBACommented:
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
 
design2oddAuthor Commented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SQLDBACommented:
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
 
SQLDBACommented:
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
 
SQLDBACommented:
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
 
design2oddAuthor Commented:
Thanks, good catch, logic escapes me sometimes.
0
 
design2oddAuthor Commented:
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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now