ACCESS FEED: RE: [MS_AccessPros] Can I alter the SQL using code?

Rabu, 26 Oktober 2011

RE: [MS_AccessPros] Can I alter the SQL using code?

David-

If you're using Access 2003 or later, you can pass the filter you want applied
to the subreport in the OpenArgs property. Then in the report Open or the
detail Format event, apply the filter to the subreport.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of djsdaddy531
Sent: Wednesday, October 26, 2011 8:06 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Can I alter the SQL using code?

Great Day

Great Day, I have a form that is used for a repair order at a automobile repair
shop. The customer comes in a lays out what is wrong with the car - brakes
squeal, gas smell, oil change, etc. When the service writer enters the repair
needs into the form (frmJobOrder), there are three categories that the service
writer can associate each repair item to: 1. Item part of original estimate; 2.
Item part of Repair Order; and 3. Item recommended by technician (Line item 3
results while the technician is doing the repair and then finds additional
things that need repair.

After the Repair Order screen has been completed, the service writer can print
an invoice by clicking "Print Invoice." The code behind the print invoice button
is enclosed. What I am trying to do is only print items that are part of the
repair order, which may or may not be part of the original estimate. I also want
to be able to print separate reports for items 1 and 3 above.

In the past, I've created three separate report to handle those three separate
reports where I would set the criteria in the query to print with a specific
criteria, but that doesn't seem to be an efficient way to approach to it. Is
there a way to alter the SQL to have the specific criteria for each report that
I want printed? If so, I have enclosed the SQL, and I would like for someone to
show me how it is done for the Invoice for the Repair Order only. The other two
seem like they would be the same application, so hopefully I can figure it out.
I am also enclosing he code for the click event

Oh the SQL is in a subreport named [rptRepairRepairOrder Subreport], plus I have
created the criteria in the query for the repair order report. I just do not
know how to change it dynamically with code.

SQL:
SELECT tblScopeOfJob.WorkRequestedID, tblScopeOfJob.JobId,
tblScopeOfJob.JobTypeID, tblScopeOfJob.WorkRequested,
tblScopeOfJob.WorkRequestedCost, tblScopeOfJob.Estimate,
tblScopeOfJob.RepairOrder, tblScopeOfJob.Recommended,
tblScopeOfJob.RemindInDays, tblScopeOfJob.RemindSentDate
FROM tblScopeOfJob
WHERE (((tblScopeOfJob.RepairOrder)=-1));

Click Event for Printing Invoice:
Private Sub btnOpenReport_Click()
On Error GoTo Err_btnOpenReport_Click

Dim stDocName As String
Dim stFilter As String
Dim stLinkCriteria

'set conditions
stDocName = "rptInvoice2"
stLinkCriteria = "[JobID]=" & Me.JobID
DoCmd.OpenReport ReportName:="rptInvoice2", _
View:=acViewPreview, _
WhereCondition:=stLinkCriteria

Exit_btnOpenReport_Click:
Exit Sub

Err_btnOpenReport_Click:
MsgBox Err.Description
Resume Exit_btnOpenReport_Click

End Sub

Thanks you in advance,

David

------------------------------------

Yahoo! Groups Links