Access VBA SQL UPDATE

[Apple001]

I am having trouble using SQL UPDATE statement in VBA.

I have:
Forms:
-"frmClaim": Main form. Data is based on table "claim." It has ClaimID, and other fields. ClaimID is the auto number and unique identifier.
-"frmClaimProduc ts":Subform attatched to frmClaim. This form is based on table "claim_product. " It has ClaimID, ProductID, TotalCost, and other fields.
-"frmClaimTransa ction": Subform attatched to frmClaim. This form is based on table "claim_transact ion." It has ClaimID, Date, Transaction Type, Amount, and other fields.



What I have done so far:
When someone create new record in "frmClaim," claim_id, transaction_id, date, and amount are inserted into table claim_transacti on. Below is the code. This works perfectly.

Public Function get_claimId() As String
    get_claimId = CLng(Nz(claim_id.value)) 'lblClaimID.Caption
End Function

Private Sub Form_Current()
Dim SQLtext As String

passID = get_claimId
DoCmd.SetWarnings False

Dim CheckCost As String
Dim strTotalCost As String

If Nz(DLookup("claim_id", "claim_product", "claim_id=" & passID)) = vbNullString Then
    SQLtext = "INSERT INTO claim_transaction (claim_id, transaction_id, amount, [date])"
    SQLtext = SQLtext & "VALUES('" & passID & "', '15', '0', Date())"
    DoCmd.RunSQL SQLtext
    SQLtext = vbNullString
Else
    If Nz(DLookup("claim_id", "claim_transaction", "transaction_id=15 And claim_id=" & passID)) = vbNullString Then
        SQLtext = "INSERT INTO claim_transaction (claim_id, transaction_id, amount, [date])"
' *****************************************************************
'Description of transaction_id=15 is "tortal product cost."
'******************************************************************
        SQLtext = SQLtext & "VALUES('" & passID & "', '15', '" & Me!frmClaimProductsTotal!SumOfTotalCost & "',Date())"
        DoCmd.RunSQL SQLtext
        SQLtext = vbNullString
    End If
End If
End Sub

What I want to do now:
When someone changes value in the frmClaimProduct s, I want to update the changes into table claim_transacti on automatically, so I wrote VBA code "On After Update" of form frmClaimProduct s. But it keeps giving me error, "Syntax error in UPDATE statement." I looked into the code and I couldn't find anything wrong by myself. Below is the code... Please help!!

Public Function get_claimId() As String

    get_claimId = CLng(Nz(claim_id.value)) 'lblClaimID.Caption
    
End Function


Private Sub Form_AfterUpdate()
passID = get_claimId
DoCmd.SetWarnings False

Dim PrdctTrnsc As String
Dim SQLtext As String


PrdctTrnsc = DLookup("amount", "claim_transaction", "transaction_id=15 And claim_id=" & passID)

DoCmd.SetWarnings False
If PrdctTrnsc <> Me!TotalCost Then
        SQLtext = "Update claim_transaction"
        SQLtext = SQLtext & "Set claim_id = '" & passID & "', transaction_id = '15', amount= '" & Me.TotalCost & "', date= Date()"
        SQLtext = SQLtext & "WHERE (((claim_id)='" & passID & "'))"
        DoCmd.RunSQL SQLtext
        SQLtext = vbNullString
    End If
DoCmd.SetWarnings True
End Sub

[Rabbit]

You don't have a space between your table name and your SET keyword. And you don't have a space between your Date() function and your WHERE keyword.

But why are you doing it this way? I don't know how your tables are set up and how your forms are set up but with a normalized table design and cascading relationships set up, there are very few situations where you need to insert and update records manually.

[Apple001]

You don't have a space between your table name and your SET keyword. And you don't have a space between your Date() function and your WHERE keyword.

But why are you doing it this way? I don't know how your tables are set up and how your forms are set up but with a normalized table design and cascading relationships set up, there are very few situations where you need to insert and update records manually.

Thank you very much for your quick reply, Rabbit!

I tried to insert the space as you told me, both before and after, but did not work... Other SQL statement I wrote doesn't have space, but they work fine.


This database that I am working on is kind of complicated... There are so many sub forms attached to the main form.

People who want me to work on this had this database for about 2 years, and this old database has too much problems. The guy who made this old database quit long time ago, so I am trying to make new forms and queries using the tables from the old one. The people do not want me to change most of the functions and designs, especially those automated insertion and update.

Example of the problems with old database are, it shows error message all the time, some people cannot get in to the database, some date fields always shows the way wrong date, like 2004, etc. All the fields in the form are not made using wisard. The guy who made this database created labels or text box for every single field in the form, write VBA and SQL code for all of them, even though he did not have to do so by using wizard. So I am trying to make the database as simple as I can by using wizards and to get rid of unnecessary codes.

Here is how the main and sub forms looks like (I can e-mail you screen shot of this form if you would like):

Main form=frmClaim: (based on table claim)
-------------------------------------------------------------------------------------------------------------------
<1st tab in the form: General Information>
(All fields are combo box except for claimID)
-claimID,
-status,
-claim reasons,
-claimant id (below this, there is a subform to show claimant's information such as address, phone# based on claimant id),
-carrier (below this, there is a same kind of subform as claimant id),
-origin state,
-origin city,
-origin (state, city, origin are combo boxes. It shows a list based on what an user select in previous box. For example, if user selected "CA" in state, it shows cities we have in city combo box. Below this, there is a same kind of subform as claimant id),
-destination state,
-destination city,
-destination (same kind of combo boxes as origin. below this, there is a same kind of subform as claimant id)

*Next to each field except for claimID, there are buttons where a user can click to add values if the user doesn't see the value in combo box.

---------------------------------------------------------------------------------------------------------------------
<2nd tab in the form: Returns and Activities>
Reference Numbers sub form (table view/ based on table claim_refnum and claim_refnum type):
-Reference Description (combo box), Refnum Value, InsertUser, UpdateUser, (hidden fields: claimID, Refnum)

Activities subform (table view / based on table claim_activity and claim_activity_ type)
-Date, Activity Description (combo box), Insert User, and Update User (hidden fields: ClaimID, ActivityID)

*Next to both sub forms, there are buttons where a user can click to add values for Reference Description and Activity Description, if the user doesn't see them in combo box.
*Activity Description "Date Opened" is inserted to the record with date when an user create new record.
*Activity Description "Date Mailed" is inserted to the record with date when an user first print sthe report.

---------------------------------------------------------------------------------------------------------------------
<3rd tab in the form: Products/ Transactions>
Products subform (table view / based on table claim_product and product)
-ProductID (combo box), Description (shows description based on ProductID), Units, Wt/Unit, Cost, Qty, Total Wt (calclates total weight), Total Cost (calclates Cost * Qty for each line of product), (hidden fields are ClaimID)

Total Products subform (based on query qryClaimProduct sTotal)
-Total Product Cost (calclates all the products cost from subform Products)

Transaction subform (table view / based on table claim_transacti on and claim_transacti on_type)
-Date, Transaction Type, Date Check Received (this field is not filled unless the user receives check and transaction type is related to check receipt), Amount (when customer pays, it's inserted as -$100 (e.g.)), CheckNo, InsertUser, UpdateUser

Total Balance subform (based on query qryTotalBalance )
-Total Balance (it calclates amount in transaction subform and shows balance.)

*Data in transaction subform is related to transaction field in the report, so Total Product Cost must be inserted.
*Transaction Type = "Total Product Costs" is inserted when the user first create the record. If there is no products in Products subform, then it inserts amount as zero.
*There are buttons for the user to add products and transaction type when the user doesnt see it on combo box.

----------------------------------------------------------------------------------------------------------------------
<4th tab in the form: Comments>
External Coments Subform (tabular view / based on table claim_comment)
-Date, Insert User, Comment

Internal Memo (this field is attached to main form, frmClaim, based on table claim)

----------------------------------------------------------------------------------------------------------------------
<In Header: enable the user to search reference number values in subform reference numbers.>
Reference number filter
-Reference Number Type (combo box)
-Reference Number



I hope this helps. Sorry about the long list. I wish I can make this explanation shorter, but I couldn’t make any shorter than this. .

[Apple001]

I realized I didn't wrap the field name "date" with [ ]. So, I did. Now the error says "data type mismatch in criteria expression."

Also I realized something stupid...I should have put the value in field, "SumOfTOtalCost " in subform Total Products in the 3rd tab.


It still doesn't work but I am trying...

[Rabbit]

No, you definitely need the spaces in there.
[Code=vb]
Dim strSQL As String

strSQL = "UPDATE Table1"
strSQL = strSQL & "SET Field1 = 'A'"
[/Code]
This returns:
[Code=sql]
UPDATE Table1SET Field1 = 'A'
[/Code]
Which doesn't work as a SQL statement. So you need the space. The one after Date() might be okay because it's a function and you might not need the space but you definitely need one before the SET

[Code=vb]
Dim strSQL As String

strSQL = "UPDATE Table1"
strSQL = strSQL & " SET Field1 = 'A'"
[/Code]

Also, in your DLookup functions, you define claim_id and transaction_id as numbers and yet, when you try to update them, you're defining them as string values using single quotes. '9' is not the same thing as 9.

[Apple001]

No, you definitely need the spaces in there.
[Code=vb]
Dim strSQL As String

strSQL = "UPDATE Table1"
strSQL = strSQL & "SET Field1 = 'A'"
[/Code]
This returns:
[Code=sql]
UPDATE Table1SET Field1 = 'A'
[/Code]
Which doesn't work as a SQL statement. So you need the space. The one after Date() might be okay because it's a function and you might not need the space but you definitely need one before the SET

[Code=vb]
Dim strSQL As String

strSQL = "UPDATE Table1"
strSQL = strSQL & " SET Field1 = 'A'"
[/Code]

Also, in your DLookup functions, you define claim_id and transaction_id as numbers and yet, when you try to update them, you're defining them as string values using single quotes. '9' is not the same thing as 9.

Ok, that makes sense.
I took your advice and changed the code. It still doesn't work. I am getting confused because passID is a string, but I still need to use numeric formula, which not to use any " or & at all?

I also moved the code into main form, frmClaim's OnCurrent() and put it together with other auto insertion code. Insertion codes still works fine, but the update code doesn't work at all. I don't get any error message anymore.

Thanks again for your help!

Public Function get_claimId() As String

    get_claimId = CLng(Nz(claim_id.value)) 'lblClaimID.Caption
    
End Function

Private Sub Form_Current()
    
Dim SQLtext As String

passID = get_claimId
DoCmd.SetWarnings False

Dim strCheckCost As String
Dim strTotalCost As String


    If Nz(DLookup("claim_id", "claim_product", "claim_id = " & passID)) = vbNullString Then
         SQLtext = "INSERT INTO claim_transaction (claim_id, transaction_id, amount, [date])"
         SQLtext = SQLtext & " VALUES('" & passID & "', 15, 0, Date())"
         DoCmd.RunSQL SQLtext
         SQLtext = vbNullString
    Else
        If Nz(DLookup("claim_id", "claim_transaction", "transaction_id =15 And claim_id = " & passID)) = vbNullString Then
            SQLtext = "INSERT INTO claim_transaction (claim_id, transaction_id, amount, [date])"
            SQLtext = SQLtext & " VALUES('" & passID & "', 15, '" & Me!frmClaimProductsTotal!SumOfTotalCost & "',Date())"
            DoCmd.RunSQL SQLtext
            SQLtext = vbNullString
        Else
         CheckCost = DLookup("amount", "claim_transaction", "transaction_id = 15 And claim_id = " & passID)
            If strCheckCost <> Me!frmClaimProductsTotal!SumOfTotalCost Then
                SQLtext = "Update claim_transaction"
                SQLtext = SQLtext & " Set [claim_id] = '" & passID & "', [transaction_id] = 15, [date] = Date(), [amount] = " & Me!frmClaimProductsTotal!SumOfTotalCost.value
                QLtext = SQLtext & " WHERE (((claim_id) = '" & passID & "'))"
            End If
     End If
    End If

[Rabbit]

So this is the part that we need to look at:
[Code=vb]
If strCheckCost <> Me!frmClaimProd uctsTotal!SumOf TotalCost Then
SQLtext = "Update claim_transacti on"
SQLtext = SQLtext & " Set [claim_id] = '" & passID & "', [transaction_id] = 15, [date] = Date(), [amount] = " & Me!frmClaimProd uctsTotal!SumOf TotalCost.value
QLtext = SQLtext & " WHERE (((claim_id) = '" & passID & "'))"
End If
[/Code]
Me!frmClaimProd uctsTotal!SumOf TotalCost
Unless frmClaimProduct s is a subform on the event that called it, then you don't need it. If it is a subform, then the correct syntax is:
Me!frmClaimProd uctsTotal.Form. SumOfTotalCost

If your fields really are strings then you will need the quotes. It just looked like they weren't from the way you set up your DLookups().

[Apple001]

So this is the part that we need to look at:
[Code=vb]
If strCheckCost <> Me!frmClaimProd uctsTotal!SumOf TotalCost Then
SQLtext = "Update claim_transacti on"
SQLtext = SQLtext & " Set [claim_id] = '" & passID & "', [transaction_id] = 15, [date] = Date(), [amount] = " & Me!frmClaimProd uctsTotal!SumOf TotalCost.value
QLtext = SQLtext & " WHERE (((claim_id) = '" & passID & "'))"
End If
[/Code]
Me!frmClaimProd uctsTotal!SumOf TotalCost
Unless frmClaimProduct s is a subform on the event that called it, then you don't need it. If it is a subform, then the correct syntax is:
Me!frmClaimProd uctsTotal.Form. SumOfTotalCost

If your fields really are strings then you will need the quotes. It just looked like they weren't from the way you set up your DLookups().

Yes, frmClaimProduct sTotal is a sub form attached to main form frmClaim. SumOfTotalCost is a field in sub form frmClaimProduct s. I changed to the correct format to reffer to subform, but it still doesn't work :-(

If strCheckCost <> Me!frmClaimProductsTotal.Form.SumOfTotalCost Then
                SQLtext = "UPDATE claim_transaction"
                SQLtext = SQLtext & " Set [claim_id] = '" & passID & "', [transaction_id] = 15, [date] = Date(), [amount] = '" & Me!frmClaimProductsTotal.Form.SumOfTotalCost & "'"
                SQLtext = SQLtext & " WHERE (((claim_id) = '" & passID & "'))"
                DoCmd.RunSQL SQLtext
                SQLtext = vbNullString
        End If

[Rabbit]

Ok, I think I know the problem now. While you are on the main form, the subform controls don't return a value. So the update won't trigger because the value on the subform won't ever match your value since you're triggering the event from the main form.

What you have to do is trigger the event from the subform. Probably through a button of some sort. Or maybe through the On Current event of the subform.

[Apple001]

Ok, I think I know the problem now. While you are on the main form, the subform controls don't return a value. So the update won't trigger because the value on the subform won't ever match your value since you're triggering the event from the main form.

What you have to do is trigger the event from the subform. Probably through a button of some sort. Or maybe through the On Current event of the subform.

Hm, that's interesting...
the other INSERT SQL using subform's value works fine, which is:

  Else
    'If there is any products, do this
        If Nz(DLookup("claim_id", "claim_transaction", "transaction_id =15 And claim_id = " & passID)) = vbNullString Then
            SQLtext = "INSERT INTO claim_transaction (claim_id, transaction_id, amount, [date])"
            SQLtext = SQLtext & " VALUES('" & passID & "', 15, '" & Me!frmClaimProductsTotal!SumOfTotalCost & "',Date())"
            DoCmd.RunSQL SQLtext
            SQLtext = vbNullString
        End If

I wonder if there is any difference.

[Apple001]

OMG!!!!!!!!!!!!
I made it work!!

I am so dumb. I was missing something in WHERE part. Also I got rid of updating the same information as before UPDATE, which are claim_ID, Date (I may need to update date but I am not sure for now)

Below is the code that worked.
Rabitt, thank you very much for your help!!
Have a nice day :-)

Private Sub Form_Current()
    
Dim SQLtext As String

passID = get_claimId
DoCmd.SetWarnings False

Dim strCheckCost As String
Dim strTotalCost As String

    'Do this if there is no product for this claimID
    If Nz(DLookup("claim_id", "claim_product", "claim_id = " & passID)) = vbNullString Then
         SQLtext = "INSERT INTO claim_transaction (claim_id, transaction_id, amount, [date])"
         SQLtext = SQLtext & " VALUES('" & passID & "', 15, 0, Date())"
         DoCmd.RunSQL SQLtext
         SQLtext = vbNullString
    Else
    'If there is any products, do this
        If Nz(DLookup("claim_id", "claim_transaction", "transaction_id =15 And claim_id = " & passID)) = vbNullString Then
            SQLtext = "INSERT INTO claim_transaction (claim_id, transaction_id, amount, [date])"
            SQLtext = SQLtext & " VALUES('" & passID & "', 15, '" & Me!frmClaimProductsTotal!SumOfTotalCost & "',Date())"
            DoCmd.RunSQL SQLtext
            SQLtext = vbNullString
        End If
            'If produt total (=transaction_id 15) already exist in "claim_transaction" table,
            'and its value is different from sub form ffrmClaimProductsTotal, then update "claim_transaction" table
            CheckCost = DLookup("amount", "claim_transaction", "transaction_id = 15 And claim_id = " & passID)
        If strCheckCost <> Me!frmClaimProductsTotal.Form.SumOfTotalCost Then
                SQLtext = "UPDATE claim_transaction"
                SQLtext = SQLtext & " Set amount = '" & Me!frmClaimProductsTotal!SumOfTotalCost & "'"
                SQLtext = SQLtext & " WHERE transaction_id = 15 And claim_id = " & passID
                DoCmd.RunSQL SQLtext
                SQLtext = vbNullString
        End If
    End If

[Rabbit]

Excellent, glad you figured out the problem. Good luck.