Runnign a QueryDef from CmdBtn on a form

[NoodNutt]

Hi all

I am fairly comfortably around standard nested queries, but I get in a world
of hurt when it comes to QueryDef's.

Can anyone assist with structuring the following please.

Private Sub CmdBtnNSW_Click()

Dim dbMyDB As Database, NSWQuery As QueryDef
Dim rsMyRS As Recordset, strMySQL As String
Dim myDateOut As Date

strmysql = "PARAMETERS DateOut date;" & _
"SELECT tblPupDetails.DateOut, tblPupDetails.DestState,
tblCustomers.CustName, tblPupDetails.QTY, tblType.TypeDesc,
tblPupDetails.Weight, tblDrivers.DriverName" & _
" FROM tblDrivers RIGHT JOIN (tblType RIGHT JOIN (tblPupDetails
INNER JOIN tblCustomers ON tblPupDetails.CustID = tblCustomers.CustID) ON
tblType.TypeID = tblPupDetails.Type) ON tblDrivers.DriverID =
tblPupDetails.DriverAllocated" & _
" WHERE (tblPupDetails.DateOut =[DateOut] AND
(tblPupDetails.DestState ="NSW"));"

Set dbMyDB = CurrentDb
Set NSWQuery = dbMyDB.CreateQueryDef("NSW Report", strMySQL)
NSWQuery.Parameters("DateOut") = Forms!frmLinehaul!SendDate
Set rsMyRS = NSWQuery.OpenRecordset()
rsMyRS.Close
dbMyDB.Close

End Sub

What I would like to happen is that in the linehaul form the user can select
indivual states to view a report indicating volumes for the particular day.

The initial parameter will match up with the date displayed on the Linehaul
form, the second parameter would filter out all DestStates that = "NSW".

Finally, I need to be able to access this generated QDef to display in a
report.

Any assistance is appreciated
TIA
Mark.

 

[Allen Browne]

You really don't need to create a QueryDef to do that, Mark.

To limit your form to just the one date, you can assign your SQL statement
to the forrm's RecordSource property:
Me.RecordSource = strmysql

(Or it might be even easier to just assign the WHERE clause to the form's
Filter.)

Similarly, you can OpenReport with a WhereCondtion to filter the report,
e.g.:

Private Sub cmdPreview_Click()
Dim strWhere As String
Dim lngLen As Long

If IsDate(Me.SendDate) Then
strWhere = "(DateOut = " & Format(Me.SendDate,
"\#mm\/dd\/yyyy\#") & ") AND "
End If
strWhere = strWhere & "(DestStates = 'NSW')"
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

 

[NoodNutt]

G'day Allen

Thx for your reply

I think I wasn't very clear in my explanation last night (put in a 16hr day
@ work) and was somewhat tired.

Unfortunately, the form in question "frmLinehaul" has no recordset, it is an
unbound form with a dozen CmdBtn's which the guy's in Linehaul will use to
view daily/ and up to 7 day future reports for the state they select.

So I have to create the recordset of "tblPupDetails" using the "txtSendDate"
as PARAMETER and "DestState" as WHERE on the fly, (rather than nested), then
use that recordset to populate the report, eg "rptNSWToday".

I have found in the past with other stand-alone DB's I have done (all-be-it
not MVP elite DB's) start to slow down too much with re-calculations of
nested queries, especiallywhen they become sizable.

The guy's in IT are hesitant of me using Access as they are concerned with
bogging the network down citing Access' perpensity to be resource hungry.

I have assured them that I will attempt to keep the DB as practicle as
possible, additionally, limiting the overall use of the DB to 3 terminals
only.

This is the first DB I have done where splitting, networking and alike has
become a factor and is uncharted for me, but I'm gonna have a crack at it.

Really appreciate any assistance.

Regards
Mark.

 

[Allen Browne]

Okay, the obvious options are:

a) Create a WhereCondition for OpenReport.

b) In the query that feeds the report, include criteria that reads the data
from your form, e.g.:
[Forms].[Form1].[txtSendDate]
in place of DateOut

 

[NoodNutt]

Thx again Allen

Have gone with your second suggestion.

Regards
Mark.