Using listbox on form to open record in report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gregatvrm - 25 Jul 2008 21:34 GMT

I'm a newbie.

I have a form named "Form1" that has an unbound listbox that shows me the
dates from a table named "Invoice". I need to open my report named,
"PreviousInvoices" with the information from my table for that selected date.
Can I have it open the report from a OnChange event? How can I write the vba
code to open my report with only the information related to the date selected
from the listbox and where do I put this code? Should I make a query and get
my data for the report from it?

Thanks for the help.

Reply to this Message

Allen Browne - 26 Jul 2008 05:04 GMT

You can use the AfterUpdate event procedure of the unbound list box to open
the report to the selected date.

The code for the after update event procedure would be something like this:

Private Sub lstDate_AfterUpdate()
Dim strWhere As String
If IsDate(Me.lstDate) Then
strWhere = "[InvoiceDate] = " & Format(Me.lstDate,
"\#mm\/dd\/yyyy\#")
'Debug.Print strWhere
DoCmd.OpenReport "PreviousInvoices", acViewPreview, , strWhere
Else
MsgBox "List box is not a date. Value: " & Me.lstDate
End If
End Sub

There are many assumptions in that example, e.g.:
- the list box is named lstDate
- its Bound Column is a date/time value
- the field to match is called InvoiceDate, and in contains a date only
(i.e. no time component.)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> I have a form named "Form1" that has an unbound listbox that shows me the
> dates from a table named "Invoice". I need to open my report named,
[quoted text clipped - 7 lines]
> get
> my data for the report from it?

Reply to this Message

gregatvrm - 28 Jul 2008 17:20 GMT

Allen,

Thanks for answering, My table is date/time and I made it a short date but
it still put in time. I tried using your code and it comes up with a syntax
error highlighting the first line.

Private Sub List11_AfterUpdate()
Dim strWhere As String
If List11(Me.List11) Then
strWhere = "[SalesDate] = " & Format(Me.List11,
"\#mm\/dd\/yyyy\#")
'Debug.Print strWhere
DoCmd.OpenReport "PreviousSalesInvoices", acViewPreview, , strWhere
Else
MsgBox "List box is not a date. Value: " & Me.List11
End If
End Sub

I'm using access 2007. "SalesDate" is the table field name where I'm
getting the info from, "List11" is the List Box name and
"PreviousSalesInvoices" is the name of the report. To add further, what I am
trying to do is make it easy for our Thrift Store manager to reprint an
invoice when asked to.

> You can use the AfterUpdate event procedure of the unbound list box to open
> the report to the selected date.
[quoted text clipped - 30 lines]
> > get
> > my data for the report from it?

Reply to this Message

Allen Browne - 28 Jul 2008 17:59 GMT

The Format controls only how the data is displayed (not what's stored), so
it can still have a time component in the field. Perhaps you used =Now()
where you should have used =Date()?

Once you deal with that, you can move on to your code.

I did not understand the line:
If List11(Me.List11) Then
Do you have a List11() function?
Or did you intend to use the IsDate() function?

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Allen,
>
[quoted text clipped - 56 lines]
>> > to the date selected from the listbox and where do I put this code?
>> > Should I make a query and get my data for the report from it?

Reply to this Message

gregatvrm - 28 Jul 2008 18:37 GMT

In my table "Input" where I'm getting the data, under format for the
"SalesDate" field it says "mm/dd/yyyy". Should I change that to Date()?
List11 is the name of the List Box. All I'm trying to do is when the form
"Form1" is opened I want to choose the date from a List Box or Combo Box that
gets its info from my table and once it is chosen then it opens the report
with that dates information. I hope this explains it.

> The Format controls only how the data is displayed (not what's stored), so
> it can still have a time component in the field. Perhaps you used =Now()
> where you should have used =Date()?

> Once you deal with that, you can move on to your code.
>
[quoted text clipped - 63 lines]
> >> > to the date selected from the listbox and where do I put this code?
> >> > Should I make a query and get my data for the report from it?

Reply to this Message

Allen Browne - 28 Jul 2008 18:42 GMT

As stated, you have 2 different issues here:
- the Format is not really relevant
- If the field contains a time component (regardless of how you format it),
you will have to sort this out.

Use the IsDate() function in your code, as shown previously.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> In my table "Input" where I'm getting the data, under format for the
> "SalesDate" field it says "mm/dd/yyyy". Should I change that to Date()?
[quoted text clipped - 80 lines]
>> >> > to the date selected from the listbox and where do I put this code?
>> >> > Should I make a query and get my data for the report from it?

Reply to this Message

gregatvrm - 28 Jul 2008 19:18 GMT

This is the after update event,

Private Sub List11_AfterUpdate()
Dim strWhere As String
If IsDate(Me.List11) Then
strWhere = "[InvoiceDate] = " & Format(Me.List11, "\#mm\/dd\/yyyy\#")
'Debug.Print strWhere
DoCmd.OpenReport "PreviousSalesInvoices", acViewPreview, , strWhere
Else
MsgBox "List box is not a date. Value: " & Me.List11
End If
End Sub

I'm getting a message box that says List box not a date. Value 1003.

I see the data in the list box as a date when I scroll through, I just don't
see where I can format the List box as a date. Thanks for being patient.

> As stated, you have 2 different issues here:
> - the Format is not really relevant
[quoted text clipped - 87 lines]
> >> >> > to the date selected from the listbox and where do I put this code?
> >> >> > Should I make a query and get my data for the report from it?

Reply to this Message

Allen Browne - 29 Jul 2008 03:30 GMT

Either the bound column of the list box is not a date, or the value in the
bound column is not recognised as a date.

To sort that out, look at these properties of the list box:
- Column Count tells how many columns you have
- Row Source tells what the columns are
- Bound Column tells which one is the value of the list box.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> This is the after update event,
>
[quoted text clipped - 125 lines]
>> >> >> > code?
>> >> >> > Should I make a query and get my data for the report from it?

Reply to this Message

gregatvrm - 29 Jul 2008 17:13 GMT

Here's what I've got:

Column Count - 2
Row Source - SELECT [Input].[ID], [Input].[SalesDate] FROM [Input] ORDER BY
[SalesDate];
Row Source Type - Table/Query
Bound Column - 1

I'm able to open the report using any selection in the ListBox but it
doesn't show any information. I put in the OnOpen event of my report the
following code and it doesn't seem to work,
Select * From Input Where SalesDate = Forms![Form1]![List13]
My report does not have anything in the Record Source, and has all of the
textboxes for all information from my Input table if that helps.

> Either the bound column of the list box is not a date, or the value in the
> bound column is not recognised as a date.
[quoted text clipped - 3 lines]
> - Row Source tells what the columns are
> - Bound Column tells which one is the value of the list box.

Reply to this Message

Allen Browne - 30 Jul 2008 01:12 GMT

Exactly: the bound column is the ID value, not the date.

You may be able to get the date by referring to:
List11.Column(1)

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Here's what I've got:
>
[quoted text clipped - 20 lines]
>> - Row Source tells what the columns are
>> - Bound Column tells which one is the value of the list box.

Reply to this Message

gregatvrm - 30 Jul 2008 22:52 GMT

As I mentioned earlier I am new to this programming. Where do I put
List11.Column(1). Do I put this somewhere in the Form or somewhere in the
Report. I made a new unbound form with an unbound ComboBox and used the
exact same After Update code as the list box. It doesn't use the [ID] field.

Column Count - 1
RowSource - SELECT [Input].[SalesDate] FROM [Input] ORDER BY [SalesDate];
RowSource type - Query/Table
BoundColumn - 1

After Update Code:

Private Sub Combo13_AfterUpdate()
Dim strWhere As String
If IsDate(Me.Combo13) Then
strWhere = "[SalesDate] = " & Format(Me.Combo13, "\#mm\/dd\/yyyy\#")
'Debug.Print strWhere
DoCmd.OpenReport "PreviousSalesInvoice", acViewPreview, , strWhere
Else
MsgBox "List box is not a date. Value: " & Me.Combo13
End If
End Sub

I see my dates but when I choose a date it comes up with an error:
"The expression After Update you entered as the event property setting
produced the following error: File not found.
*The expression may result in the name of a macro, the name of a user
defined function, or [Event Procedure].
*There may have been an error evaluating the function, event or macro."

I have gone over it and don't see anything mispelled. And what and where in
the report do I put any codes.

> Exactly: the bound column is the ID value, not the date.
>
[quoted text clipped - 25 lines]
> >> - Row Source tells what the columns are
> >> - Bound Column tells which one is the value of the list box.

Reply to this Message

Allen Browne - 31 Jul 2008 14:25 GMT

Might have to let you work through how to do the programming here.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> As I mentioned earlier I am new to this programming

Reply to this Message

gregatvrm - 31 Jul 2008 16:27 GMT

Thank you Allen for all your patience and time.

> Might have to let you work through how to do the programming here.
>
> > As I mentioned earlier I am new to this programming

Reply to this Message