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?
"kealaz" <(E-Mail Removed)> wrote in message
news:7431C424-088F-4C67-A02A-(E-Mail Removed)...
>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
|
Similar Threads
