Error when trying to create my Report

B

Bob Vance

On My Form ,When I select my Dates range and my owner I am getting these 3
Errors
It is creating my email to email, its not creating a report but no Owner or
Details on it
Thanks if you can help me................Bob
Actually might have happened when I upgraded froom 2000- to 2007
WindowsXp, Access 2007


Enter Parameter Value
Forms!frmBillStatement!cbOwner
Forms!frmBillStatement!tbDateFrom
Forms!frmBillStatement!tbDateTo

------------------------------------------------
Private Sub SendMailButton_Click()


On Error GoTo ErrorHandler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String Dim
msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As Integer

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"
----------------------------------------------------------------------------------------------
Case "OwnerStatement"

Me.Caption = "Owner Statement"

strSQL = "SELECT tblInvoice.OwnerID AS OwnerID," _
& " tblInvoice.OwnerName AS OwnerName," _
& " tblInvoice.InvoiceID, tblInvoice.InvoiceNo," _
& " tblInvoice.InvoiceDate, tblInvoice.HorseID," _
& " funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID) AS
HorseInfo," _
& " tblinvoice.OwnerPercent AS OwnerPercent,
tblinvoice.OwnerPercentAmount AS PercentAmount" _
& " FROM tblInvoice, tblHorseInfo" _
& " WHERE tblInvoice.HorseId=tblHorseInfo.HorseID " _
& " AND tblInvoice.OwnerID=" &
Forms!frmBillStatement![cbOwnerName] _
& " AND InvoiceDate >=" &
Format("Forms!frmBillStatement![tbDateFrom]", "mm/dd/yyyy") _
& " AND InvoiceDate <=" &
Format("Forms!frmBillStatement![tbDateTo]", "mm/dd/yyyy")
 
K

Ken Sheridan

Bob:

The fact that you are being prompted for the values of the three controls
suggests two things:

1. That the report is based on a query which references the controls as
parameters.
2. The form is not open when the report opens, or the parameters in the
query have the wrong name for the form.

However, you are also building an SQL statement in the code, which is not
necessary if you are already referencing the controls as parameters as the
behaviour your are experiencing suggests.

I'd suggest that you either base the form on a query which references the
controls as parameters, and omit the code building the SQL statement from the
SendMailButton_Click event procedure. The query would thus be like this:

PARAMETERS
Forms!frmBillStatement![cbOwnerName] LONG,
Forms!frmBillStatement![tbDateFrom] DATETIME,
Forms!frmBillStatement![tbDateTo] DATETIME;
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName,
tblInvoice.InvoiceID, tblInvoice.InvoiceNo,
tblInvoice.InvoiceDate, tblInvoice.HorseID,
funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID) AS HorseInfo,
tblinvoice.OwnerPercent, tblinvoice.OwnerPercentAmount
FROM tblInvoice, tblHorseInfo
WHERE tblInvoice.HorseId = tblHorseInfo.HorseID
AND tblInvoice.OwnerID = Forms!frmBillStatement![cbOwnerName]
AND InvoiceDate BETWEEN Forms!frmBillStatement![tbDateFrom]
AND Forms!frmBillStatement![tbDateTo];

A few things to note about the query:

1. The parameters are declared. This is particularly important with
date/time values which might otherwise, if entered in short date format, be
interpreted as arithmetical expressions and give the wrong results. I've
assumed that the OwnerID column is a long integer data type (most likely an
autonumber).

2. A BETWEEN….AND operation is used rather than separate >= and <=
operations (the result is the same).

3. There is no need to format the dates entered as declaring the parameters
will ensure they are interpreted correctly in accordance with the system's
local date format setting.

Having set the report's RecordSource property up like so you don't need to
build the SQL statement in the code, simply open the report from
frmBillStatement:

Const conREPORT = " rptOwnerPaymentMethod"

Select Case Me.OpenArgs
Case "OwnerStatement"
DoCmd.OpenReport conREPORT

The above prints the report. To preview it you'd use:

DoCmd.OpenReport conREPORT, View:=acViewPreview

If you want to close the frmBillStatement form automatically do so in the
Close event procedure of the rptOwnerPaymentMethod report so that the form
remains open and the controls accessible to the report when it runs.


Or, the alternative would be to base the report by default on a query (as an
SQL statement, not a saved query) without parameters:

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName,
tblInvoice.InvoiceID, tblInvoice.InvoiceNo,
tblInvoice.InvoiceDate, tblInvoice.HorseID,
funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID) AS HorseInfo,
tblinvoice.OwnerPercent, tblinvoice.OwnerPercentAmount
FROM tblInvoice, tblHorseInfo
WHERE tblInvoice.HorseId = tblHorseInfo.HorseID;

and pass the additional parts of the WHERE clause to the report via the
OpenArgs mechanism:

Const conREPORT = "rptOwnerPaymentMethod"
Dim strWhere As String

Select Case Me.OpenArgs
Case "OwnerStatement"
strWhere = _
" AND tblInvoice.OwnerID = " &
Forms!frmBillStatement![cbOwnerName] & _
" AND InvoiceDate BETWEEN #" & _
FORMAT(Forms!frmBillStatement![tbDateFrom],"mm/dd/yyyy") & "#" & _
" AND #" & _
FORMAT(Forms!frmBillStatement![tbDateFrom],"mm/dd/yyyy") & "#"

DoCmd.OpenReport conREPORT, OpenArgs:=strWhere

Or to preview it:

DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
OpenArgs:=strWhere

Note that in this case it is necessary to format the dates entered (to
internationalize it; it would work without if the dates are entered in a US
or otherwise internationally unambiguous format), and the values are
delimited with the # date delimiter character.

In the report's Open event procedure put:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Replace(Me.RecordSource, ";", "") & Me.OpenArgs
End If

The Replace function is called to remove the terminating semi-colon before
adding the additional parts of the WHERE clause (a different method would be
necessary if the query already included any other semi-colons of course, i.e.
within literal strings, using the Len function for instance). Note that in
this case you can close the form in the code if you wish immediately after
executing the line to open the report, and would not need to do so in the
report's module.

Ken Sheridan
Stafford, England

Bob Vance said:
On My Form ,When I select my Dates range and my owner I am getting these 3
Errors
It is creating my email to email, its not creating a report but no Owner or
Details on it
Thanks if you can help me................Bob
Actually might have happened when I upgraded froom 2000- to 2007
WindowsXp, Access 2007


Enter Parameter Value
Forms!frmBillStatement!cbOwner
Forms!frmBillStatement!tbDateFrom
Forms!frmBillStatement!tbDateTo

------------------------------------------------
Private Sub SendMailButton_Click()


On Error GoTo ErrorHandler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String Dim
msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As Integer

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"
----------------------------------------------------------------------------------------------
Case "OwnerStatement"

Me.Caption = "Owner Statement"

strSQL = "SELECT tblInvoice.OwnerID AS OwnerID," _
& " tblInvoice.OwnerName AS OwnerName," _
& " tblInvoice.InvoiceID, tblInvoice.InvoiceNo," _
& " tblInvoice.InvoiceDate, tblInvoice.HorseID," _
& " funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID) AS
HorseInfo," _
& " tblinvoice.OwnerPercent AS OwnerPercent,
tblinvoice.OwnerPercentAmount AS PercentAmount" _
& " FROM tblInvoice, tblHorseInfo" _
& " WHERE tblInvoice.HorseId=tblHorseInfo.HorseID " _
& " AND tblInvoice.OwnerID=" &
Forms!frmBillStatement![cbOwnerName] _
& " AND InvoiceDate >=" &
Format("Forms!frmBillStatement![tbDateFrom]", "mm/dd/yyyy") _
& " AND InvoiceDate <=" &
Format("Forms!frmBillStatement![tbDateTo]", "mm/dd/yyyy")
Click to expand...
 
B

Bob Vance

Thanks Ken, you have gone to so much trouble to explain to me how it should
have been done!. I must have deleted some code that was needed in the report
as I went back to a earlier version and imported the Report and replaced the
one that I had. That did the trick it is now going good. :)

Ken Sheridan said:
Bob:

The fact that you are being prompted for the values of the three controls
suggests two things:

1. That the report is based on a query which references the controls as
parameters.
2. The form is not open when the report opens, or the parameters in the
query have the wrong name for the form.

However, you are also building an SQL statement in the code, which is not
necessary if you are already referencing the controls as parameters as the
behaviour your are experiencing suggests.

I'd suggest that you either base the form on a query which references the
controls as parameters, and omit the code building the SQL statement from
the
SendMailButton_Click event procedure. The query would thus be like this:

PARAMETERS
Forms!frmBillStatement![cbOwnerName] LONG,
Forms!frmBillStatement![tbDateFrom] DATETIME,
Forms!frmBillStatement![tbDateTo] DATETIME;
SELECT tblInvoice.OwnerID, tblInvoice.OwnerName,
tblInvoice.InvoiceID, tblInvoice.InvoiceNo,
tblInvoice.InvoiceDate, tblInvoice.HorseID,
funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID) AS HorseInfo,
tblinvoice.OwnerPercent, tblinvoice.OwnerPercentAmount
FROM tblInvoice, tblHorseInfo
WHERE tblInvoice.HorseId = tblHorseInfo.HorseID
AND tblInvoice.OwnerID = Forms!frmBillStatement![cbOwnerName]
AND InvoiceDate BETWEEN Forms!frmBillStatement![tbDateFrom]
AND Forms!frmBillStatement![tbDateTo];

A few things to note about the query:

1. The parameters are declared. This is particularly important with
date/time values which might otherwise, if entered in short date format,
be
interpreted as arithmetical expressions and give the wrong results. I've
assumed that the OwnerID column is a long integer data type (most likely
an
autonumber).

2. A BETWEEN..AND operation is used rather than separate >= and <=
operations (the result is the same).

3. There is no need to format the dates entered as declaring the
parameters
will ensure they are interpreted correctly in accordance with the system's
local date format setting.

Having set the report's RecordSource property up like so you don't need to
build the SQL statement in the code, simply open the report from
frmBillStatement:

Const conREPORT = " rptOwnerPaymentMethod"

Select Case Me.OpenArgs
Case "OwnerStatement"
DoCmd.OpenReport conREPORT

The above prints the report. To preview it you'd use:

DoCmd.OpenReport conREPORT, View:=acViewPreview

If you want to close the frmBillStatement form automatically do so in the
Close event procedure of the rptOwnerPaymentMethod report so that the form
remains open and the controls accessible to the report when it runs.


Or, the alternative would be to base the report by default on a query (as
an
SQL statement, not a saved query) without parameters:

SELECT tblInvoice.OwnerID, tblInvoice.OwnerName,
tblInvoice.InvoiceID, tblInvoice.InvoiceNo,
tblInvoice.InvoiceDate, tblInvoice.HorseID,
funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID) AS HorseInfo,
tblinvoice.OwnerPercent, tblinvoice.OwnerPercentAmount
FROM tblInvoice, tblHorseInfo
WHERE tblInvoice.HorseId = tblHorseInfo.HorseID;

and pass the additional parts of the WHERE clause to the report via the
OpenArgs mechanism:

Const conREPORT = "rptOwnerPaymentMethod"
Dim strWhere As String

Select Case Me.OpenArgs
Case "OwnerStatement"
strWhere = _
" AND tblInvoice.OwnerID = " &
Forms!frmBillStatement![cbOwnerName] & _
" AND InvoiceDate BETWEEN #" & _
FORMAT(Forms!frmBillStatement![tbDateFrom],"mm/dd/yyyy") & "#"
& _
" AND #" & _
FORMAT(Forms!frmBillStatement![tbDateFrom],"mm/dd/yyyy") & "#"

DoCmd.OpenReport conREPORT, OpenArgs:=strWhere

Or to preview it:

DoCmd.OpenReport conREPORT, _
View:=acViewPreview, _
OpenArgs:=strWhere

Note that in this case it is necessary to format the dates entered (to
internationalize it; it would work without if the dates are entered in a
US
or otherwise internationally unambiguous format), and the values are
delimited with the # date delimiter character.

In the report's Open event procedure put:

If Not IsNull(Me.OpenArgs) Then
Me.RecordSource = Replace(Me.RecordSource, ";", "") & Me.OpenArgs
End If

The Replace function is called to remove the terminating semi-colon before
adding the additional parts of the WHERE clause (a different method would
be
necessary if the query already included any other semi-colons of course,
i.e.
within literal strings, using the Len function for instance). Note that
in
this case you can close the form in the code if you wish immediately after
executing the line to open the report, and would not need to do so in the
report's module.

Ken Sheridan
Stafford, England

Bob Vance said:
On My Form ,When I select my Dates range and my owner I am getting these
3
Errors
It is creating my email to email, its not creating a report but no Owner
or
Details on it
Thanks if you can help me................Bob
Actually might have happened when I upgraded froom 2000- to 2007
WindowsXp, Access 2007


Enter Parameter Value
Forms!frmBillStatement!cbOwner
Forms!frmBillStatement!tbDateFrom
Forms!frmBillStatement!tbDateTo

------------------------------------------------
Private Sub SendMailButton_Click()


On Error GoTo ErrorHandler

Dim lngID As Long, strMail As String, strBodyMsg As String, _
blEditMail As Boolean, sndReport As String, strCompany As String
Dim
msgPmt As String, msgBtns As Integer, msgTitle As String, msgResp As
Integer

Select Case Me.OpenArgs

Case "OwnerStatement"

sndReport = "rptOwnerPaymentMethod"
----------------------------------------------------------------------------------------------
Case "OwnerStatement"

Me.Caption = "Owner Statement"

strSQL = "SELECT tblInvoice.OwnerID AS OwnerID," _
& " tblInvoice.OwnerName AS OwnerName," _
& " tblInvoice.InvoiceID, tblInvoice.InvoiceNo," _
& " tblInvoice.InvoiceDate, tblInvoice.HorseID," _
& " funGetHorse(tblInvoice.InvoiceID,tblInvoice.HorseID)
AS
HorseInfo," _
& " tblinvoice.OwnerPercent AS OwnerPercent,
tblinvoice.OwnerPercentAmount AS PercentAmount" _
& " FROM tblInvoice, tblHorseInfo" _
& " WHERE tblInvoice.HorseId=tblHorseInfo.HorseID " _
& " AND tblInvoice.OwnerID=" &
Forms!frmBillStatement![cbOwnerName] _
& " AND InvoiceDate >=" &
Format("Forms!frmBillStatement![tbDateFrom]", "mm/dd/yyyy") _
& " AND InvoiceDate <=" &
Format("Forms!frmBillStatement![tbDateTo]", "mm/dd/yyyy")
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

Need to add 1 Field to query! 5
Open Report while creating email 1
No Error Message wanted 2
Add to text box String 4
Add 1 field to query 1
Case strFormat = acFormatRTF, Problem 5
Sending Report via Email 3
Format a text box! 1

Top