To Print a List Box Control on a report

[Guest]

I have two controls in a form: a list box named "FiscalYear" and a comb box
named "BudgetType". I need to print two controls on a report. I used the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window, but I get
Null in the immediate window. I think that's why I could not print out on the
report for what I chosed for FiscalYear from the form. but don't know
why....Do comb box and list box work different on the report when you are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

Has the list box been set to allow MultiSelect? If so, then yes, it works
differently than a combo box. You won't be able to simply refer to it:
you'll need VBA to loop through its ItemsSelected collection, concatenating
all of the selected items and then putting that concatenated list on the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears & Forms!frmForm1!FiscalYear.ItemData(varSelected) &
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

 

[Guest]

Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to put the code
in the form or in the report? If it should be entered in the form. How should
I do with the Text box in the report?

Thanks so much for your time!

Ally

 

[Douglas J. Steele]

That code was intended to be run when the report open. If you look at the
Properies window with the report object selected, set the Open property to
[Event Procedure] and click on the ellipsis (...) to the right of the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

 

[Guest]

Douglas,

After I copy the code to the Open event for the report. I got a compile
error message saying Method or data member not found. The cursor in the code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which matches
the same name in the code?

Currently, I have the control named "Text80" that has the expression as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & " Under50K "
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

Please advise on how I should modify it to match the suggested code.

Thanks,

Ally

That code was intended to be run when the report open. If you look at the
Properies window with the report object selected, set the Open property to
[Event Procedure] and click on the ellipsis (...) to the right of the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to put the
code
in the form or in the report? If it should be entered in the form. How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

 

[Douglas J. Steele]

You were supposed to replace "MyTextbox" with the name of the textbox to
which you want the data written.

Based on the additional information you've given me, try:

Me.MyTextBox = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

On the other hand, if you're only expecting a single year, have you made
sure that the MultiSelect property of the FiscalYear list box has been set
to None?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

After I copy the code to the Open event for the report. I got a compile
error message saying Method or data member not found. The cursor in the
code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which matches
the same name in the code?

Currently, I have the control named "Text80" that has the expression as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & " Under50K
"
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please advise on how I should modify it to match the suggested code.

Thanks,

Ally

That code was intended to be run when the report open. If you look at the
Properies window with the report object selected, set the Open property
to
[Event Procedure] and click on the ellipsis (...) to the right of the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to put the
code
in the form or in the report? If it should be entered in the form. How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Guest]

Douglas,

I replace "MyTextbox" in the code with "Text80", which is the name of the
text control in the report. Currently the text box control is empty, which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

I have the Run-Time error "438" saying Object doesn't support this property
or method. The cursor stays on the follwing line after clicking Debug:

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in the form
as Extended because I do need to choose multiple Fiscal year sometimes.

Please advise.

Thanks,

Ally

 

[Guest]

Douglas,

Here is my final code for the open procedure for the report:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear
strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

End Sub

But I got a error message that I mentioned in my previous response. Would
you please help?

thanks,

Ally

You were supposed to replace "MyTextbox" with the name of the textbox to
which you want the data written.

Based on the additional information you've given me, try:

Me.MyTextBox = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

On the other hand, if you're only expecting a single year, have you made
sure that the MultiSelect property of the FiscalYear list box has been set
to None?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

After I copy the code to the Open event for the report. I got a compile
error message saying Method or data member not found. The cursor in the
code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which matches
the same name in the code?

Currently, I have the control named "Text80" that has the expression as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & " Under50K
"
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please advise on how I should modify it to match the suggested code.

Thanks,

Ally

That code was intended to be run when the report open. If you look at the
Properies window with the report object selected, set the Open property
to
[Event Procedure] and click on the ellipsis (...) to the right of the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to put the
code
in the form or in the report? If it should be entered in the form. How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

I noticed that you have another thread in progress on your problem, with the
implication being that you've got the wrong table name or field name, or
that the form isn't open when the code is running.

Please confine each problem to a single thread: otherwise, some of us end up
wasting our time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Here is my final code for the open procedure for the report:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear
strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

End Sub

But I got a error message that I mentioned in my previous response. Would
you please help?

thanks,

Ally

You were supposed to replace "MyTextbox" with the name of the textbox to
which you want the data written.

Based on the additional information you've given me, try:

Me.MyTextBox = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

On the other hand, if you're only expecting a single year, have you made
sure that the MultiSelect property of the FiscalYear list box has been
set
to None?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

After I copy the code to the Open event for the report. I got a compile
error message saying Method or data member not found. The cursor in the
code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which
matches
the same name in the code?

Currently, I have the control named "Text80" that has the expression as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & "
Under50K
"
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please advise on how I should modify it to match the suggested code.

Thanks,

Ally

:

That code was intended to be run when the report open. If you look at
the
Properies window with the report object selected, set the Open
property
to
[Event Procedure] and click on the ellipsis (...) to the right of the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to put
the
code
in the form or in the report? If it should be entered in the form.
How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not
print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Guest]

Douglas,

Sorry that I confused you and make you feel that way which I was not my
intention to do so... but I really did not see the thread of the problem you
mentioned about wrong table name or field name. I checked, they look right to
me... The name of the form is called frmUnder50KItemsTrackingParms...Would
you please generously point out for me what went wrong?

Thanks a lot!

Ally

I noticed that you have another thread in progress on your problem, with the
implication being that you've got the wrong table name or field name, or
that the form isn't open when the code is running.

Please confine each problem to a single thread: otherwise, some of us end up
wasting our time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Here is my final code for the open procedure for the report:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear
strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

End Sub

But I got a error message that I mentioned in my previous response. Would
you please help?

thanks,

Ally

You were supposed to replace "MyTextbox" with the name of the textbox to
which you want the data written.

Based on the additional information you've given me, try:

Me.MyTextBox = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

On the other hand, if you're only expecting a single year, have you made
sure that the MultiSelect property of the FiscalYear list box has been
set
to None?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

After I copy the code to the Open event for the report. I got a compile
error message saying Method or data member not found. The cursor in the
code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which
matches
the same name in the code?

Currently, I have the control named "Text80" that has the expression as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & "
Under50K
"
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please advise on how I should modify it to match the suggested code.

Thanks,

Ally

:

That code was intended to be run when the report open. If you look at
the
Properies window with the report object selected, set the Open
property
to
[Event Procedure] and click on the ellipsis (...) to the right of the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to put
the
code
in the form or in the report? If it should be entered in the form.
How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not
print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

In the thread "How to get value from a list box", you told Allen that

?Forms!frmUnder50KItemsTracking.Name

resulted in Null.

That's really not possible: assuming that the form really is named
frmUnder50KItemsTracking, that's what the Name property should return (and
if it's not named that, you should get an error trying to refer to it).

Previously, you mentioned that the error "Method or data member not found"
was being generated by the line of code with MyTextBox. Now that you
corrected the name of that text box, which line of code is generating the
error? If it's the one with Me.Text80, are you sure Text80 is the correct
name? The other thing to do is check that

?[Forms]![frmUnder50KItemsTrackingParms]![BudgetType]

in the Immediate window returns what you expect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Sorry that I confused you and make you feel that way which I was not my
intention to do so... but I really did not see the thread of the problem
you
mentioned about wrong table name or field name. I checked, they look right
to
me... The name of the form is called frmUnder50KItemsTrackingParms...Would
you please generously point out for me what went wrong?

Thanks a lot!

Ally

I noticed that you have another thread in progress on your problem, with
the
implication being that you've got the wrong table name or field name, or
that the form isn't open when the code is running.

Please confine each problem to a single thread: otherwise, some of us end
up
wasting our time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Here is my final code for the open procedure for the report:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear
strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

End Sub

But I got a error message that I mentioned in my previous response.
Would
you please help?

thanks,

Ally
:

You were supposed to replace "MyTextbox" with the name of the textbox
to
which you want the data written.

Based on the additional information you've given me, try:

Me.MyTextBox = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

On the other hand, if you're only expecting a single year, have you
made
sure that the MultiSelect property of the FiscalYear list box has been
set
to None?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

After I copy the code to the Open event for the report. I got a
compile
error message saying Method or data member not found. The cursor in
the
code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which
matches
the same name in the code?

Currently, I have the control named "Text80" that has the expression
as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & "
Under50K
"
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please advise on how I should modify it to match the suggested code.

Thanks,

Ally

:

That code was intended to be run when the report open. If you look
at
the
Properies window with the report object selected, set the Open
property
to
[Event Procedure] and click on the ellipsis (...) to the right of
the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to
put
the
code
in the form or in the report? If it should be entered in the
form.
How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

:

Has the list box been set to allow MultiSelect? If so, then yes,
it
works
differently than a combo box. You won't be able to simply refer
to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated
list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear"
and a
comb
box
named "BudgetType". I need to print two controls on a report.
I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate
Window,
but I
get
Null in the immediate window. I think that's why I could not
print
out
on
the
report for what I chosed for FiscalYear from the form. but
don't
know
why....Do comb box and list box work different on the report
when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Guest]

Douglas,

Sorry for the confusion. When I answered Allen's questions, I mean I
received the NUll when I do ? Forms!frmUnder50KItemsTrackingParms!FiscalYear.
I have mistakenly thought he was ask me what the result is for
?Forms!frmUnder50KItemsTrackingParms!FiscalYear since that's my focus why it
did not work.

I did test for ?Froms!frmUnder50KItemsTrackingParms.Name, I got
frmUnder50KItemsTrackingParms
as the result.

But I still got Null when I try to print out the fiscalyear I select.

Feel so frustrated as you do...

Thanks for your help though!

Ally

In the thread "How to get value from a list box", you told Allen that

?Forms!frmUnder50KItemsTracking.Name

resulted in Null.

That's really not possible: assuming that the form really is named
frmUnder50KItemsTracking, that's what the Name property should return (and
if it's not named that, you should get an error trying to refer to it).

Previously, you mentioned that the error "Method or data member not found"
was being generated by the line of code with MyTextBox. Now that you
corrected the name of that text box, which line of code is generating the
error? If it's the one with Me.Text80, are you sure Text80 is the correct
name? The other thing to do is check that

?[Forms]![frmUnder50KItemsTrackingParms]![BudgetType]

in the Immediate window returns what you expect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Sorry that I confused you and make you feel that way which I was not my
intention to do so... but I really did not see the thread of the problem
you
mentioned about wrong table name or field name. I checked, they look right
to
me... The name of the form is called frmUnder50KItemsTrackingParms...Would
you please generously point out for me what went wrong?

Thanks a lot!

Ally

I noticed that you have another thread in progress on your problem, with
the
implication being that you've got the wrong table name or field name, or
that the form isn't open when the code is running.

Please confine each problem to a single thread: otherwise, some of us end
up
wasting our time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Here is my final code for the open procedure for the report:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear
strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

End Sub

But I got a error message that I mentioned in my previous response.
Would
you please help?

thanks,

Ally
:

You were supposed to replace "MyTextbox" with the name of the textbox
to
which you want the data written.

Based on the additional information you've given me, try:

Me.MyTextBox = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

On the other hand, if you're only expecting a single year, have you
made
sure that the MultiSelect property of the FiscalYear list box has been
set
to None?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

After I copy the code to the Open event for the report. I got a
compile
error message saying Method or data member not found. The cursor in
the
code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which
matches
the same name in the code?

Currently, I have the control named "Text80" that has the expression
as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & "
Under50K
"
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please advise on how I should modify it to match the suggested code.

Thanks,

Ally

:

That code was intended to be run when the report open. If you look
at
the
Properies window with the report object selected, set the Open
property
to
[Event Procedure] and click on the ellipsis (...) to the right of
the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to
put
the
code
in the form or in the report? If it should be entered in the
form.
How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

:

Has the list box been set to allow MultiSelect? If so, then yes,
it
works
differently than a combo box. You won't be able to simply refer
to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated
list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear"
and a
comb
box
named "BudgetType". I need to print two controls on a report.
I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate
Window,
but I
get
Null in the immediate window. I think that's why I could not
print
out
on
the
report for what I chosed for FiscalYear from the form. but
don't
know
why....Do comb box and list box work different on the report
when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

You will always get Null for a multiselect list box, regardless of how many
items may be selected in it.

Have you got it working now? If not, you still haven't answered my question
about which line of code is now generating the error.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

Sorry for the confusion. When I answered Allen's questions, I mean I
received the NUll when I do ?
Forms!frmUnder50KItemsTrackingParms!FiscalYear.
I have mistakenly thought he was ask me what the result is for
?Forms!frmUnder50KItemsTrackingParms!FiscalYear since that's my focus why
it
did not work.

I did test for ?Froms!frmUnder50KItemsTrackingParms.Name, I got
frmUnder50KItemsTrackingParms
as the result.

But I still got Null when I try to print out the fiscalyear I select.

Feel so frustrated as you do...

Thanks for your help though!

Ally

In the thread "How to get value from a list box", you told Allen that

?Forms!frmUnder50KItemsTracking.Name

resulted in Null.

That's really not possible: assuming that the form really is named
frmUnder50KItemsTracking, that's what the Name property should return
(and
if it's not named that, you should get an error trying to refer to it).

Previously, you mentioned that the error "Method or data member not
found"
was being generated by the line of code with MyTextBox. Now that you
corrected the name of that text box, which line of code is generating the
error? If it's the one with Me.Text80, are you sure Text80 is the correct
name? The other thing to do is check that

?[Forms]![frmUnder50KItemsTrackingParms]![BudgetType]

in the Immediate window returns what you expect.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Sorry that I confused you and make you feel that way which I was not my
intention to do so... but I really did not see the thread of the
problem
you
mentioned about wrong table name or field name. I checked, they look
right
to
me... The name of the form is called
frmUnder50KItemsTrackingParms...Would
you please generously point out for me what went wrong?

Thanks a lot!

Ally

:

I noticed that you have another thread in progress on your problem,
with
the
implication being that you've got the wrong table name or field name,
or
that the form isn't open when the code is running.

Please confine each problem to a single thread: otherwise, some of us
end
up
wasting our time.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Here is my final code for the open procedure for the report:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear
strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

End Sub

But I got a error message that I mentioned in my previous response.
Would
you please help?

thanks,

Ally
:

You were supposed to replace "MyTextbox" with the name of the
textbox
to
which you want the data written.

Based on the additional information you've given me, try:

Me.MyTextBox = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

On the other hand, if you're only expecting a single year, have you
made
sure that the MultiSelect property of the FiscalYear list box has
been
set
to None?


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

After I copy the code to the Open event for the report. I got a
compile
error message saying Method or data member not found. The cursor
in
the
code
is staying at the last code: MyTextBox.

Do you know why? Do I need to have a text box in the report which
matches
the same name in the code?

Currently, I have the control named "Text80" that has the
expression
as
following:

="FY " & [Forms]![frmUnder50KItemstrackingParms]![FiscalYear] & "
Under50K
"
& [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & "
Capital
Projects"

Please advise on how I should modify it to match the suggested
code.

Thanks,

Ally

:

That code was intended to be run when the report open. If you
look
at
the
Properies window with the report object selected, set the Open
property
to
[Event Procedure] and click on the ellipsis (...) to the right
of
the
property. That will take you to the VB Editor in the midst of:

Private Sub Report_Open(Cancel As Integer)

End Sub

Put the suggested code there.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Douglas,

Thanks for your help! Sorry for my ignorance, but do I need to
put
the
code
in the form or in the report? If it should be entered in the
form.
How
should
I do with the Text box in the report?

Thanks so much for your time!

Ally

:

Has the list box been set to allow MultiSelect? If so, then
yes,
it
works
differently than a combo box. You won't be able to simply
refer
to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated
list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear"
and a
comb
box
named "BudgetType". I need to print two controls on a
report.
I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not
Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate
Window,
but I
get
Null in the immediate window. I think that's why I could
not
print
out
on
the
report for what I chosed for FiscalYear from the form. but
don't
know
why....Do comb box and list box work different on the
report
when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

Okay, this may be the error to which you were referring elsethread.

Sorry, that was my typo. It should be

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I replace "MyTextbox" in the code with "Text80", which is the name of the
text control in the report. Currently the text box control is empty, which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

I have the Run-Time error "438" saying Object doesn't support this
property
or method. The cursor stays on the follwing line after clicking Debug:

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in the form
as Extended because I do need to choose multiple Fiscal year sometimes.

Please advise.

Thanks,

Ally

Has the list box been set to allow MultiSelect? If so, then yes, it works
differently than a combo box. You won't be able to simply refer to it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list on the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears & Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

 

[Guest]

Douglas,

I still did not make it working. The error message is " You cannot assign a
value to this object. " The cursor stays at the line :

"Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

Please help! BTW, I have a empty text control named Text80 (Unbound) in the
report. Is this right?

Thanks,

Ally

Okay, this may be the error to which you were referring elsethread.

Sorry, that was my typo. It should be

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I replace "MyTextbox" in the code with "Text80", which is the name of the
text control in the report. Currently the text box control is empty, which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

I have the Run-Time error "438" saying Object doesn't support this
property
or method. The cursor stays on the follwing line after clicking Debug:

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in the form
as Extended because I do need to choose multiple Fiscal year sometimes.

Please advise.

Thanks,

Ally

Has the list box been set to allow MultiSelect? If so, then yes, it works
differently than a combo box. You won't be able to simply refer to it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list on the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears & Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a comb
box
named "BudgetType". I need to print two controls on a report. I used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window, but I
get
Null in the immediate window. I think that's why I could not print out
on
the
report for what I chosed for FiscalYear from the form. but don't know
why....Do comb box and list box work different on the report when you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

Yes, Text80 should be unbound.

Is that a single line of code you're showing? (it should be) Is the quote
there at the beginning? (it shouldn't be)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I still did not make it working. The error message is " You cannot assign
a
value to this object. " The cursor stays at the line :

"Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

Please help! BTW, I have a empty text control named Text80 (Unbound) in
the
report. Is this right?

Thanks,

Ally

Okay, this may be the error to which you were referring elsethread.

Sorry, that was my typo. It should be

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I replace "MyTextbox" in the code with "Text80", which is the name of
the
text control in the report. Currently the text box control is empty,
which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

I have the Run-Time error "438" saying Object doesn't support this
property
or method. The cursor stays on the follwing line after clicking Debug:

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in the
form
as Extended because I do need to choose multiple Fiscal year sometimes.

Please advise.

Thanks,

Ally




:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Guest]

Yes Douglas. That's the single line highlighted in yellow after I click the
debug button. My completed code is following:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected

strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

End Sub

Would you please advise?

Thanks a lot!

Ally

Yes, Text80 should be unbound.

Is that a single line of code you're showing? (it should be) Is the quote
there at the beginning? (it shouldn't be)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I still did not make it working. The error message is " You cannot assign
a
value to this object. " The cursor stays at the line :

"Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

Please help! BTW, I have a empty text control named Text80 (Unbound) in
the
report. Is this right?

Thanks,

Ally

Okay, this may be the error to which you were referring elsethread.

Sorry, that was my typo. It should be

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I replace "MyTextbox" in the code with "Text80", which is the name of
the
text control in the report. Currently the text box control is empty,
which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

I have the Run-Time error "438" saying Object doesn't support this
property
or method. The cursor stays on the follwing line after clicking Debug:

For Each varSelected In Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in the
form
as Extended because I do need to choose multiple Fiscal year sometimes.

Please advise.

Thanks,

Ally




:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

Only two possibilities occur to me:

1) Text80 isn't actually a text box: it's some other type of control (such
as a label)
2) [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] isn't a valid
reference

Try:

Private Sub Report_Open(Cancel As Integer)

Dim strOutput As String
Dim strYears As String
Dim varSelected As Variant

With Forms!frmUnder50KItemsTrackingParms!FiscalYear
For Each varSelected In .ItemsSelected
strYears = strYears & .ItemData(varSelected) & ", "
Next varSelected
End With

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

strOutput = "FY " & strYears & " Under50K " & _
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & _
" Capital Projects"
Me.Text80 = strOutput

End Sub

If the line strOutput = ... now raises the error, then the problem is likely
the reference to [Forms]![frmUnder50KItemsTrackingParms]![BudgetType].

If the line Me.Text80 = strOuput now raises the error, then the problem is
with the Text80 control.

I've reformatted the code above so that it shouldn't word-wrap. (I also
added back the ", " which you seem to have dropped.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes Douglas. That's the single line highlighted in yellow after I click
the
debug button. My completed code is following:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected

strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

End Sub

Would you please advise?

Thanks a lot!

Ally

Yes, Text80 should be unbound.

Is that a single line of code you're showing? (it should be) Is the quote
there at the beginning? (it shouldn't be)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas,

I still did not make it working. The error message is " You cannot
assign
a
value to this object. " The cursor stays at the line :

"Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please help! BTW, I have a empty text control named Text80 (Unbound) in
the
report. Is this right?

Thanks,

Ally

Okay, this may be the error to which you were referring elsethread.

Sorry, that was my typo. It should be

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I replace "MyTextbox" in the code with "Text80", which is the name
of
the
text control in the report. Currently the text box control is empty,
which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

I have the Run-Time error "438" saying Object doesn't support this
property
or method. The cursor stays on the follwing line after clicking
Debug:

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in
the
form
as Extended because I do need to choose multiple Fiscal year
sometimes.

Please advise.

Thanks,

Ally




:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not
print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Guest]

Douglas,

Thanks for the patience with my problem!! After doublling check the Text80
in the report, it is the Text Box for sure.

Then I copy the new suggested code and paste it in the Open event for the
report.

After select 2007 as FiscalYear and Contingent as BudgetType from the form,
I got the similar error message as the one I previously received:

Run-time error "-2147352567 (80020009)": You can't assign a value to this
object.

If clicking Debug button, the cursor stays at the last line highlighted in
Yellow:

Me.Text80 = strOutput

Really confused and have no clue.... Please advise!

Thanks,

Ally

Only two possibilities occur to me:

1) Text80 isn't actually a text box: it's some other type of control (such
as a label)
2) [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] isn't a valid
reference

Try:

Private Sub Report_Open(Cancel As Integer)

Dim strOutput As String
Dim strYears As String
Dim varSelected As Variant

With Forms!frmUnder50KItemsTrackingParms!FiscalYear
For Each varSelected In .ItemsSelected
strYears = strYears & .ItemData(varSelected) & ", "
Next varSelected
End With

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

strOutput = "FY " & strYears & " Under50K " & _
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & _
" Capital Projects"
Me.Text80 = strOutput

End Sub

If the line strOutput = ... now raises the error, then the problem is likely
the reference to [Forms]![frmUnder50KItemsTrackingParms]![BudgetType].

If the line Me.Text80 = strOuput now raises the error, then the problem is
with the Text80 control.

I've reformatted the code above so that it shouldn't word-wrap. (I also
added back the ", " which you seem to have dropped.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes Douglas. That's the single line highlighted in yellow after I click
the
debug button. My completed code is following:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected

strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital Projects"

End Sub

Would you please advise?

Thanks a lot!

Ally

Yes, Text80 should be unbound.

Is that a single line of code you're showing? (it should be) Is the quote
there at the beginning? (it shouldn't be)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I still did not make it working. The error message is " You cannot
assign
a
value to this object. " The cursor stays at the line :

"Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please help! BTW, I have a empty text control named Text80 (Unbound) in
the
report. Is this right?

Thanks,

Ally

Okay, this may be the error to which you were referring elsethread.

Sorry, that was my typo. It should be

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I replace "MyTextbox" in the code with "Text80", which is the name
of
the
text control in the report. Currently the text box control is empty,
which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

I have the Run-Time error "438" saying Object doesn't support this
property
or method. The cursor stays on the follwing line after clicking
Debug:

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in
the
form
as Extended because I do need to choose multiple Fiscal year
sometimes.

Please advise.

Thanks,

Ally




:

Has the list box been set to allow MultiSelect? If so, then yes, it
works
differently than a combo box. You won't be able to simply refer to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear" and a
comb
box
named "BudgetType". I need to print two controls on a report. I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate Window,
but I
get
Null in the immediate window. I think that's why I could not
print
out
on
the
report for what I chosed for FiscalYear from the form. but don't
know
why....Do comb box and list box work different on the report when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally

 

[Douglas J. Steele]

Aargh. The control probably hasn't been instantiated yet in the Open event.

Try:

Me.Text80.Value = strOutput

If that still doesn't work, try using the report's Activate event instead.

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,

Thanks for the patience with my problem!! After doublling check the Text80
in the report, it is the Text Box for sure.

Then I copy the new suggested code and paste it in the Open event for the
report.

After select 2007 as FiscalYear and Contingent as BudgetType from the
form,
I got the similar error message as the one I previously received:

Run-time error "-2147352567 (80020009)": You can't assign a value to this
object.

If clicking Debug button, the cursor stays at the last line highlighted in
Yellow:

Me.Text80 = strOutput

Really confused and have no clue.... Please advise!

Thanks,

Ally

Only two possibilities occur to me:

1) Text80 isn't actually a text box: it's some other type of control
(such
as a label)
2) [Forms]![frmUnder50KItemsTrackingParms]![BudgetType] isn't a valid
reference

Try:

Private Sub Report_Open(Cancel As Integer)

Dim strOutput As String
Dim strYears As String
Dim varSelected As Variant

With Forms!frmUnder50KItemsTrackingParms!FiscalYear
For Each varSelected In .ItemsSelected
strYears = strYears & .ItemData(varSelected) & ", "
Next varSelected
End With

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

strOutput = "FY " & strYears & " Under50K " & _
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & _
" Capital Projects"
Me.Text80 = strOutput

End Sub

If the line strOutput = ... now raises the error, then the problem is
likely
the reference to [Forms]![frmUnder50KItemsTrackingParms]![BudgetType].

If the line Me.Text80 = strOuput now raises the error, then the problem
is
with the Text80 control.

I've reformatted the code above so that it shouldn't word-wrap. (I also
added back the ", " which you seem to have dropped.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes Douglas. That's the single line highlighted in yellow after I click
the
debug button. My completed code is following:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected

strYears = strYears &
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemData(varSelected)
Next varSelected
If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

End Sub

Would you please advise?

Thanks a lot!

Ally

:

Yes, Text80 should be unbound.

Is that a single line of code you're showing? (it should be) Is the
quote
there at the beginning? (it shouldn't be)

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I still did not make it working. The error message is " You cannot
assign
a
value to this object. " The cursor stays at the line :

"Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

Please help! BTW, I have a empty text control named Text80 (Unbound)
in
the
report. Is this right?

Thanks,

Ally

Okay, this may be the error to which you were referring elsethread.

Sorry, that was my typo. It should be

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear.ItemsSelected


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas,

I replace "MyTextbox" in the code with "Text80", which is the
name
of
the
text control in the report. Currently the text box control is
empty,
which
has no expression on the report. Is this right?

After placing the suggested code as following in the code:
Me.Text80 = "FY " & strYears & " Under50K " &
[Forms]![frmUnder50KItemsTrackingParms]![BudgetType] & " Capital
Projects"

I have the Run-Time error "438" saying Object doesn't support
this
property
or method. The cursor stays on the follwing line after clicking
Debug:

For Each varSelected In
Forms!frmUnder50KItemsTrackingParms!FiscalYear

Also, I set the MultiSlect property of the FiscalYear list box in
the
form
as Extended because I do need to choose multiple Fiscal year
sometimes.

Please advise.

Thanks,

Ally




:

Has the list box been set to allow MultiSelect? If so, then yes,
it
works
differently than a combo box. You won't be able to simply refer
to
it:
you'll need VBA to loop through its ItemsSelected collection,
concatenating
all of the selected items and then putting that concatenated
list
on
the
report.

Something like this should do it:

Private Sub Report_Open(Cancel As Integer)

Dim strYears As String
Dim varSelected As Variant

For Each varSelected In Forms!frmForm1!FiscalYear
strYears = strYears &
Forms!frmForm1!FiscalYear.ItemData(varSelected)
&
", "
Next varSelected

If Len(strYears) > 0 Then
strYears = Left$(strYears, Len(strYears) - 2)
End If

Me.MyTextBox = strYears

End Sub

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two controls in a form: a list box named "FiscalYear"
and a
comb
box
named "BudgetType". I need to print two controls on a report.
I
used
the
follwing in the text control on the report:

=Forms!frmForm1!BudgetType and
=Forms!frmForm1!FiscalYear

However, when it works for budget type control but not Fiscal
year.

I did put " Print Forms!frmForm1!FiscalYear" to Immediate
Window,
but I
get
Null in the immediate window. I think that's why I could not
print
out
on
the
report for what I chosed for FiscalYear from the form. but
don't
know
why....Do comb box and list box work different on the report
when
you
are
trying to print out the control? Does anyone know how?

Thanks a lot!

Ally