Filtering a report from a selected item in a listbox on a form

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

Guest

I have a form that has a 2 column listbox showing the job number (10 characters) and job name. I want the user to select a project and click on the open report comand button to open the report filtered for the selected project only. How can I extract the job number in a text format so that I can use it to filter the report?
 
Amyers,

One way to do this is to base your report on a query, and in the
criteria of the Job Number field, put the equivalent of...
[Forms]![NameOfYourForm]![NameOfYourListbox]

Another way, assuming you are using an OpenReport macro on the Click
event of the command button, is to use the Where Condition argument of
the OpenReport, such as...
[Job Number]=[Forms]![NameOfYourForm]![NameOfYourListbox]
 
Steve,
How do I edit the contents for the listbox to grab just the jobnumber and not the whole string which is the job number followed by the job name?

I have tried this:
active = "wlbjobnum = forms![frmTasks]!ProjectList"
job = Left(active, 10)

but then job becomes "wlbjobnum " not the job number itself.

----- Steve Schapel wrote: -----

Amyers,

One way to do this is to base your report on a query, and in the
criteria of the Job Number field, put the equivalent of...
[Forms]![NameOfYourForm]![NameOfYourListbox]

Another way, assuming you are using an OpenReport macro on the Click
event of the command button, is to use the Where Condition argument of
the OpenReport, such as...
[Job Number]=[Forms]![NameOfYourForm]![NameOfYourListbox]
 
Amyers,

Do you mean the job number and job name are both in the same field? I
thought you said the listbox had two columns? What is the data in the
Report's recordsource which you are trying to match... presumably if the
job number and name are in the same field in the listbox, they will
also be in the same field in the report? Can you give a bit more detail
about what you have actually got.
 
Here is the code that I tried to work from the report code. This works to until it gets to the where clause on the SQL statement. Then is puts that the job numbers as if it should be a field name not a text. For example I need the job number to be "199049C002". It is pulling in 199049C002 but not as a string

Private Sub Report_Open(Cancel As Integer

'Set up the SQL statement to filter the repor
Dim active As Strin
Dim job As Strin
Dim strSQL As Strin
Dim ProjectList As Objec

Me!ProjectList.Enabled = Tru
job = Left(ProjectList, 10
' active = "wlbjobnum = forms![frmTasks]!Left(ProjectList, 10)

strSQL =
"SELECT ProjectSetup.WLBJobNum, ProjectSetup.DateCreated, ProjectSetup.PM, ProjectSetup.ProjectName, ProjectSetup.WorkPerformed, OpenTask.TaskNo, OpenTask.Description " &
"FROM ProjectSetup INNER JOIN OpenTask ON ProjectSetup.WLBJobNum = OpenTask.ProjectNo Where WLBJobNum = job;

Me.RecordSource = strSQ

End Su

******************************************************************
I have also tried to use the following code on the form portion. (When I use this code the code on the report is commented out

active = "wlbjobnum = forms![frmTasks]!ProjectList
job = Left(active, 10

'Open the report for the selected project ... I hop
DoCmd.OpenReport "rptopentasks", acViewPreview, , jo
DoCmd.Maximiz

In this case I am pulling from an unbound listbox with the following row source
SELECT [qryProjectSetup].[WLBJobNum], [qryProjectSetup].[ProjectName] FROM qryProjectSetup ORDER BY [wlbjobnum];

Do I need to truncate the information that is coming in from the listbox or does the code automatically know what I am looking for

I know this should be easy but I have myself all confused. Any help you can give will be greatly appreciated.
 
Steve if I change the code to this

Private Sub OpenReport_Click(
Dim active As Strin
Dim WLBJobNum As Strin

Me!ProjectList.Enabled = Tru
WLBJobNum = Forms![frmTasks]!ProjectLis

'Open the report for the selected project ... I hop
DoCmd.OpenReport "rptopentasks", acViewPreview, , WLBJobNu
DoCmd.Maximiz
' DoCmd.SelectObject acForm, "frmTasks
' DoCmd.Clos

End Su

It seems to be working but it is not applying the filter to the report. Is there a setting that I am missing? The reports opens but is unable to populate any of the fields. They all say "#Name?".
 
Amyers,

Since this is a macros neewsgroup, I had assumed you were using macros,
but I now see that this is not the case.

As mentioned before, you can make a query as the Recordsource of the
report. But if you really must use VBA to assign the Recordsource at
runtime, I would simply remove the WHERE clause from the strSQL
expression in the report's Open event. And then, I can't see why it
wouldn't work to simply use this from whatever form event you are using
to view the report...
DoCmd.OpenReport "rptopentasks", acViewPreview, , "WLBJobNum='" &
Me.ListboxName & "'"
 
Amyers,

I ProjectList the name of the listbox? I thought WLBJobum is the name
of the field in the report... if so, it is not a good idea to name a
variable with the same name. And just putting this in the Condition
argument of the OpenReport method is incorrect syntax. You need to use
the type of syntax as referred to in my earlier post. There is other
stuff in the code that is redundant. The 'active' variable is not used,
but in the code you showed earlier, it was not defined anyay as a
string, it should have been Dim active As Boolean but I couldn't see
what its purpose was anyway.
 
Back
Top