Check value before running Update Query

[David]

Hello once again all,
On the form, I just want the user to "CHECK OUT" a record and have SQL
change the field "Status" from Available to OUT.
Running Access 2002, I am running the update query fine (2nd block of SQL) ,
but want to verify the value of the Status field before making the update. I
can do this in the update query, but I want to be able to tell if the update
query returns zero records so I can notify the user that the update was not
successful.
I've tried the docmd.openquery "qry_Name", but it doesn't allow me to notify
the user. I've tried ADO record set, but don't know ADO that well, and
couldn't get the multiple conditions of the WHERE clause figured out.
The error I receive with the code below is "Can Not Execute a Select Query"

If I execute the SQL code with the "DoCmd.RunSQL strSQL" line, ERROR of "A
RunSQL action requires an argument consisting of an SQL statement."

If someone can assist me, I would be grateful. I don't have much sanity left.

'**CODE START
On Error GoTo Err_lbl_Reserve_Click


Dim strSQL As String

'DoCmd.SetWarnings False

'Where clause is all on one LONG line until I can get it to work

strSQL = "SELECT tbl_Plans.DSPLY_CAT_NAME, tbl_Plans.DSPLY_CAT_NAME_1,
tbl_Plans.DSPLY_CAT_NAME_2, tbl_Plans.DSPLY_CAT_NAME_3, tbl_Plans.PLAN_NAME,
tbl_Plans.P2K_SVC_NAME, tbl_Plans.Status" & _
" FROM tbl_Plans" & _
" WHERE
(((tbl_Plans.DSPLY_CAT_NAME)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME])
AND
((tbl_Plans.DSPLY_CAT_NAME_1)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1])
AND
((tbl_Plans.DSPLY_CAT_NAME_2)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_2])
AND
((tbl_Plans.DSPLY_CAT_NAME_3)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_3])
AND ((tbl_Plans.PLAN_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME]) AND
((tbl_Plans.P2K_SVC_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME2]) AND
((tbl_Plans.Status)='Available'));"

' DoCmd.RunSQL strSQL
' Line above produces ERROR when not remarked out

DBEngine(0)(0).Execute strSQL, dbFailOnError
'Line above produces ERROR

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "NO RECORDS FOUND"
Exit Sub
Else
MsgBox "Records Found"
'the Where clause is all in one LONG line until I can get it to work

strSQL = "UPDATE tbl_Plans SET tbl_Plans.Status = 'Out'" & _
" WHERE (((tbl_Plans.Status)='Available') AND
((tbl_Plans.DSPLY_CAT_NAME)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME]) AND
((tbl_Plans.DSPLY_CAT_NAME_1)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1])
AND
((tbl_Plans.DSPLY_CAT_NAME_2)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_2])
AND
((tbl_Plans.DSPLY_CAT_NAME_3)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_3])
AND ((tbl_Plans.PLAN_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME]) AND
((tbl_Plans.P2K_SVC_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME2]));"

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End If
'** CODE END

 

[SteveS]

David,

Like the error message said "You can't Execute a Select Query".

So you need to open a recordset. Be sure and have a reference set to
Microsoft DAO 3.6 Object Library

Compare your "Where" clause to the code below. When you want to get a value
from a form, the Forms!FormName!ControlName needs to be outside of the quotes.
Otherwise you are comparing the string "Forms!FormName!ControlName" instead of
the value from "Forms!FormName!ControlName".

I assumed the values in the combo boxes are long integers, not text. If they
are text, then you will need to change the lines in strWhere that refer to
combo boxes to look like this (added two single quotes) :

" AND tbl_Plans.DSPLY_CAT_NAME_1 = '" &
[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1] & "'"


This is **AIR CODE** Watch for line wrap!!

'**CODE START *********************************

On Error GoTo Err_lbl_Reserve_Click

Dim strSQL As String, strWhere as String
Dim rst as DAO.Recordset
Dim RecFound as Boolean

'Where clause is all on one LONG line until I can get it to work
strWhere = " WHERE tbl_Plans.Status)='Available'" & _
" AND tbl_Plans.DSPLY_CAT_NAME = " &
[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME]
" AND tbl_Plans.DSPLY_CAT_NAME_1 = " &
[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1]
" AND tbl_Plans.DSPLY_CAT_NAME_2 = " &
[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_2]
" AND tbl_Plans.DSPLY_CAT_NAME_3 = " &
[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_3]
" AND tbl_Plans.PLAN_NAME = " & [Forms]![frm_Check_Out]![cbo_PLAN_NAME])
" AND tbl_Plans.P2K_SVC_NAME = " & [Forms]![frm_Check_Out]![cbo_PLAN_NAME2]
" ;"


strSQL = "SELECT tbl_Plans.DSPLY_CAT_NAME, tbl_Plans.DSPLY_CAT_NAME_1," & _
" tbl_Plans.DSPLY_CAT_NAME_2, tbl_Plans.DSPLY_CAT_NAME_3,
tbl_Plans.PLAN_NAME," & _
" tbl_Plans.P2K_SVC_NAME, tbl_Plans.Status" & _
" FROM tbl_Plans"

strSQL = strSQL & strWhere

' Open a recordset
set rst = currentdb.Openrecordset(strSQL)

' if at BOF and EOF then there are no records in the recordset
RecFound = Not (rst.BOF and rst.EOF)

rst.Close
Set rst = Nothing

If RecFound Then
MsgBox "NO RECORDS FOUND"
Exit Sub
Else
MsgBox "Records Found"
strSQL = "UPDATE tbl_Plans SET tbl_Plans.Status = 'Out'" & _

strSQL = strSQL & strWhere

CurrentDB.Execute strSQL, dbFailOnError

End If
'** CODE END *********************************



HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)

Hello once again all,
On the form, I just want the user to "CHECK OUT" a record and have SQL
change the field "Status" from Available to OUT.
Running Access 2002, I am running the update query fine (2nd block of SQL) ,
but want to verify the value of the Status field before making the update. I
can do this in the update query, but I want to be able to tell if the update
query returns zero records so I can notify the user that the update was not
successful.
I've tried the docmd.openquery "qry_Name", but it doesn't allow me to notify
the user. I've tried ADO record set, but don't know ADO that well, and
couldn't get the multiple conditions of the WHERE clause figured out.
The error I receive with the code below is "Can Not Execute a Select Query"

If I execute the SQL code with the "DoCmd.RunSQL strSQL" line, ERROR of "A
RunSQL action requires an argument consisting of an SQL statement."

If someone can assist me, I would be grateful. I don't have much sanity left.

'**CODE START
On Error GoTo Err_lbl_Reserve_Click


Dim strSQL As String

'DoCmd.SetWarnings False

'Where clause is all on one LONG line until I can get it to work

strSQL = "SELECT tbl_Plans.DSPLY_CAT_NAME, tbl_Plans.DSPLY_CAT_NAME_1,
tbl_Plans.DSPLY_CAT_NAME_2, tbl_Plans.DSPLY_CAT_NAME_3, tbl_Plans.PLAN_NAME,
tbl_Plans.P2K_SVC_NAME, tbl_Plans.Status" & _
" FROM tbl_Plans" & _
" WHERE
(((tbl_Plans.DSPLY_CAT_NAME)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME])
AND
((tbl_Plans.DSPLY_CAT_NAME_1)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1])
AND
((tbl_Plans.DSPLY_CAT_NAME_2)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_2])
AND
((tbl_Plans.DSPLY_CAT_NAME_3)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_3])
AND ((tbl_Plans.PLAN_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME]) AND
((tbl_Plans.P2K_SVC_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME2]) AND
((tbl_Plans.Status)='Available'));"

' DoCmd.RunSQL strSQL
' Line above produces ERROR when not remarked out

DBEngine(0)(0).Execute strSQL, dbFailOnError
'Line above produces ERROR

If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "NO RECORDS FOUND"
Exit Sub
Else
MsgBox "Records Found"
'the Where clause is all in one LONG line until I can get it to work

strSQL = "UPDATE tbl_Plans SET tbl_Plans.Status = 'Out'" & _
" WHERE (((tbl_Plans.Status)='Available') AND
((tbl_Plans.DSPLY_CAT_NAME)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME]) AND
((tbl_Plans.DSPLY_CAT_NAME_1)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_1])
AND
((tbl_Plans.DSPLY_CAT_NAME_2)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_2])
AND
((tbl_Plans.DSPLY_CAT_NAME_3)=[Forms]![frm_Check_Out]![cbo_DSPLY_CAT_NAME_3])
AND ((tbl_Plans.PLAN_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME]) AND
((tbl_Plans.P2K_SVC_NAME)=[Forms]![frm_Check_Out]![cbo_PLAN_NAME2]));"

DoCmd.RunSQL strSQL

DoCmd.SetWarnings True

End If
'** CODE END