Report Generating Form with Combo box

[Andrew Hollis]

The report as far as I can tell is okay. whenever I take away the totals
function in the query and specify no criteria, it produces a report that
includes all the changes for each individual regulation change. Whenever I
set the criteria for a certain regulation, the report outputs the revisions
for just that regulation like it should.

Sorting and grouping is set with RegulationNumber first, and Fed Approval
Date 2nd (like it should be)

Once I set it to a totals query, it prompts me every time i try to open the
report directly, or from the search form. it then outputs whatever was in
the query (which works okay if i specify a regulation instead of using
[Forms]![frmByReg].[RegComboBox], but it labels it whatever i put in the
"enter parameter" box even if what I put in there is nonsence.
I'm not sure what to check to fix the problem, and I'm not sure why the form
with the combo box lookup doesnt work.
looking at the control source on the Report seems good (Regulation Number).
Does it matter that whenever i use the Totals where in the query, it moves
that parameter to the end and won't show it?

This is in a report, isn't it? Look at the report itself. Have you perhaps
referred incorrectly to the field somewhere in the report? Don't forget to
check the Sorting and Grouping dialog.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I triple checked the spelling for the OK_Click code, the query, and the
combo
box name with no luck finding typos. Once again, the code I'm using for
the
click is:
Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.RegComboBox & "" <> "" Then
stDocName = "rptByReg "
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "You must select regulation for your reports.", vbOKOnly,
"Selection Error"
End If


Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click
End Sub
__________________

and setting my query as a totals query for "where" in Regulation Number
with
criteria [Forms]![frmByReg].[RegComboBox].

Double check the spelling. The error you're getting implies that you've
used
an incorrect field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Yeah. Its a table with just "RegulationNumber" and "RegulationName"

:

Does table Regulations have a field named RegulationNumber?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
With this I get a popup box asking to "enter parameter value" for
RegulationNumber. If I cut and past a valid RegulationNumber, I
still
end
up
with an empty form.
It seems like there'd be a simple way to have it simply append the
report
for the one regulation.

:

I don't see the point of the GROUP BY clause. If you're trying to
eliminate
duplicates, just use

SELECT DISTINCT Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate,
T_SIP_Revisions.SubmitalDate, T_SIP_Revisions.[FR Number],
T_SIP_Revisions.[SR Date], T_SIP_Revisions.Description
FROM T_SIP_Revisions INNER JOIN (Regulations INNER JOIN
Revisions_Regs
ON
Regulations.RegID = Revisions_Regs.JRegID) ON T_SIP_Revisions.REVID
=
Revisions_Regs.JRevID
WHERE Regulations.RegulationNumber=[Forms]![frmByReg].[RegComboBox]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
okay, here it is:
SELECT Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate,
T_SIP_Revisions.SubmitalDate, T_SIP_Revisions.[FR Number],
T_SIP_Revisions.[SR Date], T_SIP_Revisions.Description
FROM T_SIP_Revisions INNER JOIN (Regulations INNER JOIN
Revisions_Regs
ON
Regulations.RegID = Revisions_Regs.JRegID) ON
T_SIP_Revisions.REVID
=
Revisions_Regs.JRevID
WHERE
(((Regulations.RegulationNumber)=[Forms]![frmByReg].[RegComboBox]))
GROUP BY Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate,
T_SIP_Revisions.SubmitalDate, T_SIP_Revisions.[FR Number],
T_SIP_Revisions.[SR Date], T_SIP_Revisions.Description;

_____________________________________________

I've also tried:
SELECT Regulations.RegulationNumber, Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate, T_SIP_Revisions.SubmitalDate,
T_SIP_Revisions.[FR Number], T_SIP_Revisions.[SR Date],
T_SIP_Revisions.Description
FROM T_SIP_Revisions INNER JOIN (Regulations INNER JOIN
Revisions_Regs
ON
Regulations.RegID = Revisions_Regs.JRegID) ON
T_SIP_Revisions.REVID
=
Revisions_Regs.JRevID
WHERE
(((Regulations.RegulationNumber)=[Forms]![frmByReg].[RegComboBox]))
GROUP BY Regulations.RegulationNumber,
Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate, T_SIP_Revisions.SubmitalDate,
T_SIP_Revisions.[FR Number], T_SIP_Revisions.[SR Date],
T_SIP_Revisions.Description;
____________________
Which is only different in that I added a second "Regulation
Number"
criteria for the "Where" total.

It was a typo when I wrote Regualtion Number.

:

Chad's suggestion says "RegulationNumber", your reported error
message
says
"RegualtionNumber".

I really need to see the SQL of the query that the report's
using
(the
query
Chad worked on with you). To get to the SQL, open the query in
design
view,
then choose SQL View from the View menu.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


in
message
The field is good as far as I can tell.
I'm running:
Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.RegComboBox & "" <> "" Then
stDocName = "rptByReg "
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "You must select regulation for your reports.",
vbOKOnly,
"Selection Error"
End If


Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click
End Sub
---------------------------------------------
As suggested by Chad. With
Field: RegulationNumber
Table: Regulations
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]
in the reports query.

I've tried a few other things, and either get the error
message I
mentioned
earlier, or a prompt to select a parameter which will only
output
an
empty
report.


:

Did you mistype the name of the field? If not, what's the SQL
that's
being
run?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Andrew Hollis" <[email protected]>
wrote
in
message
Got it.
Though now whenever I run the code, I get the error "You
tried
to
execute
a
query that does not include the specified expression
'RegualtionNumber'
as
part of an aggregate function."
And it reorders my original query.



:

Hopefully you realize that that's supposed to be on a
single
line.

If that's not the problem, what's the "trouble" you've
been
having?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Andrew Hollis" <[email protected]>
wrote
in
message
Sorry for the late response (been on holiday), but I'm
having
trouble
with
the statement:

MsgBox "You must select regulation for your
reports.",vbOKOnly,
"Selection Error"

in the code you suggested.

:

Sorry posted before I was finished. You then would use
something
like
this in
your reports query so all the info is base on the

 

[Douglas J. Steele]

I'm very confused.

The last code you posted opens a report. Isn't that the code that causes the
popup box asking to "enter parameter value" for
RegulationNumber?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The report as far as I can tell is okay. whenever I take away the totals
function in the query and specify no criteria, it produces a report that
includes all the changes for each individual regulation change. Whenever
I
set the criteria for a certain regulation, the report outputs the
revisions
for just that regulation like it should.

Sorting and grouping is set with RegulationNumber first, and Fed Approval
Date 2nd (like it should be)

Once I set it to a totals query, it prompts me every time i try to open
the
report directly, or from the search form. it then outputs whatever was in
the query (which works okay if i specify a regulation instead of using
[Forms]![frmByReg].[RegComboBox], but it labels it whatever i put in the
"enter parameter" box even if what I put in there is nonsence.
I'm not sure what to check to fix the problem, and I'm not sure why the
form
with the combo box lookup doesnt work.
looking at the control source on the Report seems good (Regulation
Number).
Does it matter that whenever i use the Totals where in the query, it moves
that parameter to the end and won't show it?

This is in a report, isn't it? Look at the report itself. Have you
perhaps
referred incorrectly to the field somewhere in the report? Don't forget
to
check the Sorting and Grouping dialog.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I triple checked the spelling for the OK_Click code, the query, and the
combo
box name with no luck finding typos. Once again, the code I'm using
for
the
click is:
Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.RegComboBox & "" <> "" Then
stDocName = "rptByReg "
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "You must select regulation for your reports.", vbOKOnly,
"Selection Error"
End If


Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click
End Sub
__________________

and setting my query as a totals query for "where" in Regulation Number
with
criteria [Forms]![frmByReg].[RegComboBox].

:

Double check the spelling. The error you're getting implies that
you've
used
an incorrect field name.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
Yeah. Its a table with just "RegulationNumber" and "RegulationName"

:

Does table Regulations have a field named RegulationNumber?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
With this I get a popup box asking to "enter parameter value" for
RegulationNumber. If I cut and past a valid RegulationNumber, I
still
end
up
with an empty form.
It seems like there'd be a simple way to have it simply append
the
report
for the one regulation.

:

I don't see the point of the GROUP BY clause. If you're trying
to
eliminate
duplicates, just use

SELECT DISTINCT Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate,
T_SIP_Revisions.SubmitalDate, T_SIP_Revisions.[FR Number],
T_SIP_Revisions.[SR Date], T_SIP_Revisions.Description
FROM T_SIP_Revisions INNER JOIN (Regulations INNER JOIN
Revisions_Regs
ON
Regulations.RegID = Revisions_Regs.JRegID) ON
T_SIP_Revisions.REVID
=
Revisions_Regs.JRevID
WHERE
Regulations.RegulationNumber=[Forms]![frmByReg].[RegComboBox]


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


in
message
okay, here it is:
SELECT Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate,
T_SIP_Revisions.SubmitalDate, T_SIP_Revisions.[FR Number],
T_SIP_Revisions.[SR Date], T_SIP_Revisions.Description
FROM T_SIP_Revisions INNER JOIN (Regulations INNER JOIN
Revisions_Regs
ON
Regulations.RegID = Revisions_Regs.JRegID) ON
T_SIP_Revisions.REVID
=
Revisions_Regs.JRevID
WHERE
(((Regulations.RegulationNumber)=[Forms]![frmByReg].[RegComboBox]))
GROUP BY Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate,
T_SIP_Revisions.SubmitalDate, T_SIP_Revisions.[FR Number],
T_SIP_Revisions.[SR Date], T_SIP_Revisions.Description;

_____________________________________________

I've also tried:
SELECT Regulations.RegulationNumber,
Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate, T_SIP_Revisions.SubmitalDate,
T_SIP_Revisions.[FR Number], T_SIP_Revisions.[SR Date],
T_SIP_Revisions.Description
FROM T_SIP_Revisions INNER JOIN (Regulations INNER JOIN
Revisions_Regs
ON
Regulations.RegID = Revisions_Regs.JRegID) ON
T_SIP_Revisions.REVID
=
Revisions_Regs.JRevID
WHERE
(((Regulations.RegulationNumber)=[Forms]![frmByReg].[RegComboBox]))
GROUP BY Regulations.RegulationNumber,
Regulations.RegulationName,
T_SIP_Revisions.FedApprovalDate, T_SIP_Revisions.SubmitalDate,
T_SIP_Revisions.[FR Number], T_SIP_Revisions.[SR Date],
T_SIP_Revisions.Description;
____________________
Which is only different in that I added a second "Regulation
Number"
criteria for the "Where" total.

It was a typo when I wrote Regualtion Number.

:

Chad's suggestion says "RegulationNumber", your reported
error
message
says
"RegualtionNumber".

I really need to see the SQL of the query that the report's
using
(the
query
Chad worked on with you). To get to the SQL, open the query
in
design
view,
then choose SQL View from the View menu.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Andrew Hollis" <[email protected]>
wrote
in
message
The field is good as far as I can tell.
I'm running:
Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

If Me.RegComboBox & "" <> "" Then
stDocName = "rptByReg "
DoCmd.OpenReport stDocName, acPreview
Else
MsgBox "You must select regulation for your
reports.",
vbOKOnly,
"Selection Error"
End If


Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click
End Sub
---------------------------------------------
As suggested by Chad. With
Field: RegulationNumber
Table: Regulations
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]
in the reports query.

I've tried a few other things, and either get the error
message I
mentioned
earlier, or a prompt to select a parameter which will only
output
an
empty
report.


:

Did you mistype the name of the field? If not, what's the
SQL
that's
being
run?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Andrew Hollis" <[email protected]>
wrote
in
message
Got it.
Though now whenever I run the code, I get the error "You
tried
to
execute
a
query that does not include the specified expression
'RegualtionNumber'
as
part of an aggregate function."
And it reorders my original query.



:

Hopefully you realize that that's supposed to be on a
single
line.

If that's not the problem, what's the "trouble" you've
been
having?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"Andrew Hollis"
<[email protected]>
wrote
in
message
Sorry for the late response (been on holiday), but
I'm
having
trouble
with
the statement:

MsgBox "You must select regulation for your
reports.",vbOKOnly,
"Selection Error"

in the code you suggested.

:

Sorry posted before I was finished. You then would
use
something
like
this in
your reports query so all the info is base on the

 

[Andrew Hollis]

I applied the code to a form that has a combo box that the user can select a
regulation, and click ok, and a report is opened that just has that
regulations revisions.
the popup window is an unwanted bug that happens when I try to open the
report from the form or when I just try to open the report directly in the
database.

 

[Andrew Hollis]

Is there a way to do this without making the query a totals query.
If I don't make it a totals query and just set
[Forms]![frmByReg].[RegComboBox] into the criteria of the RegulationNumber
box, and I open the report directly, the popup window asks me to enter the
parameter value for "Forms!frmByReg.RegComboBox". When I open it using the
form, I don't get a popup, but only get a blank report...like its not
recognizing what was selected in the combo box.

 

[Douglas J. Steele]

If you get that popup window regardless of how you open the report, then
it's almost definite that you have an invalid reference somewhere in your
report. Pay attention to the exact wording in the caption of the popup
window, then search through your report for any use of that object.

I'm afraid I don't understand your other question about not making it a
Totals query.

 

[Andrew Hollis]

With the totals query thing, I mean to set it so I dont have to specify a
"totals" function (right click the field and deselect the totals option in
regular design view)

 

[Douglas J. Steele]

Remember that I joined this thread part way through it. Looking back through
the thread, I see absolutely no reference to a Totals query, so I have no
idea what you're doing.