Query as recordset

B

Brad

Thanks for taking the time to read my question.

I am opening a report, but want to check if there is data
for the report first. I have this working on another
report, but I am having troubles with my querydefn. I
get the error "Expected 2" when I run my code. I have
put the code and the SQL of the query below.

My question is: What is my second querydefn? I have
tried pasting the iif statement - IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) - from the
PurchaseStatusText field, but that doesn't seem to work.
The code can't find the field [tblPurchases]. If I take
that out, it can't find the field [PurchaseStatus].

I've also tried - IIf([qdf]![PurchaseStatus]=1,"Open",IIf
([qdf]![PurchaseStatus]=2,"Incomplete","Complete"))

Thanks again for the help.

Brad

CODE:


Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryProfitLoss")
qdf.Parameters(0).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]
qdf.Parameters(1).Value = 'Something Needs to go here
Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 0 Then

DoCmd.Close
MsgBox "No data available for this report." & Chr
(13) & Chr(13) & "You have tried to open the Profit Loss
Report. No Sales have occurred. Use this report when a
transaction has been fully or partially completed.", 48

End If
End Sub


SQL of Query:

SELECT tblPurchases.PurchaseTransactionID,
tblPurchases.PurchaseDate, tblPurchases.StockName,
tblPurchases.StockCode,
tblPurchases.TransactionCompletedBy,
tblPurchases.PurchaseAmount, tblPurchases.PurchasePrice,
tblPurchases.PurchaseFee, IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) AS
PurchaseStatusText, tblSales.SaleDate,
tblSales.SaleAmount, tblSales.SalePrice,
tblSales.SaleFee,
qryProfitLossAllTotals.ProfitLossAllTotals
FROM (tblPurchases INNER JOIN qryProfitLossAllTotals ON
tblPurchases.PurchaseTransactionID =
qryProfitLossAllTotals.qryProfitLossDLookupPreA.PurchaseTr
ansactionID) INNER JOIN tblSales ON
tblPurchases.PurchaseTransactionID =
tblSales.PurchaseTransactionID
WHERE (((tblPurchases.PurchaseDate) Between [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]))
ORDER BY tblPurchases.PurchaseDate,
tblPurchases.StockName;
 
A

Alex Dybenko

try
qdf.Parameters(0).Value = [Forms]![frmReportGenerator]![StartDate]
qdf.Parameters(1).Value = [Forms]![frmReportGenerator]![EndDate]


and instead
DoCmd.Close
you can just write:
cancel=true
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com



Brad said:
Thanks for taking the time to read my question.

I am opening a report, but want to check if there is data
for the report first. I have this working on another
report, but I am having troubles with my querydefn. I
get the error "Expected 2" when I run my code. I have
put the code and the SQL of the query below.

My question is: What is my second querydefn? I have
tried pasting the iif statement - IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) - from the
PurchaseStatusText field, but that doesn't seem to work.
The code can't find the field [tblPurchases]. If I take
that out, it can't find the field [PurchaseStatus].

I've also tried - IIf([qdf]![PurchaseStatus]=1,"Open",IIf
([qdf]![PurchaseStatus]=2,"Incomplete","Complete"))

Thanks again for the help.

Brad

CODE:


Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryProfitLoss")
qdf.Parameters(0).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]
qdf.Parameters(1).Value = 'Something Needs to go here
Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 0 Then

DoCmd.Close
MsgBox "No data available for this report." & Chr
(13) & Chr(13) & "You have tried to open the Profit Loss
Report. No Sales have occurred. Use this report when a
transaction has been fully or partially completed.", 48

End If
End Sub


SQL of Query:

SELECT tblPurchases.PurchaseTransactionID,
tblPurchases.PurchaseDate, tblPurchases.StockName,
tblPurchases.StockCode,
tblPurchases.TransactionCompletedBy,
tblPurchases.PurchaseAmount, tblPurchases.PurchasePrice,
tblPurchases.PurchaseFee, IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) AS
PurchaseStatusText, tblSales.SaleDate,
tblSales.SaleAmount, tblSales.SalePrice,
tblSales.SaleFee,
qryProfitLossAllTotals.ProfitLossAllTotals
FROM (tblPurchases INNER JOIN qryProfitLossAllTotals ON
tblPurchases.PurchaseTransactionID =
qryProfitLossAllTotals.qryProfitLossDLookupPreA.PurchaseTr
ansactionID) INNER JOIN tblSales ON
tblPurchases.PurchaseTransactionID =
tblSales.PurchaseTransactionID
WHERE (((tblPurchases.PurchaseDate) Between [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]))
ORDER BY tblPurchases.PurchaseDate,
tblPurchases.StockName;
Click to expand...
 
B

Brad

Thanks Alex!! It worked.

I am just not sure why.

Here is code from my other report. It works fine and I
did not split out the between start date and end date
like you suggested on the first report.

What is the difference? Why does it work on the one
report but not the other?

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

If FilterForOpenPurchases = True Then

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs
("qrytblPurchasesFilterForOpen")
qdf.Parameters(0).Value = "Open"

'HERE**********

qdf.Parameters(1).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]

'***************

Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 1 Then

Me.RecordSource = "qrytblPurchasesFilterForOpen"

Else
Forms!frmReportGenerator!chkJustOpenPurchases =
False
MsgBox "No data available for this report." & Chr
(13) & Chr(13) & "You have tried to open the Purchase
Report filtered for " & Chr(34) & "Open" & Chr(34) & "
transactions. " & Chr(13) & "The " & Chr(34) & "Just
Open Purchases" & Chr(34) & " check box has been
unchecked, and all data will be displayed for the date
range you have selected.", 48

End If

Else
Me.RecordSource = "qrytblPurchases"
End If
End Sub
-----Original Message-----
try
qdf.Parameters(0).Value = [Forms]![frmReportGenerator]! [StartDate]
qdf.Parameters(1).Value = [Forms]![frmReportGenerator]! [EndDate]


and instead
DoCmd.Close
you can just write:
cancel=true
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com



Thanks for taking the time to read my question.

I am opening a report, but want to check if there is data
for the report first. I have this working on another
report, but I am having troubles with my querydefn. I
get the error "Expected 2" when I run my code. I have
put the code and the SQL of the query below.

My question is: What is my second querydefn? I have
tried pasting the iif statement - IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) - from the
PurchaseStatusText field, but that doesn't seem to work.
The code can't find the field [tblPurchases]. If I take
that out, it can't find the field [PurchaseStatus].

I've also tried - IIf([qdf]![PurchaseStatus] =1,"Open",IIf
([qdf]![PurchaseStatus]=2,"Incomplete","Complete"))

Thanks again for the help.

Brad

CODE:


Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryProfitLoss")
qdf.Parameters(0).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]
qdf.Parameters(1).Value = 'Something Needs to go here
Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 0 Then

DoCmd.Close
MsgBox "No data available for this report." & Chr
(13) & Chr(13) & "You have tried to open the Profit Loss
Report. No Sales have occurred. Use this report when a
transaction has been fully or partially completed.", 48

End If
End Sub


SQL of Query:

SELECT tblPurchases.PurchaseTransactionID,
tblPurchases.PurchaseDate, tblPurchases.StockName,
tblPurchases.StockCode,
tblPurchases.TransactionCompletedBy,
tblPurchases.PurchaseAmount, tblPurchases.PurchasePrice,
tblPurchases.PurchaseFee, IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) AS
PurchaseStatusText, tblSales.SaleDate,
tblSales.SaleAmount, tblSales.SalePrice,
tblSales.SaleFee,
qryProfitLossAllTotals.ProfitLossAllTotals
FROM (tblPurchases INNER JOIN qryProfitLossAllTotals ON
tblPurchases.PurchaseTransactionID =
qryProfitLossAllTotals.qryProfitLossDLookupPreA.PurchaseTr
ansactionID) INNER JOIN tblSales ON
tblPurchases.PurchaseTransactionID =
tblSales.PurchaseTransactionID
WHERE (((tblPurchases.PurchaseDate) Between [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]))
ORDER BY tblPurchases.PurchaseDate,
tblPurchases.StockName;
Click to expand...


.
Click to expand...
 
P

Phil Hunt

I don't see the big picture of what you are doing. But there is a NoData
event for a Report object, can't you use that ?


Brad said:
Thanks Alex!! It worked.

I am just not sure why.

Here is code from my other report. It works fine and I
did not split out the between start date and end date
like you suggested on the first report.

What is the difference? Why does it work on the one
report but not the other?

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

If FilterForOpenPurchases = True Then

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs
("qrytblPurchasesFilterForOpen")
qdf.Parameters(0).Value = "Open"

'HERE**********

qdf.Parameters(1).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]

'***************

Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 1 Then

Me.RecordSource = "qrytblPurchasesFilterForOpen"

Else
Forms!frmReportGenerator!chkJustOpenPurchases =
False
MsgBox "No data available for this report." & Chr
(13) & Chr(13) & "You have tried to open the Purchase
Report filtered for " & Chr(34) & "Open" & Chr(34) & "
transactions. " & Chr(13) & "The " & Chr(34) & "Just
Open Purchases" & Chr(34) & " check box has been
unchecked, and all data will be displayed for the date
range you have selected.", 48

End If

Else
Me.RecordSource = "qrytblPurchases"
End If
End Sub
-----Original Message-----
try
qdf.Parameters(0).Value = [Forms]![frmReportGenerator]! [StartDate]
qdf.Parameters(1).Value = [Forms]![frmReportGenerator]! [EndDate]


and instead
DoCmd.Close
you can just write:
cancel=true
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com



Thanks for taking the time to read my question.

I am opening a report, but want to check if there is data
for the report first. I have this working on another
report, but I am having troubles with my querydefn. I
get the error "Expected 2" when I run my code. I have
put the code and the SQL of the query below.

My question is: What is my second querydefn? I have
tried pasting the iif statement - IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) - from the
PurchaseStatusText field, but that doesn't seem to work.
The code can't find the field [tblPurchases]. If I take
that out, it can't find the field [PurchaseStatus].

I've also tried - IIf([qdf]![PurchaseStatus] =1,"Open",IIf
([qdf]![PurchaseStatus]=2,"Incomplete","Complete"))

Thanks again for the help.

Brad

CODE:


Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryProfitLoss")
qdf.Parameters(0).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]
qdf.Parameters(1).Value = 'Something Needs to go here
Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 0 Then

DoCmd.Close
MsgBox "No data available for this report." & Chr
(13) & Chr(13) & "You have tried to open the Profit Loss
Report. No Sales have occurred. Use this report when a
transaction has been fully or partially completed.", 48

End If
End Sub


SQL of Query:

SELECT tblPurchases.PurchaseTransactionID,
tblPurchases.PurchaseDate, tblPurchases.StockName,
tblPurchases.StockCode,
tblPurchases.TransactionCompletedBy,
tblPurchases.PurchaseAmount, tblPurchases.PurchasePrice,
tblPurchases.PurchaseFee, IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) AS
PurchaseStatusText, tblSales.SaleDate,
tblSales.SaleAmount, tblSales.SalePrice,
tblSales.SaleFee,
qryProfitLossAllTotals.ProfitLossAllTotals
FROM (tblPurchases INNER JOIN qryProfitLossAllTotals ON
tblPurchases.PurchaseTransactionID =
qryProfitLossAllTotals.qryProfitLossDLookupPreA.PurchaseTr
ansactionID) INNER JOIN tblSales ON
tblPurchases.PurchaseTransactionID =
tblSales.PurchaseTransactionID
WHERE (((tblPurchases.PurchaseDate) Between [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]))
ORDER BY tblPurchases.PurchaseDate,
tblPurchases.StockName;
Click to expand...


.
Click to expand...
Click to expand...
 
B

Brad

I was not aware of that. Thanks for the tip!!

Brad
-----Original Message-----
I don't see the big picture of what you are doing. But there is a NoData
event for a Report object, can't you use that ?


Thanks Alex!! It worked.

I am just not sure why.

Here is code from my other report. It works fine and I
did not split out the between start date and end date
like you suggested on the first report.

What is the difference? Why does it work on the one
report but not the other?

Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

If FilterForOpenPurchases = True Then

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs
("qrytblPurchasesFilterForOpen")
qdf.Parameters(0).Value = "Open"

'HERE**********

qdf.Parameters(1).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]

'***************

Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 1 Then

Me.RecordSource = "qrytblPurchasesFilterForOpen"

Else
Forms!frmReportGenerator!chkJustOpenPurchases =
False
MsgBox "No data available for this report." & Chr
(13) & Chr(13) & "You have tried to open the Purchase
Report filtered for " & Chr(34) & "Open" & Chr(34) & "
transactions. " & Chr(13) & "The " & Chr(34) & "Just
Open Purchases" & Chr(34) & " check box has been
unchecked, and all data will be displayed for the date
range you have selected.", 48

End If

Else
Me.RecordSource = "qrytblPurchases"
End If
End Sub
-----Original Message-----
try
qdf.Parameters(0).Value = [Forms]!
Click to expand...
[frmReportGenerator]!
[StartDate]
qdf.Parameters(1).Value = [Forms]!
Click to expand...
[frmReportGenerator]!
[EndDate]
and instead
DoCmd.Close
you can just write:
cancel=true
--
Alex Dybenko (MVP)
http://Alex.Dybenko.com



Thanks for taking the time to read my question.

I am opening a report, but want to check if there is data
for the report first. I have this working on another
report, but I am having troubles with my querydefn. I
get the error "Expected 2" when I run my code. I have
put the code and the SQL of the query below.

My question is: What is my second querydefn? I have
tried pasting the iif statement - IIf ([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) - from the
PurchaseStatusText field, but that doesn't seem to work.
The code can't find the field [tblPurchases]. If I take
that out, it can't find the field [PurchaseStatus].

I've also tried - IIf([qdf]![PurchaseStatus] =1,"Open",IIf
([qdf]![PurchaseStatus]=2,"Incomplete","Complete"))

Thanks again for the help.

Brad

CODE:


Private Sub Report_Open(Cancel As Integer)
Dim dbs As Database, rst As Recordset
Dim qdf As QueryDef
Dim RecNum As Integer

RecNum = 0

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryProfitLoss")
qdf.Parameters(0).Value = [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]
qdf.Parameters(1).Value = 'Something Needs to go here
Set rst = qdf.OpenRecordset()

If Not rst.EOF Then
rst.MoveFirst
Do Until RecNum = 1
RecNum = RecNum + 1
rst.MoveNext
Loop
End If

rst.Close
Set rst = Nothing
Set dbs = Nothing

If RecNum = 0 Then

DoCmd.Close
MsgBox "No data available for this report."
Click to expand...
&
Chr
(13) & Chr(13) & "You have tried to open the Profit Loss
Report. No Sales have occurred. Use this report when a
transaction has been fully or partially completed.", 48

End If
End Sub


SQL of Query:

SELECT tblPurchases.PurchaseTransactionID,
tblPurchases.PurchaseDate, tblPurchases.StockName,
tblPurchases.StockCode,
tblPurchases.TransactionCompletedBy,
tblPurchases.PurchaseAmount, tblPurchases.PurchasePrice,
tblPurchases.PurchaseFee, IIf([tblPurchases]!
[PurchaseStatus]=1,"Open",IIf([tblPurchases]!
[PurchaseStatus]=2,"Incomplete","Complete")) AS
PurchaseStatusText, tblSales.SaleDate,
tblSales.SaleAmount, tblSales.SalePrice,
tblSales.SaleFee,
qryProfitLossAllTotals.ProfitLossAllTotals
FROM (tblPurchases INNER JOIN qryProfitLossAllTotals ON
tblPurchases.PurchaseTransactionID =
Click to expand...
qryProfitLossAllTotals.qryProfitLossDLookupPreA.PurchaseTr
ansactionID) INNER JOIN tblSales ON
tblPurchases.PurchaseTransactionID =
tblSales.PurchaseTransactionID
WHERE (((tblPurchases.PurchaseDate) Between [Forms]!
[frmReportGenerator]![StartDate] And [Forms]!
[frmReportGenerator]![EndDate]))
ORDER BY tblPurchases.PurchaseDate,
tblPurchases.StockName;



.
Click to expand...
Click to expand...


.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SQL pass thru query from code always propting for DSN 5
VBA error calling parameter query 2
IIf Query assistance 7
Execute make table query in vba 3
run a make table query using parameters in multi-select list box 7
Row and Column Averages in Dynamic Crosstab Report? 3
Excluding Weekends in Query 2
Dynamic crosstab report - need help!! 2

Top