Solved CurrentDB.Execute Syntax (2 Viewers)

[evictme]

Hey all,

Im trying to include the following code in a _Onclick event

CurrentDb.Execute ("UPDATE Client SET Client.CaseMgrFax = " & Me.Fax_Number & " WHERE Client.CCID = '" & Me.ID & "'")

The event is for Form that includes information that should be updated in another table whenever there is an update/change. Not sure if the .Execute is the best way to do this...I was trying to use a rs.update but that does not do ALL the records with the matching ID (See below). Any advice or suggestions for best doing this would be appreciated! Thank you.

 DoCmd.SetWarnings False
          
           Set rs = CurrentDb.OpenRecordset("SELECT * FROM Client")
            
            rs.FindFirst ("CCID = " & Me.ID)
              If Not rs.NoMatch Then
                rs.Edit
                rs.[CaseManager] = Me.Case_Coordinator
                rs.[CCID] = Me.ID
                rs.[ServiceArea] = Me.Region
                rs.[CMPhone] = Me.Phone_Number
                rs.[CaseMgrFax] = Me.Fax_Number
                rs.[CaseCoordEmail] = Me.Email
                rs.Update
                rs.Close
              
              End If
              
            Set rs = Nothing
            
       DoCmd.SetWarnings True

 

[Isaac]

I think Execute seems like a fine way to do this. Are you having any particular problem with it? Is CCID column numeric? If so you need to drop those single quotes surrounding it.

You can leave the parenthesis off of Execute

 

[evictme]

I think Execute seems like a fine way to do this. Are you having any particular problem with it? Is CCID column numeric? If so you need to drop those single quotes surrounding it.

You can leave the parenthesis off of Execute

the whole reason I came here...Yes. The CCID is numeric. It was erroring because of the extra single quotes...Thank you Isaac!

 

[Isaac]

the whole reason I came here...Yes. The CCID is numeric. It was erroring because of the extra single quotes...Thank you Isaac!

Sounds like you got it working - glad to hear!

 

[The_Doc_Man]

.I was trying to use a rs.update but that does not do ALL the records with the matching ID

The reason is that when you do something via SQL and an UPDATE query, you do all matching members of the set at once (in effect if not in fact). When you do a recordset you are attempting to do one at a time. Your code MIGHT have worked the way you wanted if you had built a loop into the logic of using the recordset. BUT the SQL is faster and (to my way of thinking) easier.

 

[evictme]

I think Execute seems like a fine way to do this. Are you having any particular problem with it? Is CCID column numeric? If so you need to drop those single quotes surrounding it.

You can leave the parenthesis off of Execute

Im trying to do multiple fields but I keep getting a Syntax error. Am I missing an operator/single quotes?

CurrentDb.Execute "UPDATE Client SET Client.CaseManger = " & Me.Case_Coordinator & " and Client.CCID = " & Me.ID & " and Client.CMPhone = " & Me.Phone_Number & " and Client.ServiceArea = " & Me.Region & " and  Client.CaseMgrFax = " & Me.Fax_Number & " and  Client.CaseCoordEmail = " & Me.Email & " WHERE Client.CCID = " & Me.ID & ""

 

[evictme]

The reason is that when you do something via SQL and an UPDATE query, you do all matching members of the set at once (in effect if not in fact). When you do a recordset you are attempting to do one at a time. Your code MIGHT have worked the way you wanted if you had built a loop into the logic of using the recordset. BUT the SQL is faster and (to my way of thinking) easier.

Yea, I realized the loop part just after I made the execute code. I think the execute might work better for me, if I can figure out the syntax

 

[Isaac]

What column type is Client.CaseManger? Is it spelled right?

Remember, you DO need single quotes for all the text fields. Do those form control values (like Me.Region, Me.Fax_Number) already have the single quotes? (probably not)

 

[cheekybuddha]

The SET arguments should be separated by commas. Also, you can't qualify the fields in an UPDATE statement:

CurrentDb.Execute "UPDATE Client " & _
                  "SET CaseManger = '" & Me.Case_Coordinator & "', " & _
                  "    CMPhone = '" & Me.Phone_Number & "', " & _
                  "    ServiceArea = '" & Me.Region & "', " & _
                  "    CaseMgrFax = '" & Me.Fax_Number & "', " & _
                  "    CaseCoordEmail = '" & Me.Email & "' " & _
                  "WHERE CCID = " & Me.ID & ";", _
                  dbFailOnError

I've assumed all the fields are string fields. If not, remove the single quotes from around the appropriate value.

The dbFailOnError argument will give you a trappable error if anything goes wrong.

hth,

d

 

[evictme]

What column type is Client.CaseManger? Is it spelled right?

Remember, you DO need single quotes for all the text fields. Do those form control values (like Me.Region, Me.Fax_Number) already have the single quotes? (probably not)

I corrected the misspelling. Working on the single quotes for the fields...

 

[evictme]

The SET arguments should be separated by commas. Also, you can't qualify the fields in an UPDATE statement:

CurrentDb.Execute "UPDATE Client " & _
                  "SET CaseManger = '" & Me.Case_Coordinator & "', " & _
                  "    CMPhone = '" & Me.Phone_Number & "', " & _
                  "    ServiceArea = '" & Me.Region & "', " & _
                  "    CaseMgrFax = '" & Me.Fax_Number & "', " & _
                  "    CaseCoordEmail = '" & Me.Email & "' " & _
                  "WHERE CCID = " & Me.ID & ";", _
                  dbFailOnError

I've assumed all the fields are string fields. If not, remove the single quotes from around the appropriate value.

The dbFailOnError argument will give you a trappable error if anything goes wrong.

hth,

d

Thank you. This works perfect (after the spelling correction). Clearly needed some outside perspective, was working on this all morning

 

[Isaac]

Thank you. This works perfect (after the spelling correction). Clearly needed some outside perspective, was working on this all morning

Happens to everyone

 

[Isaac]

Also, you can't qualify the fields in an UPDATE statement:

Good catch ... I didn't even notice that.

 

[cheekybuddha]

Thank you. This works perfect (after the spelling correction).

Glad you got it working! I'm pleased we all could help.

It's often useful to build the SQL into a string variable first, which will give you the opportunity to inspect it if necessary.
eg

Dim strSQL As String

strSQL = "UPDATE Client " & _
         "SET CaseManager = '" & Me.Case_Coordinator & "', " & _
         "    CMPhone = '" & Me.Phone_Number & "', " & _
         "    ServiceArea = '" & Me.Region & "', " & _
         "    CaseMgrFax = '" & Me.Fax_Number & "', " & _
         "    CaseCoordEmail = '" & Me.Email & "' " & _
         "WHERE CCID = " & Me.ID & ";"
       
Debug.Print strSQL  ' Remove or comment out after development when you are happy with the SQL

CurrentDb.Execute strSQL, dbFailOnError

hth,

d

 

[NauticalGent]

Seems trivial at this point, but I don't think you need toggle Set Warnings when using the CurrentDb.Execute method...