SQL Statements using Access 2010

D

DeonO

Novice Programmer
Local time
Today, 09:14
Joined
Sep 15, 2011
Messages
31
Hi all,
I am trying to update and insert records with SQL statements. Below my code:

Select Case FirstGRV
Case "Y"
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = date(), SellingPriceDateTo = #" & Me!Text3 & "#" & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateFrom =#01-01-1900#;"
DoCmd.RunSQL sql
Case "N"
Dim db As Database
Set db = CurrentDb
sql = "UPDATE Tbl_SellingPrices " & _
"SET SellingPrice = " & Me!Text2 & " " & _
"WHERE SellingPriceStockCode = Forms!Frm_GRV!GRVStockCode and SellingPriceDateTo = #" & Me!Text3 & "#;"
db.Execute sql

If db.RecordsAffected = 0 Then
sql = "INSERT INTO Tbl_SellingPrices " _
& "(SellingPriceStockCode, SellingPrice, SellingPriceDateFrom, SellingPriceDateTo) VALUES " _
& "(Forms!Frm_GRV!GRVStockCode, " & Me!Text2 & ", date(), #" & Me!Text3 & "#);"
db.Execute sql
End If
End Select

My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.

I have now been struggling with this for a week and no closer to a solution.
Can somebody shed light on what I may be doing wrong?

Thanks
Deon
 
Hi, try this piece of code.
Code: 
[COLOR=Red][B]Dim db As Database
Set db = CurrentDb[/B][/COLOR]

Select Case FirstGRV
    Case "Y"
        sql = "UPDATE Tbl_SellingPrices " & _
              "SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = [COLOR=Red][B]#" Date() "#[/B][/COLOR], SellingPriceDateTo = #" & Me!Text3 & "#" & _
              " WHERE SellingPriceStockCode = [COLOR=Red][B]" & Forms!Frm_GRV!GRVStockCode & "[/B][/COLOR] AND SellingPriceDateFrom = #01/01/1900#;"
        [COLOR=Red][B]db.Execute sql[/B][/COLOR]
    Case "N"
        sql = "UPDATE Tbl_SellingPrices " & _
              "SET SellingPrice = " & Me!Text2 & _
              " WHERE SellingPriceStockCode = [COLOR=Red][B]" & Forms!Frm_GRV!GRVStockCode & "[/B][/COLOR] AND SellingPriceDateTo = #" & Me!Text3 & "#;"
        db.Execute sql

        If db.RecordsAffected = 0 Then
            sql = "INSERT INTO Tbl_SellingPrices " _
                  & "(SellingPriceStockCode, SellingPrice, SellingPriceDateFrom, SellingPriceDateTo) VALUES " _
                  & "([COLOR=Red][B]" & Forms!Frm_GRV!GRVStockCode & "[/B][/COLOR], " & Me!Text2 & ", [COLOR=Red][B]#" & Date() & "#[/B][/COLOR], #" & Me!Text3 & "#);"
            db.Execute sql
        End If
End Select
One small request, Please use Code Tags when posting VBA Code
 
pr2-eugin said:
Code: 
[COLOR=Red][B]Dim db As Database
[COLOR="Red"]Set db = CurrentDb[/COLOR][/B][/COLOR]
Click to expand...
I would imagine you're already doing this since you're using the "db" object in your code.

Add this:
pr2-eugin said:
Code: 
        db.Execute sql[COLOR="red"], dbFailOnError[/COLOR]
Click to expand...

If you're 100% sure that records are being updated and you can see the updates then best to leave this part as it already is in your code. Let the syntax analyzer do the type casting for you. If you bring it out of the quotes then you need to start worrying about whether it's Text or Date or Number.
pr2-eugin said:
" & Forms!Frm_GRV!GRVStockCode & "
Click to expand...
 
Hi all,
Thanks for the responses. Let me try and explain what I want to achieve:

I have a table (Tbl_SellingPrices) with the following fields:

SellingPriceStockCode [Text]
SellingPrice [Number]
SellingPriceDateFrom [Date/Time]
SellingPriceDateTo [Date/Time]

When a GRV (Goods Receive Voucher) is done, the selling prices needs to be updated in the Tbl_SellingPrices Table. This is where I use the SQL statements.

If it is the first time that a GRV is done against the stock code, FirstGRV = "Y". now the code in the Select Statement must execute:

Code: 
Select Case FirstGRV
    Case "Y"
        sql = "UPDATE Tbl_SellingPrices " & _
              "SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = #" & Date & "#, SellingPriceDateTo = #" & Me!Text3 & "#" & _
              " WHERE SellingPriceStockCode = " & Forms!Frm_GRV!GRVStockCode & " AND SellingPriceDateFrom = #01-01-1900#;"
        db.Execute sql

It should check where the SellingPriceStockCode is equal to GRVStockCode (a text box control on my form) and where the date is equal to 01-01-1900. In this case, it fails, although the stock code is in the table and the date is set to 01-01-1900.

In the case where FirstGRV returns "N" the "Case "N"" part needs to execute:
The first SQL statement must execute where the stock code exists and the SellingPriceDateTo = Text3 (also set to short date format) on my form. Only the Selling Price must be updated with the new price:

Code: 
    Case "N"
        sql = "UPDATE Tbl_SellingPrices " & _
              "SET SellingPrice = " & Me!Text2 & _
              " WHERE SellingPriceStockCode = " & Forms!Frm_GRV!GRVStockCode & " AND SellingPriceDateTo = #" & Me!Text3 & "#;"
        db.Execute sql

if the update fails, then the second SQL statement must be executed. It must then insert a new record:

Code: 
        If db.RecordsAffected = 0 Then
            sql = "INSERT INTO Tbl_SellingPrices " _
                  & "(SellingPriceStockCode, SellingPrice, SellingPriceDateFrom, SellingPriceDateTo) VALUES " _
                  & "(" & Forms!Frm_GRV!GRVStockCode & ", " & Me!Text2 & ", #" & Date & "#, #" & Me!Text3 & "#);"
            db.Execute sql

NOTE: I inserted the code as proposed by pr2, but it still does not do what it should. All the SQL statements fail except the second SQL statement above.

I hope I explained it in enough detail.

Much appreciated
Deon
 
We understand what you're trying to do. Did you take note of the last two things I mentioned in my post? This is not reflecting in your new code. And like I also mentioned, you need to check that after execution, the record gets updated. If it doesn't then there's something wrong with the SQL syntax.
 
Sorry, I forgot to mention that I also tried the "dbFailOnError", but with the same results.

I checked the table after execution of the SQL statements, but nothing has been updated or inserted in the table.

When I run the procedure for the second time where "FirstGRV" evaluates to "N", the second SQL statement keeps on inserting lines into the table, although the record must just be updated as the stock code is present and the SellingPriceDateTo is equal to the date (Text3) on my form.
 
DeonO said:
I checked the table after execution of the SQL statements, but nothing has been updated or inserted in the table.
Click to expand...
There's obviously nothing wrong with the RecordsAffected property. It's something to do with your SQL statements. And by the way you still need to have dbFailOnError there.

So let's begin with the very first one, can you create an SELECT statement from that and do it directly in a query. You need to check that it first returns records. You're ok with this step?
 
Hi,
I created the SELECT query and it returned the correct records.
 
Hi,
I ran the SQL statement and printed to Immediate Window:
Code: 
UPDATE Tbl_SellingPrices SET SellingPrice = 15 WHERE SellingPriceStockCode = C002 AND SellingPriceDateTo = #31-07-2014#;

This is for the first SQL statement when FirstGRV = "N"

The following is the second SQL statement when FirstGRV= "N":
Code: 
INSERT INTO Tbl_SellingPrices (SellingPriceStockCode, SellingPrice, SellingPriceDateFrom, SellingPriceDateTo) VALUES (C002, 15, #11-07-2014#, #31-07-2014#);

everything looks fine to me, but it still does not do what it should do.
 
C002 is a String, Date Format is wrong, now try wrapping SellingPriceStockCode value in single quotes, Format the Date using Format function. Something like.
Code: 
Dim db As Database
Set db = CurrentDb

Select Case FirstGRV
    Case "Y"
        sql = "UPDATE Tbl_SellingPrices " & _
              "SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = " [COLOR=Red][B]Format([/B][/COLOR]Date(), [COLOR=Red][B]"\#mm\/dd\/yyyy\#")[/B][/COLOR] ", SellingPriceDateTo = " & [COLOR=Red][B]Format([/B][/COLOR]Me!Text3[COLOR=Red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR] & _
              " WHERE SellingPriceStockCode = [COLOR=Red][B]'[/B][/COLOR]" & Forms!Frm_GRV!GRVStockCode & "[COLOR=Red][B]'[/B][/COLOR] AND SellingPriceDateFrom = #01/01/1900#;"
        db.Execute sql
    Case "N"
        sql = "UPDATE Tbl_SellingPrices " & _
              "SET SellingPrice = " & Me!Text2 & _
              " WHERE SellingPriceStockCode = [COLOR=Red][B]'[/B][/COLOR]" & Forms!Frm_GRV!GRVStockCode & "[COLOR=Red][B]'[/B][/COLOR] AND SellingPriceDateTo = " &[COLOR=Red][B] Format([/B][/COLOR]Me!Text3, [COLOR=Red][B]"\#mm\/dd\/yyyy\#")[/B][/COLOR] & ";"
        db.Execute sql

        If db.RecordsAffected = 0 Then
            sql = "INSERT INTO Tbl_SellingPrices " _
                  & "(SellingPriceStockCode, SellingPrice, SellingPriceDateFrom, SellingPriceDateTo) VALUES " _
                  & "([COLOR=Red][B]'[/B][/COLOR]" & Forms!Frm_GRV!GRVStockCode & "[COLOR=Red][B]'[/B][/COLOR], " & Me!Text2 & ", " & [COLOR=Red][B]Format([/B][/COLOR]Date(),[COLOR=Red][B] "\#mm\/dd\/yyyy\#")[/B][/COLOR] & ", " & [COLOR=Red][B]Format([/B][/COLOR]Me!Text3,[COLOR=Red][B] "\#mm\/dd\/yyyy\#") [/B][/COLOR]& ");"
            db.Execute sql
        End If
End Select
 
pr2-eugin said:
Code: 
Select Case FirstGRV
    Case "Y"
        sql = "UPDATE Tbl_SellingPrices " & _
              "SET SellingPrice = " & Me!Text2 & ", SellingPriceDateFrom = " [COLOR=Red][B]Format([/B][/COLOR]Date(), [COLOR=Red][B]"\#mm\/dd\/yyyy\#")[/B][/COLOR] ", SellingPriceDateTo = " & [COLOR=Red][B]Format([/B][/COLOR]Me!Text3[COLOR=Red][B], "\#mm\/dd\/yyyy\#")[/B][/COLOR] & _
              " WHERE SellingPriceStockCod[COLOR="Red"]e =  F[/COLOR]orms!Frm_GRV!GRVStockCod[COLOR="red"]e A[/COLOR]ND SellingPriceDateFrom = #01/01/1900#;"
        db.Execute sql[COLOR="red"], dbFailOnError[/COLOR]
Click to expand...
I keep saying that you should pass your Forms!FormName!ControlName as a parameter in the SQL, don't pass it as a value. And I also mentioned about using dbFailOnError. Both amended and highlighted in red.
 
And do the same for Date() too. Put it back inside the SQL statement, don't pass the value and do all that formatting.
 
vbaInet said:
I keep saying that you should pass your Forms!FormName!ControlName as a parameter in the SQL, don't pass it as a value. And I also mentioned about using dbFailOnError. Both amended and highlighted in red.
Click to expand...
I am confused. If this is passed as a parameter over values, would it not hit a runtime error 3061 - Too few parameters. Expected 1 or 2 something like that? When using SQL in VBA?

If you are using DoCmd.OpenQuery (which could be another alternative), I think what you are doing would be fine as it will pick up the values. :confused:

In regards to this,
vbaInet said:
And do the same for Date() too. Put it back inside the SQL statement, don't pass the value and do all that formatting.
Click to expand...
In VBA does it not matter of the Date Format? Access expects it to be in MM/DD/YYYY regardless of the regional settings? :confused:
 
Remember you're not opening a recordset so you won't have the too few parameters error. You're passing a valid UPDATE statement for execution. Passing it as a parameter allows Access to work out the right data type to use so you won't need to fiddle with that bit.

As for passing the Date() function, the same principle applies. You pass it inside the SQL statement as a function (and not it's interpreted value) and Access knows exactly what to do with it, i.e. it knows to use the correct date format to use. If you pass a date value then it's necessary to format.

Does it make sense?
 
Thanks a million.
The SQL statements now execute like they should and the correct SQL is executed according to the criteria.

Once again, thank you very much.:D
 
I understand what you are trying to say, but a quick test throws it out.

attachment.php


Am I doing something wrong?
 

Attachments

  • fewPara.png
    fewPara.png
    32.8 KB · Views: 164
On second thoughts you might be right there pr2-eugin. So I stand corrected! What I was thinking about is when you're passing it through the query, so using something like RunSQL. I did tell you it's been ages I touched this stuff.

But functions like Date() definitely pass within the SQL.
 
You must log in or register to reply here.

Similar threads

G
Solved Use saved Access query in Sql server statement
Replies
8
Views
1,267
Gaztry80
G
D
How to use a SQL SELECT statement with Access VBA
Replies
6
Views
3,026
Pat Hartman
P
A
Run-time error 2342 - A RunSQL action requires an argument consisting of an SQL Statement
Replies
6
Views
1,383
Ashfaque
A
G
Solved Problem in ADO, with .AddNew to a remote DB
Replies
5
Views
1,239
Guidon Mendoza
G
K
Solved MS Access Query of remote Database pulling partial data
2
Replies
26
Views
3,819
poliadisa
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom