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

Kamis, 27 Oktober 2011

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

Thanks John, although I had to play with it a little, I did get one button
to work perfectly. I'll place the other two buttons on the form that opens
the report sometime later today, and I suspect I'll have no problem.
Man-oh-man! The more I learn about Access, the more I fall deeper in love
with.

Have a great day today and an even finer one tomorrow.

Sincerely,

David

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John Viescas
Sent: Wednesday, October 26, 2011 2:37 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of
djsdaddy531
Sent: Wednesday, October 26, 2011 8:06 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.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

[Non-text portions of this message have been removed]