List box - Find records to show in reports

[zweet18]

first of all thank u in advance.... Hope this is possible

I want to use a list box to search a record to show on the reports.. after
selecting a record in the list box they can preview the report but I have 2
types of reports that is depend on the selected data.. please see below

CompanyName State Layer
Company1 NY 1
Company1 DC 1
Company1 PA 2
Company2 NY 1
Company2 DC 1

If user select a company has location in NY and Rank is equal to 1. it will
open the NY Report.
In 2nd report if they select a company has a location in DC and with rank 1
another new report. but the same preview button

Before preview the report I want to query first the data that the user
selected to preview or open the correct report.

Is like searching a record but I need to filter it and then open in a
specific reports.

Thank you again

 

[Al Campagna]

Zweet,
You just need an If statement to determine which report to open. I'll
use my own object names.
PrivateSub cmdOpenReport_Click()
If lstCompanies.Column(1) = "NY" AND lstCompanies.Column(2) = 1 Then
'open Report 1
ElseIf listCompanies.Column(1) = "DC" AND listCompanies.Column(2) = 1
Then
'open Report2
End if
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Duane Hookom]

I would make sure there is a table that links states to reports. You should
then be able to add the report name (or number) as a column in the list box.
Your code would then use SELECT CASE statements like:

Dim strReport as String
'columns are numbered from 0
Select Case Mr.lboCompany.Column(2)
Case 1
strReport = "rptForNY"
Case Else
strReport ="rptForOthers:
End Select
DoCmd.OpenReport strReport

 

[zweet18]

Thanks!
I used IF statement but something wrong in my code, Could you check it what
is wrong with it. If I'm in the form selecting NY in the LISTBOX and click
preview nothing happen.
In column do I need to put column(STATE) which the column name or leave it
just column(1)
--------------------------------------------
Private Sub PREVIEW_Click()
On Error GoTo Err_PREVIEW_Click
Dim stDocName As String

If LstMOIDOCS.Column(1) = "NY" And LstMOIDOCS.Column(2) =
Then
stDocName = "Rpt_NY"
DoCmd.OpenReport stDocName, acPreview

ElseIf LstMOIDOCS.Column(1) = "DC" And LstMOIDOCS.Column(1) = "PAUL WEISS"
Then
stDocName = "Rpt_DC"
DoCmd.OpenReport stDocName, acPreview

End If

Exit_PREVIEW_Click:
Exit Sub

Err_PREVIEW_Click:
MsgBox Err.Description
Resume Exit_PREVIEW_Click

End Sub



Thank you again
----------------------------------------------------------

 

[zweet18]

disregard the first one there some Typo

--------------------------------------------
Private Sub PREVIEW_Click()
On Error GoTo Err_PREVIEW_Click
Dim stDocName As String

If LstMOIDOCS.Column(1) = "NY" And LstMOIDOCS.Column(2) = 1
Then
stDocName = "Rpt_NY"
DoCmd.OpenReport stDocName, acPreview

ElseIf LstMOIDOCS.Column(1) = "DC" And LstMOIDOCS.Column(2) = 1 Then
stDocName = "Rpt_DC"
DoCmd.OpenReport stDocName, acPreview

End If

Exit_PREVIEW_Click:
Exit Sub

Err_PREVIEW_Click:
MsgBox Err.Description
Resume Exit_PREVIEW_Click

End Sub



Thank you again...Appreciate all your help

 

[Al Campagna]

Zweet,
I don't see anything obviously wrong with your code.
Are you saying that nothing happens at all when you click the Preview
button?

Try a Refresh right after the button is "clicked.'
It may be that your record has not been updated yet, so lstMOIDOCS has
no value.
...... etc

You could also set a break point in your code on the first IF line, and
F8 (step) your way through the code, checking the lstMOIDOCS values as you
go.
I've got a suspicion the Refresh might do it though.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[zweet18]

Thank you so much for your help....I looked all the properties and I have
found the bound=1 and changed it to 0 and it works......Now im trying to link
it with the reports.

On my report i have the query so I typed on the code on the criteria for
STATE field

Like Forms![Frm_State_DOCS]![lstCompanies].Column(1)

tI use this to filter the query and show only what was selected on the list
box on the column(1) but its not accepting.. Is this code not possible in the
Report's criteria?

Thank you...You're the best...

 

[Al Campagna]

Zweet,
Please indicate what data is in your list columns, with a few examples
of values.
Also, your Column Count, and Column Widths.
---------------------
Add an unbound textbox (ex. Temp1) to your form with the following
calculation in the Control Source...
= lstCompanies
When you select a value from the list, what do you see in Temp1. That
indicates the real value that is bound to the listbox ControlSource.
Then try
=lstCompanies.Column(1)
Expirement this way to get the value you want to criteria the the
OpenReport.

Note:
The BoundColumn property considers the columns to be 1, 2 ,3, etc...
from left to right.
Access refers to the columns in a list box as 0, 1, 2, 3, ...etc from
left to right.

Using Bound Column = 0 is just defaulting the the first column value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you so much for your help....I looked all the properties and I have
found the bound=1 and changed it to 0 and it works......Now im trying to
link
it with the reports.

On my report i have the query so I typed on the code on the criteria for
STATE field

Like Forms![Frm_State_DOCS]![lstCompanies].Column(1)

tI use this to filter the query and show only what was selected on the
list
box on the column(1) but its not accepting.. Is this code not possible in
the
Report's criteria?

Thank you...You're the best...

Zweet,
I don't see anything obviously wrong with your code.
Are you saying that nothing happens at all when you click the Preview
button?

Try a Refresh right after the button is "clicked.'
It may be that your record has not been updated yet, so lstMOIDOCS
has
no value.

...... etc

You could also set a break point in your code on the first IF line,
and
F8 (step) your way through the code, checking the lstMOIDOCS values as
you
go.
I've got a suspicion the Refresh might do it though.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

 

[zweet18]

You are the best.....Really big thanks for your helped....I'm new in this and
you really guide me in good directions....Hoping to learn more....

Zweet,
Please indicate what data is in your list columns, with a few examples
of values.
Also, your Column Count, and Column Widths.
---------------------
Add an unbound textbox (ex. Temp1) to your form with the following
calculation in the Control Source...
= lstCompanies
When you select a value from the list, what do you see in Temp1. That
indicates the real value that is bound to the listbox ControlSource.
Then try
=lstCompanies.Column(1)
Expirement this way to get the value you want to criteria the the
OpenReport.

Note:
The BoundColumn property considers the columns to be 1, 2 ,3, etc...
from left to right.
Access refers to the columns in a list box as 0, 1, 2, 3, ...etc from
left to right.

Using Bound Column = 0 is just defaulting the the first column value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Thank you so much for your help....I looked all the properties and I have
found the bound=1 and changed it to 0 and it works......Now im trying to
link
it with the reports.

On my report i have the query so I typed on the code on the criteria for
STATE field

Like Forms![Frm_State_DOCS]![lstCompanies].Column(1)

tI use this to filter the query and show only what was selected on the
list
box on the column(1) but its not accepting.. Is this code not possible in
the
Report's criteria?

Thank you...You're the best...

Zweet,
I don't see anything obviously wrong with your code.
Are you saying that nothing happens at all when you click the Preview
button?

Try a Refresh right after the button is "clicked.'
It may be that your record has not been updated yet, so lstMOIDOCS
has
no value.

Private Sub PREVIEW_Click()
On Error GoTo Err_PREVIEW_Click
Dim stDocName As String
Refresh
...... etc

You could also set a break point in your code on the first IF line,
and
F8 (step) your way through the code, checking the lstMOIDOCS values as
you
go.
I've got a suspicion the Refresh might do it though.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


disregard the first one there some Typo
--------------------------------------------
Private Sub PREVIEW_Click()
On Error GoTo Err_PREVIEW_Click
Dim stDocName As String

If LstMOIDOCS.Column(1) = "NY" And LstMOIDOCS.Column(2) = 1
Then
stDocName = "Rpt_NY"
DoCmd.OpenReport stDocName, acPreview

ElseIf LstMOIDOCS.Column(1) = "DC" And LstMOIDOCS.Column(2) = 1 Then
stDocName = "Rpt_DC"
DoCmd.OpenReport stDocName, acPreview

End If

Exit_PREVIEW_Click:
Exit Sub

Err_PREVIEW_Click:
MsgBox Err.Description
Resume Exit_PREVIEW_Click

End Sub



Thank you again...Appreciate all your help
----------------------------------------------------------







:

Zweet,
You just need an If statement to determine which report to open.
I'll
use my own object names.
PrivateSub cmdOpenReport_Click()
If lstCompanies.Column(1) = "NY" AND lstCompanies.Column(2) = 1
Then
'open Report 1
ElseIf listCompanies.Column(1) = "DC" AND
listCompanies.Column(2) =
1
Then
'open Report2
End if
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

first of all thank u in advance.... Hope this is possible

I want to use a list box to search a record to show on the
reports..
after
selecting a record in the list box they can preview the report but
I
have
2
types of reports that is depend on the selected data.. please see
below

CompanyName State Layer
Company1 NY 1
Company1 DC 1
Company1 PA 2
Company2 NY 1
Company2 DC 1

If user select a company has location in NY and Rank is equal to
1.
it
will
open the NY Report.
In 2nd report if they select a company has a location in DC and
with
rank
1
another new report. but the same preview button

Before preview the report I want to query first the data that the
user
selected to preview or open the correct report.

Is like searching a record but I need to filter it and then open
in a
specific reports.

Thank you again