using a form parameters to open a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i am trying to use parameters from a form to open a report. i have used the
information found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx to do this.
however, i was getting an error, it pops up a printing dialog box for a
second then pops up a modal box saying something like "this cannot be carried
out while processing a form or report open event." i changed a couple of
lines of code in the command button on the forms click event procedure. new
code is
Private Sub cmdDateSumQuery_Click()
On Error GoTo Err_cmdDateSumQuery_Click

Dim stDocName As String

stDocName = "rptDateRangeSums"
DoCmd.OpenReport stDocName, acViewPreview

Exit_cmdDateSumQuery_Click:
Exit Sub

Err_cmdDateSumQuery_Click:
MsgBox Err.Description
Resume Exit_cmdDateSumQuery_Click

End Sub

below is the code for my report

Option Compare Database

Private Sub Report_Close()
DoCmd.Close acForm, "frmDateRangeSums"
End Sub

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open frmDateRangeSums Dialog
DoCmd.OpenForm "frmDateRangeSums", , , , , acDialog


' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmDateRangeSums") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub

and below is my code for the module

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(frmDateRangeSums)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

the acCurViewDesign line is coming up an error saying that the
acCurViewDesign is a "Variable not defined" but the line
"Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean" is
highlighted in yellow.

any help here would be awesome and greatly appreciated. thanks

([email protected])
 
I take it you mean you want to use a form to specify report criteria. If so,
the way I have done this is to have something like this as the report's On
Open event:
DoCmd.OpenForm "frmSpecify", , , , , acDialog
frmSpecify has Pop Up and Modal set to Yes. After entering the criteria, a
command button on the form has Me.Visible = False as its On Click event.
With the form hidden the report finishes opening.
Back to the report, which has as its record source a parameter query. The
criteria for, say, name might be something like:
[Forms]![frmSpecify]![cboName]
cboName is a combo box from which Name is selected, but it could be
anything. The report's On close event needs to include closing frmSpecify.
I think that's it. Another approach is to open the form, then open the
report from there. I think MS suggest something of the sort. I tried both
ways, and prefer my method, but I no longer remember just why.
 
thank you for responding. i have actually gotten this to work by remarking a
few lines in the module coding to get them to not take place. however now
there is a problem in the sum boxes i have included in the report footer.

i have the report setup to sum the totalparts sorted and the total defects
between the two dates entered by the user on the form. if i copy and paste
the same records from the database into an excell spreadsheet, then use the
sum function for the column containing the total sorted parts, for the range
i am using i am copying over three less records to the excell sheet, but my
total on the excell sheet is less than the reports total by approximately
350,000.

i thought that maybe because the database table is not sorted by date but by
an autogenerated id number, that the query i am using is taking the first
instance of the start date and the last instance of the end date and then
everything in between. but this is not the case.

i am now thinking that because the defects and the defect codes are in
another table that references the autogenerated id from the first table, that
for each defect for that one part (can have several defect codes and
quantities per part, therefore several records in second table) the report is
adding the same number of total parts for that record to the total again.
e.g. part A total sorted 100 parts defect code a 2 parts, defect code b 3
parts, defect code c 4 parts. instead of total sorted being 100, it is now
400. 100 for the initial value and then adding another 100 per defect code.

if it is, how do i stop this behavior?

Bruce said:
I take it you mean you want to use a form to specify report criteria. If so,
the way I have done this is to have something like this as the report's On
Open event:
DoCmd.OpenForm "frmSpecify", , , , , acDialog
frmSpecify has Pop Up and Modal set to Yes. After entering the criteria, a
command button on the form has Me.Visible = False as its On Click event.
With the form hidden the report finishes opening.
Back to the report, which has as its record source a parameter query. The
criteria for, say, name might be something like:
[Forms]![frmSpecify]![cboName]
cboName is a combo box from which Name is selected, but it could be
anything. The report's On close event needs to include closing frmSpecify.
I think that's it. Another approach is to open the form, then open the
report from there. I think MS suggest something of the sort. I tried both
ways, and prefer my method, but I no longer remember just why.

jkendrick75 said:
i am trying to use parameters from a form to open a report. i have used the
information found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx to do this.
however, i was getting an error, it pops up a printing dialog box for a
second then pops up a modal box saying something like "this cannot be carried
out while processing a form or report open event." i changed a couple of
lines of code in the command button on the forms click event procedure. new
code is
Private Sub cmdDateSumQuery_Click()
On Error GoTo Err_cmdDateSumQuery_Click

Dim stDocName As String

stDocName = "rptDateRangeSums"
DoCmd.OpenReport stDocName, acViewPreview

Exit_cmdDateSumQuery_Click:
Exit Sub

Err_cmdDateSumQuery_Click:
MsgBox Err.Description
Resume Exit_cmdDateSumQuery_Click

End Sub

below is the code for my report

Option Compare Database

Private Sub Report_Close()
DoCmd.Close acForm, "frmDateRangeSums"
End Sub

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open frmDateRangeSums Dialog
DoCmd.OpenForm "frmDateRangeSums", , , , , acDialog


' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmDateRangeSums") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub

and below is my code for the module

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(frmDateRangeSums)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

the acCurViewDesign line is coming up an error saying that the
acCurViewDesign is a "Variable not defined" but the line
"Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean" is
highlighted in yellow.

any help here would be awesome and greatly appreciated. thanks

([email protected])
Click to expand...
Click to expand...
 
OK, maybe it's because this is Friday, but I can't quite sort out what you
are saying. I'm not getting a picture of your database structure and table
relationships. You say the user enters dates on a form. What form? Is it a
form just for entering query parameters, or is it your database's main form?
If it is the main form, on what table (or query) is it based?
I think my main question has to do with how data gets into the tables. Is a
new record generated whenever a defect is noted? If so, tblDefectLog (or
whatever you call it) is one table, linked to a parts table, with each part
being linked to a reject code or codes from a third table, or something like
that. Can each defect report (by which I mean a record in the tblDefectLog)
contain more than one part, or is each record for a single part. If each
part can be associated with many records and each record of defects can
contain only one part, there is a one-to-many relationship between parts and
defect records. If each defect record can be associated with many parts and
each part can be associated with many defect records (for instance, say that
part number 12345 is problematic, and has defects fairly often), there is a
many-to-many relationship. So you see why an accurate answer requires a more
complete picture of the database.
I would suggest that you make a copy of the database and empty it of all
records of defects, then add a few sample records in such a way that you know
exactly how many defects, etc. should appear in the total. Database tables
and spreadsheets resemble each other on the surface, but they function very
differently. You need to know which one is giving you the correct answer,
and we can take it from there.

jkendrick75 said:
thank you for responding. i have actually gotten this to work by remarking a
few lines in the module coding to get them to not take place. however now
there is a problem in the sum boxes i have included in the report footer.

i have the report setup to sum the totalparts sorted and the total defects
between the two dates entered by the user on the form. if i copy and paste
the same records from the database into an excell spreadsheet, then use the
sum function for the column containing the total sorted parts, for the range
i am using i am copying over three less records to the excell sheet, but my
total on the excell sheet is less than the reports total by approximately
350,000.

i thought that maybe because the database table is not sorted by date but by
an autogenerated id number, that the query i am using is taking the first
instance of the start date and the last instance of the end date and then
everything in between. but this is not the case.

i am now thinking that because the defects and the defect codes are in
another table that references the autogenerated id from the first table, that
for each defect for that one part (can have several defect codes and
quantities per part, therefore several records in second table) the report is
adding the same number of total parts for that record to the total again.
e.g. part A total sorted 100 parts defect code a 2 parts, defect code b 3
parts, defect code c 4 parts. instead of total sorted being 100, it is now
400. 100 for the initial value and then adding another 100 per defect code.

if it is, how do i stop this behavior?

Bruce said:
I take it you mean you want to use a form to specify report criteria. If so,
the way I have done this is to have something like this as the report's On
Open event:
DoCmd.OpenForm "frmSpecify", , , , , acDialog
frmSpecify has Pop Up and Modal set to Yes. After entering the criteria, a
command button on the form has Me.Visible = False as its On Click event.
With the form hidden the report finishes opening.
Back to the report, which has as its record source a parameter query. The
criteria for, say, name might be something like:
[Forms]![frmSpecify]![cboName]
cboName is a combo box from which Name is selected, but it could be
anything. The report's On close event needs to include closing frmSpecify.
I think that's it. Another approach is to open the form, then open the
report from there. I think MS suggest something of the sort. I tried both
ways, and prefer my method, but I no longer remember just why.

jkendrick75 said:
i am trying to use parameters from a form to open a report. i have used the
information found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx to do this.
however, i was getting an error, it pops up a printing dialog box for a
second then pops up a modal box saying something like "this cannot be carried
out while processing a form or report open event." i changed a couple of
lines of code in the command button on the forms click event procedure. new
code is
Private Sub cmdDateSumQuery_Click()
On Error GoTo Err_cmdDateSumQuery_Click

Dim stDocName As String

stDocName = "rptDateRangeSums"
DoCmd.OpenReport stDocName, acViewPreview

Exit_cmdDateSumQuery_Click:
Exit Sub

Err_cmdDateSumQuery_Click:
MsgBox Err.Description
Resume Exit_cmdDateSumQuery_Click

End Sub

below is the code for my report

Option Compare Database

Private Sub Report_Close()
DoCmd.Close acForm, "frmDateRangeSums"
End Sub

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open frmDateRangeSums Dialog
DoCmd.OpenForm "frmDateRangeSums", , , , , acDialog


' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmDateRangeSums") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub

and below is my code for the module

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(frmDateRangeSums)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

the acCurViewDesign line is coming up an error saying that the
acCurViewDesign is a "Variable not defined" but the line
"Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean" is
highlighted in yellow.

any help here would be awesome and greatly appreciated. thanks

([email protected])
Click to expand...
Click to expand...
Click to expand...
 
ok no prob,

i am using a form to gather a start date and an end date as parameters for
the report.
on the report i am separating each item by its part number and trying to get
a total of parts sorted and the total of defects for that part number between
the two dates. then i am trying to get an overall total for the total sorted
and total defects for all parts between the two dates. the query i am using
to give me information and total sorted and total defects per part for the
date range is:

SELECT Sum([TBL defect count].TotalSort) AS sumoftotalsort,
Sum(tblDefects.DefQuantity) AS sumofdefquantity, [TBL defect count].[Part
Number]
FROM [TBL defect count] LEFT JOIN tblDefects ON [TBL defect count].ID =
tblDefects.ID
WHERE ((([TBL defect count].Date) Between
[forms]![frmDateRangeSums]![StartDate] And
[forms]![frmDateRangeSums]![EndDate]))
GROUP BY [TBL defect count].[Part Number];

the sum fumctions in the report footer is
=Sum([sumoftotalsort]) and =Sum([sumofdefquantity])
and they are not a running sum.

for a table design there are two tables that have a one to many relationship

tblDefectCount has -
ID - auto generated key
Date
PartNum
totalsort - total parts sorted for the part number

tblDefects has -
autoID - auto generated key
ID - matches the id number from tblDefectCount
defcode
defquantity

i am using a form tied to tblDefectCount to input the partnumber, date, and
total sorted, and then a subform tied to tblDefects to enter in the defcode
and defQuantity. the user fills in the information for the partnumber, date,
and totalsorted, and then tabs over to a textbox on the subform to enter in
the defcode and defquantity. everytime the user presses the enter key after
entering the defcode and defquantity info, the subform creates a record in
the tblDefects table. so entering in a date and then a part number of 12345
on the main form with a total parts sorted being 100 (there would be 1 record
in tblDefectCount), then entering a defect code of 1 and then a quantity of
5, pressing enter will clear the subform and allow them to enter in a new
defect code and def quantity. if they enter in code 2 and quantity 2, then
there will be two records in the tblDefects table for the one record in
tblDefectCount.

i have printed out the report and the same range in the database and the
excell sheet, and have gotten the same numbers on the report for total sorted
by adding the total sort number to itself for that instance for each defect
code for that instance. example: part 1 shows up 10 times during a month as
having been sorted. each time has a different amount being sorted, and each
time there was a different amount of defect codes generated for the part. in
the first instance, there were 20 parts sorted and there were 2 defect codes.
code 1 with 5 defects and code 2 with 3 defects. what is happening is the
sum is showing that after this one time there were 40 parts sorted and 16
defects when it should show 20 parts sorted and 8 defects.

again, thanks for the reply and if you need any thing else to better
understand the situation, let me know. if you have seen any other bulletins
that show how to correct this that would be great, as i have not seen any
that will work in my situtation.

Bruce said:
OK, maybe it's because this is Friday, but I can't quite sort out what you
are saying. I'm not getting a picture of your database structure and table
relationships. You say the user enters dates on a form. What form? Is it a
form just for entering query parameters, or is it your database's main form?
If it is the main form, on what table (or query) is it based?
I think my main question has to do with how data gets into the tables. Is a
new record generated whenever a defect is noted? If so, tblDefectLog (or
whatever you call it) is one table, linked to a parts table, with each part
being linked to a reject code or codes from a third table, or something like
that. Can each defect report (by which I mean a record in the tblDefectLog)
contain more than one part, or is each record for a single part. If each
part can be associated with many records and each record of defects can
contain only one part, there is a one-to-many relationship between parts and
defect records. If each defect record can be associated with many parts and
each part can be associated with many defect records (for instance, say that
part number 12345 is problematic, and has defects fairly often), there is a
many-to-many relationship. So you see why an accurate answer requires a more
complete picture of the database.
I would suggest that you make a copy of the database and empty it of all
records of defects, then add a few sample records in such a way that you know
exactly how many defects, etc. should appear in the total. Database tables
and spreadsheets resemble each other on the surface, but they function very
differently. You need to know which one is giving you the correct answer,
and we can take it from there.

jkendrick75 said:
thank you for responding. i have actually gotten this to work by remarking a
few lines in the module coding to get them to not take place. however now
there is a problem in the sum boxes i have included in the report footer.

i have the report setup to sum the totalparts sorted and the total defects
between the two dates entered by the user on the form. if i copy and paste
the same records from the database into an excell spreadsheet, then use the
sum function for the column containing the total sorted parts, for the range
i am using i am copying over three less records to the excell sheet, but my
total on the excell sheet is less than the reports total by approximately
350,000.

i thought that maybe because the database table is not sorted by date but by
an autogenerated id number, that the query i am using is taking the first
instance of the start date and the last instance of the end date and then
everything in between. but this is not the case.

i am now thinking that because the defects and the defect codes are in
another table that references the autogenerated id from the first table, that
for each defect for that one part (can have several defect codes and
quantities per part, therefore several records in second table) the report is
adding the same number of total parts for that record to the total again.
e.g. part A total sorted 100 parts defect code a 2 parts, defect code b 3
parts, defect code c 4 parts. instead of total sorted being 100, it is now
400. 100 for the initial value and then adding another 100 per defect code.

if it is, how do i stop this behavior?

Bruce said:
I take it you mean you want to use a form to specify report criteria. If so,
the way I have done this is to have something like this as the report's On
Open event:
DoCmd.OpenForm "frmSpecify", , , , , acDialog
frmSpecify has Pop Up and Modal set to Yes. After entering the criteria, a
command button on the form has Me.Visible = False as its On Click event.
With the form hidden the report finishes opening.
Back to the report, which has as its record source a parameter query. The
criteria for, say, name might be something like:
[Forms]![frmSpecify]![cboName]
cboName is a combo box from which Name is selected, but it could be
anything. The report's On close event needs to include closing frmSpecify.
I think that's it. Another approach is to open the form, then open the
report from there. I think MS suggest something of the sort. I tried both
ways, and prefer my method, but I no longer remember just why.

:

i am trying to use parameters from a form to open a report. i have used the
information found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx to do this.
however, i was getting an error, it pops up a printing dialog box for a
second then pops up a modal box saying something like "this cannot be carried
out while processing a form or report open event." i changed a couple of
lines of code in the command button on the forms click event procedure. new
code is
Private Sub cmdDateSumQuery_Click()
On Error GoTo Err_cmdDateSumQuery_Click

Dim stDocName As String

stDocName = "rptDateRangeSums"
DoCmd.OpenReport stDocName, acViewPreview

Exit_cmdDateSumQuery_Click:
Exit Sub

Err_cmdDateSumQuery_Click:
MsgBox Err.Description
Resume Exit_cmdDateSumQuery_Click

End Sub

below is the code for my report

Option Compare Database

Private Sub Report_Close()
DoCmd.Close acForm, "frmDateRangeSums"
End Sub

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open frmDateRangeSums Dialog
DoCmd.OpenForm "frmDateRangeSums", , , , , acDialog


' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmDateRangeSums") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub

and below is my code for the module

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(frmDateRangeSums)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

the acCurViewDesign line is coming up an error saying that the
acCurViewDesign is a "Variable not defined" but the line
"Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean" is
highlighted in yellow.

any help here would be awesome and greatly appreciated. thanks

([email protected])
Click to expand...
Click to expand...
Click to expand...
 
Bruce.
I have a form open in the On Open event in my report.

I think I need to add to my form is a "Close" or "Cancel" button.
If a user open the report the form appears but I have no way for the user to
cancel the report. I have a button on the form with docmd.close frmABC and
dcmd.close rptABC. I get an error message: "This action can't be run while
processing a form or report event."

Any ideas??

Thanks,
Lynn Pennington.

Bruce said:
I take it you mean you want to use a form to specify report criteria. If so,
the way I have done this is to have something like this as the report's On
Open event:
DoCmd.OpenForm "frmSpecify", , , , , acDialog
frmSpecify has Pop Up and Modal set to Yes. After entering the criteria, a
command button on the form has Me.Visible = False as its On Click event.
With the form hidden the report finishes opening.
Back to the report, which has as its record source a parameter query. The
criteria for, say, name might be something like:
[Forms]![frmSpecify]![cboName]
cboName is a combo box from which Name is selected, but it could be
anything. The report's On close event needs to include closing frmSpecify.
I think that's it. Another approach is to open the form, then open the
report from there. I think MS suggest something of the sort. I tried both
ways, and prefer my method, but I no longer remember just why.

jkendrick75 said:
i am trying to use parameters from a form to open a report. i have used the
information found at
http://office.microsoft.com/en-us/assistance/HA011170771033.aspx to do this.
however, i was getting an error, it pops up a printing dialog box for a
second then pops up a modal box saying something like "this cannot be carried
out while processing a form or report open event." i changed a couple of
lines of code in the command button on the forms click event procedure. new
code is
Private Sub cmdDateSumQuery_Click()
On Error GoTo Err_cmdDateSumQuery_Click

Dim stDocName As String

stDocName = "rptDateRangeSums"
DoCmd.OpenReport stDocName, acViewPreview

Exit_cmdDateSumQuery_Click:
Exit Sub

Err_cmdDateSumQuery_Click:
MsgBox Err.Description
Resume Exit_cmdDateSumQuery_Click

End Sub

below is the code for my report

Option Compare Database

Private Sub Report_Close()
DoCmd.Close acForm, "frmDateRangeSums"
End Sub

Private Sub Report_Open(Cancel As Integer)
' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open frmDateRangeSums Dialog
DoCmd.OpenForm "frmDateRangeSums", , , , , acDialog


' Cancel Report if User Clicked the Cancel Button
If IsLoaded("frmDateRangeSums") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub

and below is my code for the module

Option Compare Database
Option Explicit
Public bInReportOpenEvent As Boolean ' Is report in the Open event?

Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Dim oAccessObject As AccessObject

Set oAccessObject = CurrentProject.AllForms(frmDateRangeSums)
If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

the acCurViewDesign line is coming up an error saying that the
acCurViewDesign is a "Variable not defined" but the line
"Function IsLoaded(ByVal frmDateRangeSums As String) As Boolean" is
highlighted in yellow.

any help here would be awesome and greatly appreciated. thanks

([email protected])
Click to expand...
Click to expand...
 
Back
Top