Adding code to an existing COMMAND BUTTON

[kealaz]

I have a command button that I am using on a form [frmPO_ISSUE]. This
command button is currently closing the form and opening a report. The
report is based on a query and the query is run using the strCriteria (which
comes from a text box on the form). The code is below. All of this is
working so far.


*********************************************
Private Sub ISSUE_PO_Click()
On Error GoTo Err_ISSUE_PO_Click

Const conREPORTNAME = "rptPO"
Dim strCriteria As String

strCriteria = " P_O_NO = """ & Forms("frmPO_ISSUE").P_O_NO & """"

' close form
DoCmd.Close acForm, "frmPO_ISSUE"

' open report in print preview
DoCmd.OpenReport conREPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria

Exit_ISSUE_PO_Click:
Exit Sub

Err_ISSUE_PO_Click:
MsgBox Err.Description
Resume Exit_ISSUE_PO_Click

End Sub
***********************************************



I want to add the following code to the above command button. I have tried,
and it's not working. I have verified that the SQL query works. I just
don't know how to integrate it into the code I already have. The part that
it's not liking is "WhereCondition:=strCriteria" It works for opening
report/query to view the purchase order. I thought that I could use the same
method for another query. The SQL statement below is a delete query.
qryDeleteBUY_a is a select query.

******* code I want to add to the above ********

'save record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'run query
DoCmd.RunSQL "DELETE tblBUY.PART_NO FROM tblBUY WHERE PART_NO IN (SELECT
PART_NO FROM qryDeleteBUY_a);"
WhereCondition:=strCriteria

 

[BruceM]

If you are using Access 2000 or above you could make your life simpler by
saving a record thus:
Me.Dirty = False
I'm not sure exactly when Access got away from using only the horrid
DoMenuItem syntax, but it was at least Access 2000.

Regarding the SQL, the Where needs to be written into the SQL. You cannot
add a Where condition as with OpenForm.

Dim strSQL as String, strPO as String

strPO = Forms("frmPO_ISSUE").P_O_NO
or:
strPO = Forms!frmPO_ISSUE!P_O_NO
or if frmPO_Issue is the form on which the command button resides:
strPO = Me.P_O_NO

strSQL = "DELETE tblBUY.PART_NO FROM tblBUY WHERE PART_NO = " & _
strPO

DBEngine(0)(0).Execute strSQL, dbFailOnError

All of this assumes P_O_NO is text, which is what you have indicated by your
use of quotes in strCriteria.

The SQL is a suggestion. You would do best to create a delete query using
the SQL (substitute an actual value for strPO), run it, and see if you get
the expected results. I can't tell the details of your database from your
description. Is the current form based on tblBuy? If not, what is its
Record Source? BTW, are you sure you need to delete the record?

I have a command button that I am using on a form [frmPO_ISSUE]. This
command button is currently closing the form and opening a report. The
report is based on a query and the query is run using the strCriteria
(which
comes from a text box on the form). The code is below. All of this is
working so far.


*********************************************
Private Sub ISSUE_PO_Click()
On Error GoTo Err_ISSUE_PO_Click

Const conREPORTNAME = "rptPO"
Dim strCriteria As String

strCriteria = " P_O_NO = """ & Forms("frmPO_ISSUE").P_O_NO & """"

' close form
DoCmd.Close acForm, "frmPO_ISSUE"

' open report in print preview
DoCmd.OpenReport conREPORTNAME, _
View:=acViewPreview, _
WhereCondition:=strCriteria

Exit_ISSUE_PO_Click:
Exit Sub

Err_ISSUE_PO_Click:
MsgBox Err.Description
Resume Exit_ISSUE_PO_Click

End Sub
***********************************************



I want to add the following code to the above command button. I have
tried,
and it's not working. I have verified that the SQL query works. I just
don't know how to integrate it into the code I already have. The part
that
it's not liking is "WhereCondition:=strCriteria" It works for opening
report/query to view the purchase order. I thought that I could use the
same
method for another query. The SQL statement below is a delete query.
qryDeleteBUY_a is a select query.

******* code I want to add to the above ********

'save record
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'run query
DoCmd.RunSQL "DELETE tblBUY.PART_NO FROM tblBUY WHERE PART_NO IN (SELECT
PART_NO FROM qryDeleteBUY_a);"
WhereCondition:=strCriteria

 

[Tom van Stiphout]

On Wed, 15 Apr 2009 06:15:01 -0700, kealaz

Perhaps you mean:
dim sql as string
sql = "DELETE tblBUY.PART_NO FROM tblBUY WHERE PART_NO IN (SELECT
PART_NO FROM qryDeleteBUY_a) AND " & strCriteria
DoCmd.RunSQL sql

Personally I like to run a parameter query, rather than use inline
SQL.

-Tom.
Microsoft Access MVP

 

[kealaz]

Thanks Tom! This worked great!

 

[kealaz]

Thanks Bruce! I appreciate your post. I changed the save record code. Much
simpler.