Referencing a variable in a SQL statement 1

[MsMope]

Good Morning,
I have a variable jobID which is set to a value within a recordset.

Set JobID = rs.Fields("Parent_ID")

Later on in the code I want to reference the value stored in the variable in a SQL statement such as:

strSQL = "UPDATE user_tblJobProp" & _
                  "SET user_tblJobProp.[Delivery_Charge]= '0'" & _
                  "WHERE user_tblJobProp.[ID]= [COLOR=red][i]jobID[/i][/color];"

I am not sure how to reference that variable.

 

[drctx]

if jobID is a number field then:

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

if it's text:

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

the chr(34) & jobID & chr(34) puts quotes around the jobID.

 

[MsMope]

Thanks,
Can you review the variable declaration on the top part of post, is it correct? I am recieving the following error

Compile Error: Object Required

The variable is declared globally as:

Public jobID as string

 

[drctx]

that's correct. it needs to go right right below the "Option Compare Database" statement.

 

[MsMope]

Excellent, one last thing and then I am done with you for the day, now that I have the syntax correct I need to test this update statment, I plan on using a select statement to make sure the correct record is being selected, this is the code that I have, I am recieving an error on the "FROM" clause

Dim strSQL As String
Dim strJobID As String
Dim db As Database
Dim rs As Recordset

jobID = "JOB46113"
   ' Select Case frmDelivery.Value
      'Case 1
    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")

I really appreciate the help, here is a star on me

 

[MsMope]

Ok why would this statement not work:

strSQL = "UPDATE user_tblJobProp" & _
                  "SET user_tblJobProp.[Delivery_Charge]= 1000 WHERE user_tblJobProp.[ID]=" & Chr(34) & jobID & Chr(34) & ";"

The field is not updating I don't understand.

 

[drctx]

it might be that you need a space in front of SET. or this might work: (make it all on one line.)

strSQL = "UPDATE user_tblJobProp SET user_tblJobProp.Delivery_Charge = 1000 WHERE (((user_tblJobProp.ID)=" & Chr(34) & jobID & Chr(34) & "));

 

[MsMope]

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

 

[drctx]

i set up a table called user_tblJobProp with the fields ID as text and Delivery_Charge as number. then i added your strSQL statement to a button an ran it worked like it should.

is it giving you any error messages? do you have the warnings set to false?

 

[MsMope]

No I don't have any warnings coming up at all, I verified that my fields were set to text/number and they are. It doesn't make any sense.

 

[MsMope]

OK I created an access tbl, and fields just as you listed about, I put the code behind the button , this is all the code behind the form:

  Option Compare Database

Private Sub btnBull_Click()
Dim strSQL As String
Dim jobID As String
jobID = "JOB0pbook"
strSQL = "UPDATE user_tblJobProp SET user_tblJobProp.Delivery_Charge = 1000 WHERE (((user_tblJobProp.ID)=" & Chr(34) & jobID & Chr(34) & "));"
On Error GoTo Err_btnBull_Click


    DoCmd.GoToRecord , , acNewRec

Exit_btnBull_Click:
    Exit Sub

Err_btnBull_Click:
    MsgBox Err.Description
    Resume Exit_btnBull_Click
    
End Sub

The record was not updated.

 

[drctx]

remove this line:
DoCmd.GoToRecord , , acNewRec

and replace it with:
DoCmd.RunSQL strSQL

to turn of the warnings about updating a records do it like this: (also add DoCmd.SetWarnings True beneath the exit sub statement)

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

 

[MsMope]

It is still not working in either my created dB or the original one. This is so frustrating.

 

[MsMope]

OK I reran and this is the immediate window results:

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

that is using the query you sent me

 

[drctx]

could you e-mail me the test db you created and i'll see if i can figure it out.

 

[MsMope]

I have resolved issues related to this, thank-you to everyone who contributed.