Help with opening a report

[Bob V]

I have a Report that has 4 fields one being [Category] on my Form I have a
drop down combo box with [Category] text listed only, What would I need on
my On Click or After Update in my combo Box so as if I click Apples on my
Combo Box my report [rptCategory] will show all records that Apples is in
Category
Thanks for any Help...................Bob

 

[Allen Browne]

Use the OpenReport action to open the report.

OpenReport accepts a WhereCondition string.
You need to craft this so that it limits the report to the one category when
it opens.

Here's an example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

 

[Tom Wickerath]

Hi Bob,

You might want to try Access MVP Armen Stein's "Report Selection Techniques"
sample, available here:

http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

This is easily extensible, once you decide that you want to add additional
filtering capabilities. Yes, there is some VBA code to work through, but I
think you are up to the task of reverse engineering it, to see how it works.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Bob V]

Thanks Allen I tried to use this code in the After Update in My Combo box
but I am getting a yellow line error on
strWhere = "[Category] = """ & Me.[Category] & """"
Thanks for your help..............Bob

Private Sub cmbCategoryAll_AfterUpdate()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[Category] = """ & Me.[Category] & """"

DoCmd.OpenReport "rptCategoryAll", acViewPreview, , strWhere
End If

Use the OpenReport action to open the report.

OpenReport accepts a WhereCondition string.
You need to craft this so that it limits the report to the one category
when it opens.

Here's an example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a Report that has 4 fields one being [Category] on my Form I have a
drop down combo box with [Category] text listed only, What would I need on
my On Click or After Update in my combo Box so as if I click Apples on my
Combo Box my report [rptCategory] will show all records that Apples is in
Category
Thanks for any Help...................Bob

 

[Allen Browne]

Do you have a control named Category on that form?

If not, substitute your control name. Perhaps it's called Text99 or Combo72.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen I tried to use this code in the After Update in My Combo box
but I am getting a yellow line error on
strWhere = "[Category] = """ & Me.[Category] & """"
Thanks for your help..............Bob

Private Sub cmbCategoryAll_AfterUpdate()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[Category] = """ & Me.[Category] & """"

DoCmd.OpenReport "rptCategoryAll", acViewPreview, , strWhere
End If

Use the OpenReport action to open the report.

OpenReport accepts a WhereCondition string.
You need to craft this so that it limits the report to the one category
when it opens.

Here's an example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

I have a Report that has 4 fields one being [Category] on my Form I have
a drop down combo box with [Category] text listed only, What would I need
on my On Click or After Update in my combo Box so as if I click Apples on
my Combo Box my report [rptCategory] will show all records that Apples is
in Category
Thanks for any Help...................Bob

 

[Bob V]

Sorry Allen Category is a field in my report that I wanted to sort on, I was
going to use the On Click in my combo box to select the sort on Category
Thanks for your Help....Bob

Do you have a control named Category on that form?

If not, substitute your control name. Perhaps it's called Text99 or
Combo72.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen I tried to use this code in the After Update in My Combo box
but I am getting a yellow line error on
strWhere = "[Category] = """ & Me.[Category] & """"
Thanks for your help..............Bob

Private Sub cmbCategoryAll_AfterUpdate()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[Category] = """ & Me.[Category] & """"

DoCmd.OpenReport "rptCategoryAll", acViewPreview, , strWhere
End If

Use the OpenReport action to open the report.

OpenReport accepts a WhereCondition string.
You need to craft this so that it limits the report to the one category
when it opens.

Here's an example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

I have a Report that has 4 fields one being [Category] on my Form I have
a drop down combo box with [Category] text listed only, What would I
need on my On Click or After Update in my combo Box so as if I click
Apples on my Combo Box my report [rptCategory] will show all records
that Apples is in Category
Thanks for any Help...................Bob

 

[John W. Vinson]

Sorry Allen Category is a field in my report that I wanted to sort on, I was
going to use the On Click in my combo box to select the sort on Category
Thanks for your Help....Bob

Reports don't have fields. Tables and Queries have fields; a Report (or a
Form) will likely have a Control (textbox or other) bound to a field in that
object's Recordsource.

I wonder if you're making the common error of defining "sort". It sounds like
you want to *select* records based on a Category value. Sort means "to take a
set of records and put them in a specific order". Which do you want to do -
*select* by category, or *sort* by category?

John W. Vinson [MVP]

 

[Bob V]

Thanks John, Select by [Category] Only shows records in [Category] That eg:
"Travel"

 

[John W. Vinson]

Thanks John, Select by [Category] Only shows records in [Category] That eg:
"Travel"

Ummm... exactly.

That's what you asked for.

Apparently nobody here has really understood your question.

What, exactly, from a user perspective, do you want to do?

John W. Vinson [MVP]

 

[Bob V]

Thanks John. It might be easier if I show you my SQL Query:
SELECT DISTINCT tblRemarks.Category,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, tblRemarks.dtDate,
tblRemarks.HorseID, tblHorseInfo.Status, tblRemarks.Remark
FROM tblRemarks INNER JOIN tblHorseInfo ON tblRemarks.HorseID =
tblHorseInfo.HorseID
GROUP BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False),
tblRemarks.dtDate, tblRemarks.HorseID, tblHorseInfo.Status,
tblRemarks.Remark
HAVING (((tblHorseInfo.Status) Like 'Active*'))
ORDER BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False);
I have Drop down List from another Query on Category , I am trying show
Only records with"Apples" in [Category]and 3 other fields Name,dtDate,Remark
Thanks for helping ......Bob

Thanks John, Select by [Category] Only shows records in [Category] That
eg:
"Travel"

Ummm... exactly.

That's what you asked for.

Apparently nobody here has really understood your question.

What, exactly, from a user perspective, do you want to do?

John W. Vinson [MVP]

 

[John W. Vinson]

I have Drop down List from another Query on Category , I am trying show
Only records with"Apples" in [Category]and 3 other fields Name,dtDate,Remark
Thanks for helping ......Bob

Assuming that funGetHorse isn't involved in this; and that the drop down box
is a Combo Box named cboCategory and that it's on a form named MyForm (none of
which I know to be the case, you'll need to adapt):


SELECT DISTINCT tblRemarks.Category,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, tblRemarks.dtDate,
tblRemarks.HorseID, tblHorseInfo.Status, tblRemarks.Remark
FROM tblRemarks INNER JOIN tblHorseInfo ON tblRemarks.HorseID =
tblHorseInfo.HorseID
GROUP BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False),
tblRemarks.dtDate, tblRemarks.HorseID, tblHorseInfo.Status,
tblRemarks.Remark
WHERE (((tblHorseInfo.Status) Like 'Active*'))
AND tblRemarks.Category = [Forms]![MyForm]![cboCategory]
ORDER BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False);


I don't understand why you're using a Group By query though - you're not
counting, or totaling, or anything else that would need such a query, and
you're already using SELECT DISTINCT. If you remove the entire GROUP BY clause
do you get the same results?

SELECT DISTINCT tblRemarks.Category,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, tblRemarks.dtDate,
tblRemarks.HorseID, tblHorseInfo.Status, tblRemarks.Remark
FROM tblRemarks INNER JOIN tblHorseInfo ON tblRemarks.HorseID =
tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*'))
AND tblRemarks.Category = [Forms]![MyForm]![cboCategory]
ORDER BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False);


Again... just in case this isn't yet clear... this operation is *NOT* called
"sorting" by category. The

ORDER BY tblRemarks.Category

is sorting by category - putting the records that are retrieved into
alphabetical order by category. If you're just selecting one category, you can
leave tblRemarks.Category out of the ORDER BY clause - if the records all have
the same category then there's no need to sort them.

John W. Vinson [MVP]

 

[Bob V]

John your a Genius, I used the bottom SQL and changed the Form and Combo Box
Names and works PERFECT
Much appreciated for your time and effort ....Bob
ps: the Group by is that I copied another query that did the same thing but
only showed the latest Remark in the Category by date (Max)

I have Drop down List from another Query on Category , I am trying show
Only records with"Apples" in [Category]and 3 other fields
Name,dtDate,Remark
Thanks for helping ......Bob

Assuming that funGetHorse isn't involved in this; and that the drop down
box
is a Combo Box named cboCategory and that it's on a form named MyForm
(none of
which I know to be the case, you'll need to adapt):


SELECT DISTINCT tblRemarks.Category,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, tblRemarks.dtDate,
tblRemarks.HorseID, tblHorseInfo.Status, tblRemarks.Remark
FROM tblRemarks INNER JOIN tblHorseInfo ON tblRemarks.HorseID =
tblHorseInfo.HorseID
GROUP BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False),
tblRemarks.dtDate, tblRemarks.HorseID, tblHorseInfo.Status,
tblRemarks.Remark
WHERE (((tblHorseInfo.Status) Like 'Active*'))
AND tblRemarks.Category = [Forms]![MyForm]![cboCategory]
ORDER BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False);


I don't understand why you're using a Group By query though - you're not
counting, or totaling, or anything else that would need such a query, and
you're already using SELECT DISTINCT. If you remove the entire GROUP BY
clause
do you get the same results?

SELECT DISTINCT tblRemarks.Category,
funGetHorse(0,tblHorseInfo.HorseID,False) AS Name, tblRemarks.dtDate,
tblRemarks.HorseID, tblHorseInfo.Status, tblRemarks.Remark
FROM tblRemarks INNER JOIN tblHorseInfo ON tblRemarks.HorseID =
tblHorseInfo.HorseID
WHERE (((tblHorseInfo.Status) Like 'Active*'))
AND tblRemarks.Category = [Forms]![MyForm]![cboCategory]
ORDER BY tblRemarks.Category, funGetHorse(0,tblHorseInfo.HorseID,False);


Again... just in case this isn't yet clear... this operation is *NOT*
called
"sorting" by category. The

ORDER BY tblRemarks.Category

is sorting by category - putting the records that are retrieved into
alphabetical order by category. If you're just selecting one category, you
can
leave tblRemarks.Category out of the ORDER BY clause - if the records all
have
the same category then there's no need to sort them.

John W. Vinson [MVP]