SQL statement w/in VBA 1

[MsMope]

Good Morning,
The following is a transcript from the Access Forum I am hoping someone in this forum will be able to pinpoint the problem with the statement. The SQL statment is not updating the field.


MsMope (IS/IT--Manageme) Sep 16, 2004
Alright, here is the last mod to this, I found a module that gives me the current ID, works great, but it is still not updating:

strSQL = "UPDATE user_tblJobProp" & " " & _
                  "SET user_tblJobProp.[Delivery_Charge]= '1000'" & " " & _
                  "WHERE user_tblJobProp.[ID]= is_modoutline_getcurrentid();"

DRIVING ME BONKERS

 

[xlbo]

For reference, it would be helpful if you linked the thread that this came from - that could be anything without understanding the context

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[MsMope]

sure, tell me how to link it and I will

Thanks

 

[xlbo]

If you go back to the original thread, just above where it says "Forum" in pink on the title bar, it should have
Thread and then a number
underlined
simply copy that text and paste it into a new post and the link should automatically pop up as a hyperlink

Rgds, Geoff

Yesterday it worked. Today it is not working. Windows is like that.

Please read FAQ222-2244 before you ask a question

 

[MsMope]

http://www.tek-tips.com/viewthread.cfm?qid=918308

 

[MsMope]

thread705-918308

 

[MsMope]

In reference to this, how do I display what exactly is going on? as in how do I debug this, using the immediate window or a console.writeline??

 

[cLFlaVA]

what is this????

is_modoutline_getcurrentid()

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[MsMope]

that is an internal module set to get the current record ID. I used that instead of the local variable, but neither work.

 

[cLFlaVA]

Does this work?

strSQL = "UPDATE user_tblJobProp" & " " & _
         "SET user_tblJobProp.[Delivery_Charge]= '1000'" & " " & _
         "WHERE user_tblJobProp.[ID]= " & is_modoutline_getcurrentid() & ";"

??????

*cLFlaVA
----------------------------
Ham and Eggs walks into a bar and asks, "Can I have a beer please?"
The bartender replies, "I'm sorry, we don't serve breakfast.

 

[MsMope]

no, that did not update the record either......

strSQL = "UPDATE user_tblJobProp" & " " & _
  "SET user_tblJobProp.[Delivery_Charge]= '1000'" & " " & _
 "WHERE user_tblJobProp.[ID]= " & is_modoutline_getcurrentid() & ";"

 

[MsMope]

This is code from a different part of the form, I don't know if it will help, it hasn't helped me....

Private Sub ctlCheck_Layout_AfterUpdate()
     Dim db As Database, rs As Recordset, SQL$

    Set db = CurrentDb
    SQL$ = "SELECT user_tblJobChild.Parent_ID, user_tblJobChild.Child_Class From user_tblJobchild "
    SQL$ = SQL$ & "GROUP BY user_tblJobChild.Parent_ID, "
    SQL$ = SQL$ & "user_tblJobChild.Child_Class "
    SQL$ = SQL$ & "HAVING user_tblJobChild.Parent_ID = is_modoutline_getcurrentid()"

 

[cLFlaVA]

It doesn't help me either.
Add this below your original building of strSQL:

[tt]Debug.Print strSQL[/tt]

Make sure your Immediate window is diplayed.
Step through the code.
What is the string value of strSQL?

Then, do it again, but this time, use the query I supplied you with. What is the value then?

It's hard to help you when I have no idea what your function is_modoutline_getcurrentid() does.

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[MsMope]

This is what is displaying in the immediate window
303201904036804861410019200640364 I have no idea what that is.

The following code is what I am using just to test and try to get the field to update

strSQL = "UPDATE user_tblJobProp" & _
                  "SET user_tblJobProp.[Delivery_Charge]= '0'" & _
                  "WHERE user_tblJobProp.[ID]='" & jobID & "';"

I also tried to just run a select statement and display in the immediate window, I got the same line of numbers as before

jobID= JOB0pbook
Set db = CurrentDb
        'strSQL = "SELECT user_tblJobProp.[ID] FROM user_tblJobProp WHERE user_tblJobProp.[ID]= " & Chr(34) & jobID & Chr(34) & ";"
        'Set rs = db.OpenRecordset(strSQL)
        'strJobID = rs.Fields("ID")

 

[MsMope]

I reran the query, this is what displays in the immediate window

UPDATE user_tblJobProp SET user_tblJobProp.[Delivery_Charge]= 1000 WHERE user_tblJobProp.[ID]= JOB46113;

This is the query you sent me.

 

[cLFlaVA]

and does that work?

If not, you might want to alter it slightly:

strSQL = "UPDATE user_tblJobProp" & " " & _
         "SET user_tblJobProp.[Delivery_Charge]= '1000'" & " " & _
         "WHERE user_tblJobProp.[ID]= [red]'[/red]" & is_modoutline_getcurrentid() & "[red]'[/red];"

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[MsMope]

No, it didn't work, the field is still blank in the Db, and I changed it like you suggested, this is what I get in the immediate window.

UPDATE user_tblJobProp SET user_tblJobProp.[Delivery_Charge]= 1000 WHERE user_tblJobProp.[ID]= 'JOB46113';

which SHOULD work. maybe I have table set up wrong, the query can't be any more right, can it?

 

[cLFlaVA]

What kind of field (in the database) is DeliveryCharge? If, for some reason, it's text, you'll need to surround 1000 with single quotes also.

Since you're only using one table in the query, you can get rid of the prefixes.

Neater code:

strSQL = "UPDATE user_tblJobProp " & _
         "SET Delivery_Charge = '1000' " & _
         "WHERE ID = '" & is_modoutline_getcurrentid() & "';"

Also, are you positive of the field names "ID" and "Delivery_Charge" in the database?

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!

 

[MsMope]

yes Sir/Maam both fields are in the table, the ID is a text field and delivery charge is a number field.

 

[cLFlaVA]

Sir.

What are the following lines of code, then? Since your SQL statement is correct, maybe you're not properly utilizing the SQL.

Also, when you Debug.Print the SQL statement, copy it, and run it from your database directly, does it operate as expected?

*cLFlaVA
----------------------------
When will I be able to see what other members can see about myself? It's been like, a freakin' month already!