Report Generating Form with Combo box

[Andrew Hollis]

I'm trying to make a form that appends a report based on the record chosen
from a combo box.
The combo box refers regulations, and choosing a regulation should generate
the form that that regulation along with a list all the revisions to the
regulations. I created the All Regulations report (rptByReg) the form, and
an on click code like this:

Private Sub OK_Click()
Dim strWhere As String

If Not IsNull(Me.MyComboBox) Then
strWhere = "[RegulationNumber] Like ""*" & Me.MyComboBox & "*"""
End If
DoCmd.OpenReport "rptByReg", acViewPreview, , strWhere
End Sub

Which works well for my keyword in Revisions search, but not here
apparently. What do I need to do to get it so it only shows the chosen
"RegulationNumber" on the form?

Thanks for any help!

 

[John Spencer]

Private Sub OK_Click()
Dim strWhere As String

If Not IsNull(Me.MyComboBox) Then
strWhere = "RegulationNumber = """ & Me.MyComboBox & """"
End If
DoCmd.OpenReport "rptByReg", acViewPreview, , strWhere
End Sub

I would try the above if your RegulationNumber is a text field. If
this does not work you might use a debug.print statement to see exactly
what you are passing.

If Not IsNull(Me.MyComboBox) Then
strWhere = "RegulationNumber = """ & Me.MyComboBox & """"
Debug.Print StrWhere
'Or use
'Msgbox strWhere,,"My search string"
End If


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Andrew Hollis]

My Regulation number is a text field, put the code you suggested only
produces an empty report for any regulation I chose. Not sure what the
problem is.

Private Sub OK_Click()
Dim strWhere As String

If Not IsNull(Me.MyComboBox) Then
strWhere = "RegulationNumber = """ & Me.MyComboBox & """"
End If
DoCmd.OpenReport "rptByReg", acViewPreview, , strWhere
End Sub

I would try the above if your RegulationNumber is a text field. If
this does not work you might use a debug.print statement to see exactly
what you are passing.

If Not IsNull(Me.MyComboBox) Then
strWhere = "RegulationNumber = """ & Me.MyComboBox & """"
Debug.Print StrWhere
'Or use
'Msgbox strWhere,,"My search string"
End If


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I'm trying to make a form that appends a report based on the record chosen
from a combo box.
The combo box refers regulations, and choosing a regulation should generate
the form that that regulation along with a list all the revisions to the
regulations. I created the All Regulations report (rptByReg) the form, and
an on click code like this:

Private Sub OK_Click()
Dim strWhere As String

If Not IsNull(Me.MyComboBox) Then
strWhere = "[RegulationNumber] Like ""*" & Me.MyComboBox & "*"""
End If
DoCmd.OpenReport "rptByReg", acViewPreview, , strWhere
End Sub

Which works well for my keyword in Revisions search, but not here
apparently. What do I need to do to get it so it only shows the chosen
"RegulationNumber" on the form?

Thanks for any help!

 

[Andrew Hollis]

Trying the Debug code only yielded an empty report as well.

What I'm trying to do is essentially make it so that pressing the "OK"
button places the selected Regulation from the combo box into the "Criteria"
part of the associated Query, and then run the report that's based on that
query, thereby limiting the output to just changes that deal with that
regulation.

Private Sub OK_Click()
Dim strWhere As String

If Not IsNull(Me.MyComboBox) Then
strWhere = "RegulationNumber = """ & Me.MyComboBox & """"
End If
DoCmd.OpenReport "rptByReg", acViewPreview, , strWhere
End Sub

I would try the above if your RegulationNumber is a text field. If
this does not work you might use a debug.print statement to see exactly
what you are passing.

If Not IsNull(Me.MyComboBox) Then
strWhere = "RegulationNumber = """ & Me.MyComboBox & """"
Debug.Print StrWhere
'Or use
'Msgbox strWhere,,"My search string"
End If


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

I'm trying to make a form that appends a report based on the record chosen
from a combo box.
The combo box refers regulations, and choosing a regulation should generate
the form that that regulation along with a list all the revisions to the
regulations. I created the All Regulations report (rptByReg) the form, and
an on click code like this:

Private Sub OK_Click()
Dim strWhere As String

If Not IsNull(Me.MyComboBox) Then
strWhere = "[RegulationNumber] Like ""*" & Me.MyComboBox & "*"""
End If
DoCmd.OpenReport "rptByReg", acViewPreview, , strWhere
End Sub

Which works well for my keyword in Revisions search, but not here
apparently. What do I need to do to get it so it only shows the chosen
"RegulationNumber" on the form?

Thanks for any help!

 

[Chad]

Private Sub OK_Click()
On Error GoTo Err_OK_Click

Dim stDocName As String
Dim stLinkCriteria As String

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

Exit_OK_Click:
Exit Sub

Err_OK_Click:
MsgBox Err.Description
Resume Exit_OK_Click
End Sub

 

[Chad]

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 selection you maade in your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Andrew Hollis]

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.

 

[Douglas J. Steele]

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!)

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 selection you maade in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Andrew Hollis]

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!)

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 selection you maade in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Douglas J. Steele]

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!)

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!)

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 selection you maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Andrew Hollis]

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!)

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!)


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 selection you maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Douglas J. Steele]

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!)

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!)

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!)


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 selection you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Andrew Hollis]

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!)

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!)


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!)


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 selection you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Douglas J. Steele]

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!)

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!)

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!)


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!)


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 selection you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Andrew Hollis]

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!)

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!)


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!)


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!)


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 selection you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Douglas J. Steele]

Does table Regulations have a field named RegulationNumber?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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!)

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!)


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!)


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!)


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 selection
you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Andrew Hollis]

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!)

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!)


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!)


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!)


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!)


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 selection
you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Douglas J. Steele]

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!)

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!)


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!)


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
selection
you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Andrew Hollis]

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!)


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!)


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!)


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
selection
you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]

 

[Douglas J. Steele]

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
selection
you
maade
in
your
forms MyComboBox.

Field: RegulationNumber
Table: Your Table
Total: Where
Criteria: [Forms]![Your Form Name].[MyComboBox]