Pass field values into SQL w/VBA... 1

[mmaginniss]

I am trying to take the values a user would type into two text fields and plug those two values into a SQL update query. I am trying to do this using variables. Does anyone know where I might be going wrong... Thx. Here's my code:

' Take the value of txtAddOne Begin and
' txtAddOneStop and plug it into the SQL
' update query

Private Sub cmdDoIt_Click()
Dim intValueOne As Integer
Dim intValueTwo As Integer
intValueOne = Me!txtAddOneBegin.Value
intValueTwo = Me!txtAddOneStop.Value

DoCmd.RunSQL "UPDATE tblProjects " & _
"SET tblProjects.ProjPriority = [ProjPriority]+1 " & _
"Between 'intValueOne' And 'intValueTwo'; "
DoCmd.Requery
DoCmd.Close
End Sub

 

[RoyVidar]

Hi!

When using vba sql, one needs to ensure that the values of the references/variables are concatinated into the string, not the litterals:

[tt]DoCmd.RunSQL "UPDATE tblProjects " & _
"SET tblProjects.ProjPriority = [ProjPriority]+1 " & _
"Between " & intValueOne & " And " & intValueTwo[/tt]

By the names and declaration of your variables, no qualifiers are needed.

(single quotes are text qualifiers, hash (#) date qualifier)

But shouldn't there also be a "WHERE somefield..." before the Between line?

HTH Roy-Vidar

 

[mmaginniss]

Thanks, RoyVidar! And yes, I did need a WHERE CLAUSE. Here's the final code:

' Take the value of txtAddOne Begin and
' txtAddOneStop and plug it into the SQL
' update query

Private Sub cmdDoIt_Click()
Dim intValueOne As Integer
Dim intValueTwo As Integer
intValueOne = Me!txtAddOneBegin.Value
intValueTwo = Me!txtAddOneStop.Value

DoCmd.RunSQL "UPDATE tblProjects " & _
"SET tblProjects.ProjPriority = [ProjPriority]+1 " & _
"WHERE (((tblProjects.ProjPriority) Between " & intValueOne & " And " & intValueTwo & &quot); "
DoCmd.Requery
DoCmd.Close
End Sub

One last question. When I run this, I get the access prompt "You are about to Update 15 Rows. Do you wish to continue?" Anyway to avoid that prompt and just answer yes for the user?

 

[RoyVidar]

Hi!

Thanx for the star!

[tt]docmd.setwarnings false
DoCmd.RunSQL "UPDATE tblProjects...
docmd.setwarnings true[/tt]

Be sure not to forget the last one;-)

Roy-Vidar