Using listbox on form to open record in report

[gregatvrm]

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.

 

[Allen Browne]

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.)

 

[gregatvrm]

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.

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.)

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

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,
"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?

 

[Allen Browne]

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?

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

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

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.

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.)

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?

 

[gregatvrm]

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.

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?

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

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

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.

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.)


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?

 

[Allen Browne]

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.

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

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()?
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.

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?

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.

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.)


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?

 

[gregatvrm]

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
- 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.

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

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()?
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.

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?

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.

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.)


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?

 

[Allen Browne]

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.

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

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

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
- 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.

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

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()?
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.

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?

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.

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.)


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?

 

[gregatvrm]

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.

 

[Allen Browne]

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)

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

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

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.

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.

 

[gregatvrm]

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.

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

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

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

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.

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.

 

[Allen Browne]

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

 

[gregatvrm]

Thank you Allen for all your patience and time.

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