Cant get a report working from a form

[pragv]

Hi,
I have a report that gets the data from a query. The query uses an
inner join between two tables. The report works fine. The report
contains information about several people. I now want to filter the
data. I used a form with a combo box (based on a table). It contains a
list of people. When a selection is made in the combo box and a button
is clicked, I would like to show the information relevant to the
selected person.
I used the following code for the click button
DoCmd.OpenReport "rptAVSP", acViewPreview, , "Person_Name=" &
forms![frmperson]List

But that doesn't seem to work. So I also tried putting a WHERE
condition in the query (on which the fomr is based) like

SELECT tblhrs.Person_Name, tblhrs.acthrs, tblpct.pcthrs (and so on...)
FROM tblhrs INNER JOIN tblpct ON
(tblhrs.Person_Name=tblpct.Person_Name)
WHERE tblhrs.Person_Name=forms![frmperson]List

When I run the report from the form(frmperson), the report shows no
result at all!
This time, I used the following code as I already have the condition in
the query
DoCmd.OpenReport "rptAVSP", acViewPreview

I know I'm making a simple mistake somewhere but cant really figure
out.
Sometimes, in the first case, the entire query gets erased too...

Can anyone please help me here.
Thanks
Pragv

 

[Duane Hookom]

Your statement needs to have a ! to the left of List
DoCmd.OpenReport "rptAVSP", acViewPreview, , _
"Person_Name=" & forms![frmperson]!

• 
  
  Also I expect Person_Name is text so you would need to add some quotes:
  DoCmd.OpenReport "rptAVSP", acViewPreview, , _
  "Person_Name=""" & forms![frmperson]!
  • & """"
    
    This also depends on the bound column of your combo box being the person
    name and not some ID or autonumber.

 

[pragv]

Thank you for the quick reply
Yes, the Person_Name is text. The combo box is unbound but contains a
list of person names from tblPerson (that just has a personid and
Person_Name fields) as it's row source.
I put the extra quotes and the ! as you mentioned.
The report opens up when the button is clicked but it just has blank
and no information in it!!
The query I now used (for the report) is

SELECT tblhrs.Person_Name, tblhrs.acthrs, tblpct.pcthrs (and so on...)
FROM tblhrs INNER JOIN tblpct ON
(tblhrs.Person_Name=tblpct.Person_Name)

without the WHERE clause.

Did I make a mistake somewhere. Do I somehow need to connect the
selection in the combo box to the query?
I appreciate your help.
Thanks

Your statement needs to have a ! to the left of List
DoCmd.OpenReport "rptAVSP", acViewPreview, , _
"Person_Name=" & forms![frmperson]!

• 
  
  Also I expect Person_Name is text so you would need to add some quotes:
  DoCmd.OpenReport "rptAVSP", acViewPreview, , _
  "Person_Name=""" & forms![frmperson]!
  • & """"
    
    This also depends on the bound column of your combo box being the person
    name and not some ID or autonumber.
    
    
    --
    Duane Hookom
    MS Access MVP
    
    
    
    

    Hi,
    I have a report that gets the data from a query. The query uses an
    inner join between two tables. The report works fine. The report
    contains information about several people. I now want to filter the
    data. I used a form with a combo box (based on a table). It contains a
    list of people. When a selection is made in the combo box and a button
    is clicked, I would like to show the information relevant to the
    selected person.
    I used the following code for the click button
    DoCmd.OpenReport "rptAVSP", acViewPreview, , "Person_Name=" &
    forms![frmperson]List
    
    But that doesn't seem to work. So I also tried putting a WHERE
    condition in the query (on which the fomr is based) like
    
    SELECT tblhrs.Person_Name, tblhrs.acthrs, tblpct.pcthrs (and so on...)
    FROM tblhrs INNER JOIN tblpct ON
    (tblhrs.Person_Name=tblpct.Person_Name)
    WHERE tblhrs.Person_Name=forms![frmperson]List
    
    When I run the report from the form(frmperson), the report shows no
    result at all!
    This time, I used the following code as I already have the condition in
    the query
    DoCmd.OpenReport "rptAVSP", acViewPreview
    
    I know I'm making a simple mistake somewhere but cant really figure
    out.
    Sometimes, in the first case, the entire query gets erased too...
    
    Can anyone please help me here.
    Thanks
    Pragv

 

[Duane Hookom]

Your combo box has both the PersonID and the Person_Name fields. Which one
is the bound column? I expect you are attempting to filter the report where
the combo box is the PersonID which will not match the Person_Name in the
report.

--
Duane Hookom
MS Access MVP

Thank you for the quick reply
Yes, the Person_Name is text. The combo box is unbound but contains a
list of person names from tblPerson (that just has a personid and
Person_Name fields) as it's row source.
I put the extra quotes and the ! as you mentioned.
The report opens up when the button is clicked but it just has blank
and no information in it!!
The query I now used (for the report) is

SELECT tblhrs.Person_Name, tblhrs.acthrs, tblpct.pcthrs (and so on...)
FROM tblhrs INNER JOIN tblpct ON
(tblhrs.Person_Name=tblpct.Person_Name)

without the WHERE clause.

Did I make a mistake somewhere. Do I somehow need to connect the
selection in the combo box to the query?
I appreciate your help.
Thanks

Your statement needs to have a ! to the left of List
DoCmd.OpenReport "rptAVSP", acViewPreview, , _
"Person_Name=" & forms![frmperson]!

• 
  
  Also I expect Person_Name is text so you would need to add some quotes:
  DoCmd.OpenReport "rptAVSP", acViewPreview, , _
  "Person_Name=""" & forms![frmperson]!
  • & """"
    
    This also depends on the bound column of your combo box being the person
    name and not some ID or autonumber.
    
    
    --
    Duane Hookom
    MS Access MVP
    
    
    
    

    Hi,
    I have a report that gets the data from a query. The query uses an
    inner join between two tables. The report works fine. The report
    contains information about several people. I now want to filter the
    data. I used a form with a combo box (based on a table). It contains a
    list of people. When a selection is made in the combo box and a button
    is clicked, I would like to show the information relevant to the
    selected person.
    I used the following code for the click button
    DoCmd.OpenReport "rptAVSP", acViewPreview, , "Person_Name=" &
    forms![frmperson]List
    
    But that doesn't seem to work. So I also tried putting a WHERE
    condition in the query (on which the fomr is based) like
    
    SELECT tblhrs.Person_Name, tblhrs.acthrs, tblpct.pcthrs (and so on...)
    FROM tblhrs INNER JOIN tblpct ON
    (tblhrs.Person_Name=tblpct.Person_Name)
    WHERE tblhrs.Person_Name=forms![frmperson]List
    
    When I run the report from the form(frmperson), the report shows no
    result at all!
    This time, I used the following code as I already have the condition in
    the query
    DoCmd.OpenReport "rptAVSP", acViewPreview
    
    I know I'm making a simple mistake somewhere but cant really figure
    out.
    Sometimes, in the first case, the entire query gets erased too...
    
    Can anyone please help me here.
    Thanks
    Pragv

 

[pragv]

Thank you very much for pointing out. I actually had a SELECT statement
that selects the Person_Name as the row source for the combo box and so
didnt bother to change the bound column number to 2 in the combo box
properties. The box was also showing the names.

Now when I changed it, it started working!
Thanks again.This was helpful.