Open a report from a listbox

[Guest]

I have a report that has is grouped into an employee and then by weeks and
then there daily production, information comes from a query. I have unbound
form that has a listbox which groups the employees information into
weeks(since there production is entered daily) The following code is in the
event prodcedure of the listbox
***********************************************************
Private Sub List4_DblClick(Cancel As Integer)
On Error GoTo Err_List4_DblClick

Dim DocName As String

DocName = "Daily Employee tracking"

DoCmd.OpenReport DocName, acPreview, , "[weeks]=' " &
Forms![WeeklyEmployee]![List4] & " ' "

Exit_List4_DblClick:
Exit Sub

Err_List4_DblClick:
MsgBox Err.Description
Resume Exit_List4_DblClick

End Sub
**********************************************************
The problem is that when I double click the row that I want (displays the
week number e.g., 32) , I get a message that says no records(this is an event
procedure in the no data--- msgbox comes up with no records) but there are
records, if I take out the single quotes, I get data type mismatch.

Any help on how to take care of this!

Please help!

 

[Guest]

Hi.

week number e.g., 32) , I get a message that says no records(this is an event
procedure in the no data--- msgbox comes up with no records) but there are
records, if I take out the single quotes, I get data type mismatch.

"weeks" is a text field, not a numerical field and must therefore use the
single quotes to delineate the string value passed to the report's filter
property. The behaviors you are experiencing would be consistent with a
numerical value (32) being passed to the report based upon a query that has
the "weeks" field, which is a text data type, but where no row contains 32.

Could it be that fields have been renamed in the table, or that you are
using the "Caption" property of the field instead of the field name in your
WHERE condition in the VBA code, or that "weeks" is a calculated field that
doesn't contain the value 32 at the time this particular report runs, but
does at other times when you've compared it to see whether it works?

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I have a report that has is grouped into an employee and then by weeks and
then there daily production, information comes from a query. I have unbound
form that has a listbox which groups the employees information into
weeks(since there production is entered daily) The following code is in the
event prodcedure of the listbox
***********************************************************
Private Sub List4_DblClick(Cancel As Integer)
On Error GoTo Err_List4_DblClick

Dim DocName As String

DocName = "Daily Employee tracking"

DoCmd.OpenReport DocName, acPreview, , "[weeks]=' " &
Forms![WeeklyEmployee]![List4] & " ' "

Exit_List4_DblClick:
Exit Sub

Err_List4_DblClick:
MsgBox Err.Description
Resume Exit_List4_DblClick

End Sub
**********************************************************
The problem is that when I double click the row that I want (displays the
week number e.g., 32) , I get a message that says no records(this is an event
procedure in the no data--- msgbox comes up with no records) but there are
records, if I take out the single quotes, I get data type mismatch.

Any help on how to take care of this!

Please help!

 

[Guest]

Thanks, I actualy did figure out what the problem was...

 

['69 Camaro]

Good job! Care to share your solution so that if anyone else finds himself
following in your footsteps, he can use the same solution?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

Thanks, I actualy did figure out what the problem was...

I have a report that has is grouped into an employee and then by weeks
and
then there daily production, information comes from a query. I have
unbound
form that has a listbox which groups the employees information into
weeks(since there production is entered daily) The following code is in
the
event prodcedure of the listbox
***********************************************************
Private Sub List4_DblClick(Cancel As Integer)
On Error GoTo Err_List4_DblClick

Dim DocName As String

DocName = "Daily Employee tracking"

DoCmd.OpenReport DocName, acPreview, , "[weeks]=' " &
Forms![WeeklyEmployee]![List4] & " ' "

Exit_List4_DblClick:
Exit Sub

Err_List4_DblClick:
MsgBox Err.Description
Resume Exit_List4_DblClick

End Sub
**********************************************************
The problem is that when I double click the row that I want (displays the
week number e.g., 32) , I get a message that says no records(this is an
event
procedure in the no data--- msgbox comes up with no records) but there
are
records, if I take out the single quotes, I get data type mismatch.

Any help on how to take care of this!

Please help!

 

[Guest]

Well there was an extra space after the = sign!

Good job! Care to share your solution so that if anyone else finds himself
following in your footsteps, he can use the same solution?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

Thanks, I actualy did figure out what the problem was...

I have a report that has is grouped into an employee and then by weeks
and
then there daily production, information comes from a query. I have
unbound
form that has a listbox which groups the employees information into
weeks(since there production is entered daily) The following code is in
the
event prodcedure of the listbox
***********************************************************
Private Sub List4_DblClick(Cancel As Integer)
On Error GoTo Err_List4_DblClick

Dim DocName As String

DocName = "Daily Employee tracking"

DoCmd.OpenReport DocName, acPreview, , "[weeks]=' " &
Forms![WeeklyEmployee]![List4] & " ' "

Exit_List4_DblClick:
Exit Sub

Err_List4_DblClick:
MsgBox Err.Description
Resume Exit_List4_DblClick

End Sub
**********************************************************
The problem is that when I double click the row that I want (displays the
week number e.g., 32) , I get a message that says no records(this is an
event
procedure in the no data--- msgbox comes up with no records) but there
are
records, if I take out the single quotes, I get data type mismatch.

Any help on how to take care of this!

Please help!

 

[Guest]

Hi.

Well there was an extra space after the = sign!

I must confess. I didn't believe you.

It doesn't matter if there are extra spaces in the VBA syntax when the value
being passed is a number -- and the value 32 is indeed a number. But the
requirement for the single quotes to prevent the type mismatch error clearly
shows that a string value is being passed to the report, not a number.

So I thought to myself, "What would happen if . . . I built the list box
incorrectly?" It turns out that if one assigns the wrong column to bind the
list box to, then a different value will be assigned to the list box than the
one that may be showing. To see what value is _really_ being passed to your
report, copy and paste the following line of code into your list box's
DoubleClick( ) event:

MsgBox "*" & "[weeks]=' " & Forms![WeeklyEmployee]![List4] & " ' " & "*"

Compile the code, then open the form in Form View and double click on a
number in the list box that you know should have records. You'll see the
value that's really being sent to the report between the asterisks. Even
though you've got it working, I suggest that you change the bound column to
the correct column, because it's very likely that you'll be chasing more bugs
from this list box in the future. And you'll be able to get rid of those
single quotes in the syntax, too.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Well there was an extra space after the = sign!

Good job! Care to share your solution so that if anyone else finds himself
following in your footsteps, he can use the same solution?

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)

Thanks, I actualy did figure out what the problem was...

:

I have a report that has is grouped into an employee and then by weeks
and
then there daily production, information comes from a query. I have
unbound
form that has a listbox which groups the employees information into
weeks(since there production is entered daily) The following code is in
the
event prodcedure of the listbox
***********************************************************
Private Sub List4_DblClick(Cancel As Integer)
On Error GoTo Err_List4_DblClick

Dim DocName As String

DocName = "Daily Employee tracking"

DoCmd.OpenReport DocName, acPreview, , "[weeks]=' " &
Forms![WeeklyEmployee]![List4] & " ' "

Exit_List4_DblClick:
Exit Sub

Err_List4_DblClick:
MsgBox Err.Description
Resume Exit_List4_DblClick

End Sub
**********************************************************
The problem is that when I double click the row that I want (displays the
week number e.g., 32) , I get a message that says no records(this is an
event
procedure in the no data--- msgbox comes up with no records) but there
are
records, if I take out the single quotes, I get data type mismatch.

Any help on how to take care of this!

Please help!