HELP: Using a List Box as a critera selector

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

Guest

I have a report that requires a date to be entered when opened.
Right now I simply have a Parameter Value window that appears.

I have created a form that contains all the dates in a list box, and I would
like to have it when you open the report, the list box appers, you select the
date and press a button, the report appears with the selected date.
 
Look at the NORTHWIND sample database. Almost all the reports there do what
you are asking. Copy their example.
 
Instead of openning the report, just open the form with the list box, in that
form add an OK button, that will open the report with a criteria

In the OK button write the code:
If MyListName.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
Else
docmd.OpenReport "ReportName",,,"[Mydate]=#" & Me.MyListName & "#"
End If
 
This was helpful and I am sure it will be useful for future projects; however
I hate to be picky, but I am using the table MsysObjects to create a list of
my reports so the form would not show up on my Report list.

Could you please give me the VBA that would reference the form from the
report?

Ofer said:
Instead of openning the report, just open the form with the list box, in that
form add an OK button, that will open the report with a criteria

In the OK button write the code:
If MyListName.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
Else
docmd.OpenReport "ReportName",,,"[Mydate]=#" & Me.MyListName & "#"
End If


Tim said:
I have a report that requires a date to be entered when opened.
Right now I simply have a Parameter Value window that appears.

I have created a form that contains all the dates in a list box, and I would
like to have it when you open the report, the list box appers, you select the
date and press a button, the report appears with the selected date.
Click to expand...
Click to expand...
 
The best way is to select an object name, like a report name, then filter the
MsysObjects table on the "name" field with that name of the report, check
the type field, the value you get, if you filter on it youll get all the
reports.

Select * From MsysObjects Where Type = -32764

will return all the reports in the MDB
======================================
Tim said:
This was helpful and I am sure it will be useful for future projects; however
I hate to be picky, but I am using the table MsysObjects to create a list of
my reports so the form would not show up on my Report list.

Could you please give me the VBA that would reference the form from the
report?

Ofer said:
Instead of openning the report, just open the form with the list box, in that
form add an OK button, that will open the report with a criteria

In the OK button write the code:
If MyListName.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
Else
docmd.OpenReport "ReportName",,,"[Mydate]=#" & Me.MyListName & "#"
End If


Tim said:
I have a report that requires a date to be entered when opened.
Right now I simply have a Parameter Value window that appears.

I have created a form that contains all the dates in a list box, and I would
like to have it when you open the report, the list box appers, you select the
date and press a button, the report appears with the selected date.
Click to expand...
Click to expand...
Click to expand...
 
I think the "Ok" button code below will work for me, but I'm not searching by
date, rather by field "Description" that is text. Here is the code I used:

DoCmd.OpenReport "r_2010renewals", , , "[Description]=#" & Me.List0 & "#"

Error says syntax error.. Is it the # signs throwing it off? How do I
rewrite?

Thanks for any help..!



Ofer said:
Instead of openning the report, just open the form with the list box, in that
form add an OK button, that will open the report with a criteria

In the OK button write the code:
If MyListName.ItemsSelected.Count = 0 Then
Beep
MsgBox "No Item Selected", 48
Exit Sub
Else
docmd.OpenReport "ReportName",,,"[Mydate]=#" & Me.MyListName & "#"
End If


Tim said:
I have a report that requires a date to be entered when opened.
Right now I simply have a Parameter Value window that appears.

I have created a form that contains all the dates in a list box, and I would
like to have it when you open the report, the list box appers, you select the
date and press a button, the report appears with the selected date.
Click to expand...
Click to expand...
 
tish said:
I think the "Ok" button code below will work for me, but I'm not searching
by
date, rather by field "Description" that is text. Here is the code I used:

DoCmd.OpenReport "r_2010renewals", , , "[Description]=#" & Me.List0 & "#"

Error says syntax error.. Is it the # signs throwing it off?
Yes.

How do I rewrite?
Click to expand...

Like this:

DoCmd.OpenReport "r_2010renewals", , , _
"[Description]=""" & Me.List0 & """"

That should work unless the value selected in List0 contains the
double-quote character ("). There's a workaround for that, so post back if
you need it.
 
Steve said:
This will only work if what you want for description is in the first
column of the listbox. If it is say the third column then you need:

DoCmd.OpenReport "r_2010renewals", , , _
"[Description]=""" & Me.List0.Column(2) & """"
Click to expand...


Not strictly true, Steve. What I posted will work if the description is the
bound column of the combo box, regardless of which column that happens to
be. However, if the list box's bound column is not the Description column,
then the syntax you posted is required.
 
Thank you so much..!

Dirk Goldgar said:
tish said:
I think the "Ok" button code below will work for me, but I'm not searching
by
date, rather by field "Description" that is text. Here is the code I used:

DoCmd.OpenReport "r_2010renewals", , , "[Description]=#" & Me.List0 & "#"

Error says syntax error.. Is it the # signs throwing it off?
Yes.

How do I rewrite?
Click to expand...

Like this:

DoCmd.OpenReport "r_2010renewals", , , _
"[Description]=""" & Me.List0 & """"

That should work unless the value selected in List0 contains the
double-quote character ("). There's a workaround for that, so post back if
you need it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Hey 'tish' could you please give me your contact info. I'm "Tish". Since
we both seem to be using almost the same screen name it's getting just a bit
confusing on name searches. I tried to see if Microsoft would forward an
e-mail to you but the answer was 'no' since I could just post my query on one
of the sites where you were active.
tish said:
Thank you so much..!

Dirk Goldgar said:
tish said:
I think the "Ok" button code below will work for me, but I'm not searching
by
date, rather by field "Description" that is text. Here is the code I used:

DoCmd.OpenReport "r_2010renewals", , , "[Description]=#" & Me.List0 & "#"

Error says syntax error.. Is it the # signs throwing it off?
Yes.

How do I rewrite?
Click to expand...

Like this:

DoCmd.OpenReport "r_2010renewals", , , _
"[Description]=""" & Me.List0 & """"

That should work unless the value selected in List0 contains the
double-quote character ("). There's a workaround for that, so post back if
you need it.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
Back
Top