MS Access 2007 - Report w/ listbox based on current textbox value

[Eniola]

Hi,

I have a report with the following design:
* Table1 has PK: Field1 (and additional fields).
* Table2 has Field1 & and Field2 fields, which can of course contain each
Field1. (that is unique in Table1) in more than one row (based on the
Field1,Field2 combination). Table2 can be joined to Table1 based on Field1
in both tables.
* Report has textbox: tbField1 and listbox: lbField2.
* Report is designed to have a row for each distinct Field1 from Table1
(displayed using tbField1), and all Field2 values for each respective Field1
from Table2 (displayed using lbField2). (i.e. Table1 may have values 1,2,3 as
3 rows in Field1. Table2 may have values (1,1), (1,5), (1,3), (2,3), (2,10),
(3,4) as 6 rows in (Field1, Field2). So the Report should display 3 rows:
tbField1 should display values 1, 2, and 3. lbField2 should display (1,5,3)
in row 1, (3,10) in row 2, and (4) in row 3.
* The Report Record Source is a query that pulls all of the fields I need
from Table1 (i.e. including Field1).
* I have a query: SELECT Field2 FROM Table2 WHERE Field1 = tbField1

PROBLEM:
1) should this query go into the lbfield2 Control Source, or Row Source?
2) should I set the Row Source Type to "Table/Query"?
3) should i set the Bound Column?
4) should I set the Column Count and/or Column Widths?
5) Instead of working with the Property Sheet, should I instead insert this
query into the VBA code? I have tried placing it in the Report's Detail
Section's "On Paint" and/or "On Print" Event Procedures (i.e.
Me.lbField2.ControlSource = "SELECT Field2 FROM Table2 WHERE Field1 = " &
Me.tbField1 & " ;") to no avail. I have also tried combinations of options
1) - 4) above as well, to no avail. I know there is some combination of the
above that works... any suggestions? Please let me know if my question is
not clear.

All comments will be VERY MUCH APPRECIATED!! Thank you,

 

[KARL DEWEY]

All this ezplaination sounds like home work.
Use one query --
SELECT Table1.Field1, Table2.Field1, Table2.Field2
FROM Table1 LEFT JOIN Table2 ON Table1.Field1 = Table2.Field1;

 

[EOk]

Hi Karl,

The problem is not with the query. I don't know how to make the list box
refer to the text box. Do I do it in the Properties? In the VBA?

 

[KARL DEWEY]

Why a listbox in a report?
Why make the list box refer to the text box?
There is no need. Just use a textbox with the data from the query.

 

[EOk]

Hi Karl,

A List box because there are several values in Field2 that can relate to 1
value on Field1. Make sense? If I used a text box, I can only capture one
value.

 

[Larry Linson]

A List box because there are several values in
Field2 that can relate to 1 value on Field1. Make
sense? If I used a text box, I can only capture
one value.

Consider using a Subreport Control to display all the related records.

Larry Linson
Microsoft Office Access MVP