Create a report using a parameter from a combo box

[fieldja]

I have a form called OwnerForm. It contains a combo box called
Owner. The combo box looks up names from a table called OwnerName.
It contains fields called OwnerID and Owner. I also have a main form
called ProjectsForm. This form has several fields to enter data. I
have a query that is called Owner Query. This query contains the
fields IDNumber.Projects2 Team.Projects2 and Owner.OwnerName getting
information from tables.

On the criteria of Owner I have:

forms!OwnerForm!Owner.

I want the query to only show the data from the Projects2 table that
matches the name on the combo box. I made a report called Owner
Report. I have a command button on the OwnerForm form which opens the
report in Preview. I can't seem to get the report to show any data.
It is a blank report. When I open the report from the form it is
blank. When I open the report from the report list it sends me to the
form I choose the name from the combo box then click on the command
button, it prompts me to a Enter Parameter Value. When I type in a
name it will generate the report with the proper information, but I
want the combo box to send it to the report and not have to type it
in. The record source for the report is

SELECT [OwnerName].[Owner], [Projects2].[ID Number], [Projects2].
[Team] FROM OwnerName INNER JOIN Projects2 ON [OwnerName].
[Owner]=[Projects2].[Owner] WHERE ((([OwnerName].[Owner])=[forms]!
[OwnerForm]![Owner]));

I also have the report open and close events as follows: It seems to
work ok.

Private Sub Report_Close()
DoCmd.Close acForm, "Owner Report"

End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "OwnerForm", , , , , acDialog

Any ideas would be appreciated.
End Sub

[n00b]

On Jan 30, 11:22 am, "fieldja" <[e-mail]:

I have a form called OwnerForm. It contains a combo box called
Owner. The combo box looks up names from a table called OwnerName.
It contains fields called OwnerID and Owner. I also have a main form
called ProjectsForm. This form has several fields to enter data. I
have a query that is called Owner Query. This query contains the
fields IDNumber.Projects2 Team.Projects2 and Owner.OwnerName getting
information from tables.

On the criteria of Owner I have:

forms!OwnerForm!Owner.

I want the query to only show the data from the Projects2 table that
matches the name on the combo box. I made a report called Owner
Report. I have a command button on the OwnerForm form which opens the
report in Preview. I can't seem to get the report to show any data.
It is a blank report. When I open the report from the form it is
blank. When I open the report from the report list it sends me to the
form I choose the name from the combo box then click on the command
button, it prompts me to a Enter Parameter Value. When I type in a
name it will generate the report with the proper information, but I
want the combo box to send it to the report and not have to type it
in. The record source for the report is

SELECT [OwnerName].[Owner], [Projects2].[ID Number], [Projects2].
[Team] FROM OwnerName INNER JOIN Projects2 ON [OwnerName].
[Owner]=[Projects2].[Owner] WHERE ((([OwnerName].[Owner])=[forms]!
[OwnerForm]![Owner]));

I also have the report open and close events as follows: It seems to
work ok.

Private Sub Report_Close()
DoCmd.Close acForm, "Owner Report"

End Sub

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "OwnerForm", , , , , acDialog

Any ideas would be appreciated.
End Sub

My guess is you have the BoundColumn incorrectly set on the "Owner"
combo box to the default of 1, which looks to be the field
IDNumber.Projects2 in your query. Set the Bound Column property to
3, which is the Owner.OwnerName in your query. You want to make sure
the value of the combo box is the name of the owner of the project
because that is the criteria you are basing the report on. The Bound
Column property determines the field from the query you have specified
in the Row Source that should be used as the value of the combo box.

Regards,

n00b

[Mike]

Why don't you use the filter criteria when opening the report.

See the following kb-article for an example:
http://support.microsoft.com/kb/q147143/

[fieldja]

On Jan 30, 11:38 am, "Mike" <[e-mail]:

Why don't you use the filter criteria when opening the report.

See the following kb-article for an example:http://support.microsoft.com/kb/q147143/

Thanks for the information.

I think I am getting close to making it work, but I am still having
problems.

When I run the filter I get an error message:
Syntax error (missing operator) in query expression '[Team] = "Clutch
Plate" An)'.
The An should be And.
I don't know why it is cutting that word off?
I also, don't know why it won't work for me? I applied the example to
my application and changed the table names and fields to my own
names. Also, I only wanted 3 combo boxes instead of 5, like in the
example. So I changed those values to 3.

Here is my Filter:

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer 'Build SQL string.
For intCounter = 1 To 3
If Me("Filter" & intCounter) <" " Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "]
" & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <"" Then ' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 3)) ' Set the Filter
Property.
Reports![rptOwner].Filter = strSQL
Reports![rptOwner].FilterOn = True
End If

End Sub
I have a table called Projects2 that I am using to get information
from. I have 3 combo boxes. Owner, Team, Status.

Here are the Row Sources:
SELECT DISTINCT [Owner] FROM Projects2 ORDER BY [Owner];

SELECT DISTINCT [Team] FROM Projects2 ORDER BY [Team];

SELECT DISTINCT [Status] FROM Projects2 ORDER BY [Status];

Is the "Filter" in the Set_Filter code correct? Or does it need to
read "Set Filter"?

I am at a loss as what to do, any help would be appreciative.

[fieldja]

On Feb 6, 10:39 am, "fieldja" <[e-mail]:

On Jan 30, 11:38 am, "Mike" <[e-mail]:

Why don't you use the filter criteria when opening the report.

See the following kb-article for an example:http://support.microsoft.com/kb/q147143/

Thanks for the information.

I think I am getting close to making it work, but I am still having
problems.

When I run the filter I get an error message:
Syntax error (missing operator) in query expression '[Team] = "Clutch
Plate" An)'.
The An should be And.
I don't know why it is cutting that word off?
I also, don't know why it won't work for me? I applied the example to
my application and changed the table names and fields to my own
names. Also, I only wanted 3 combo boxes instead of 5, like in the
example. So I changed those values to 3.

Here is my Filter:

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer 'Build SQL string.
For intCounter = 1 To 3
If Me("Filter" & intCounter) <" " Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "]
" & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <"" Then ' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 3)) ' Set the Filter
Property.
Reports![rptOwner].Filter = strSQL
Reports![rptOwner].FilterOn = True
End If

End Sub

I have a table called Projects2 that I am using to get information
from. I have 3 combo boxes. Owner, Team, Status.

Here are the Row Sources:
SELECT DISTINCT [Owner] FROM Projects2 ORDER BY [Owner];

SELECT DISTINCT [Team] FROM Projects2 ORDER BY [Team];

SELECT DISTINCT [Status] FROM Projects2 ORDER BY [Status];

Is the "Filter" in the Set_Filter code correct? Or does it need to
read "Set Filter"?

I am at a loss as what to do, any help would be appreciative.

I think I got it!

I had spaces in between some the "" and also in the "and" section of
the code. Once I deleted those spaces the filter worked.

Thanks again.

[fieldja]

On Feb 6, 10:59 am, "fieldja" <[e-mail]:

On Feb 6, 10:39 am, "fieldja" <[e-mail]:

On Jan 30, 11:38 am, "Mike" <[e-mail]:

Why don't you use the filter criteria when opening the report.

See the following kb-article for an example:http://support.microsoft.com/kb/q147143/

Thanks for the information.

I think I am getting close to making it work, but I am still having
problems.

When I run the filter I get an error message:
Syntax error (missing operator) in query expression '[Team] = "Clutch
Plate" An)'.
The An should be And.
I don't know why it is cutting that word off?
I also, don't know why it won't work for me? I applied the example to
my application and changed the table names and fields to my own
names. Also, I only wanted 3 combo boxes instead of 5, like in the
example. So I changed those values to 3.

Here is my Filter:

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer 'Build SQL string.
For intCounter = 1 To 3
If Me("Filter" & intCounter) <" " Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "]
" & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <"" Then ' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 3)) ' Set the Filter
Property.
Reports![rptOwner].Filter = strSQL
Reports![rptOwner].FilterOn = True
End If

End Sub

I have a table called Projects2 that I am using to get information
from. I have 3 combo boxes. Owner, Team, Status.

Here are the Row Sources:
SELECT DISTINCT [Owner] FROM Projects2 ORDER BY [Owner];

SELECT DISTINCT [Team] FROM Projects2 ORDER BY [Team];

SELECT DISTINCT [Status] FROM Projects2 ORDER BY [Status];

Is the "Filter" in the Set_Filter code correct? Or does it need to
read "Set Filter"?

I am at a loss as what to do, any help would be appreciative.

I think I got it!

I had spaces in between some the "" and also in the "and" section of
the code. Once I deleted those spaces the filter worked.

Thanks again.- Hide quoted text -

- Show quoted text -

Sorry, I thought it worked but it did not.

[fieldja]

On Feb 6, 3:14 pm, "fieldja" <[e-mail]:

On Feb 6, 10:59 am, "fieldja" <[e-mail]:

On Feb 6, 10:39 am, "fieldja" <[e-mail]:

On Jan 30, 11:38 am, "Mike" <[e-mail]:

Why don't you use the filter criteria when opening the report.

See the following kb-article for an example:http://support.microsoft.com/kb/q147143/

Thanks for the information.

I think I am getting close to making it work, but I am still having
problems.

When I run the filter I get an error message:
Syntax error (missing operator) in query expression '[Team] = "Clutch
Plate" An)'.
The An should be And.
I don't know why it is cutting that word off?
I also, don't know why it won't work for me? I applied the example to
my application and changed the table names and fields to my own
names. Also, I only wanted 3 combo boxes instead of 5, like in the
example. So I changed those values to 3.

Here is my Filter:

Private Sub Set_Filter_Click()

Dim strSQL As String, intCounter As Integer 'Build SQL string.
For intCounter = 1 To 3
If Me("Filter" & intCounter) <" " Then

strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "]
" & " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <"" Then ' Strip Last " And ".
strSQL = Left(strSQL, (Len(strSQL) - 3)) ' Set the Filter
Property.
Reports![rptOwner].Filter = strSQL
Reports![rptOwner].FilterOn = True
End If

End Sub

I have a table called Projects2 that I am using to get information
from. I have 3 combo boxes. Owner, Team, Status.

Here are the Row Sources:
SELECT DISTINCT [Owner] FROM Projects2 ORDER BY [Owner];

SELECT DISTINCT [Team] FROM Projects2 ORDER BY [Team];

SELECT DISTINCT [Status] FROM Projects2 ORDER BY [Status];

Is the "Filter" in the Set_Filter code correct? Or does it need to
read "Set Filter"?

I am at a loss as what to do, any help would be appreciative.

I think I got it!

I had spaces in between some the "" and also in the "and" section of
the code. Once I deleted those spaces the filter worked.

Thanks again.- Hide quoted text -

- Show quoted text -

Sorry, I thought it worked but it did not.- Hide quoted text -

- Show quoted text -

I was able to get the filter to work on all the combo boxes except for
the ID Number box. Is that because the data is an auto number instead
of text?

Any ideas?