Listbox on report problem

  • Thread starter Thread starter antgel
  • Start date Start date
A

antgel

Hi all,

I have a report with two listboxes on. I want the data in the listbox to
change depending on a field called articleId, which is available from the
report's bound query.

Here's the listbox SQL:

SELECT itemMaterial.id, itemMaterial.articleId, materialDetail.company,
materialDetail.model, materialDetail.colour, itemMaterial.quantity,
materialDetail.unitCost, [quantity]*[unitCost] AS lineCost FROM
materialDetail INNER JOIN itemMaterial ON
materialDetail.variationId=itemMaterial.materialId WHERE
(((itemMaterial.articleId)=Me!articleId));

However, a box comes up asking me for the value of Me!articleId - I want
this to be taken from the report's bound query! I have also tried
WHERE itemmaterial.articleid=reports!rptcosting.articleid;
but that just makes Access crash.

Then I rethought my approach. I used the above once, successfully, in a
subform when I wanted to access the main form's value. But here I have no
sub-anything, so why not make the listbox bound? So:

I tried dropping the WHERE clause, and making the listbox bound, e.g. for
the above listbox, setting controlSource to articleId, and boundColumn to 2.
But this just populates the listbox with the query results for all values of
articleId.

I must have missed something stupid. I can provide more info if necessary.

Antony
 
First, clarify to help me understand why you want to use a Control (List
Box) intended for the user to choose data on a Report with which a user
cannot, by definition, interact.

Second, by "Listbox SQL" do you mean the RowSource for the ListBox? If so,
the Me shortcut cannot be used... it can only be used in VBA code in the
Form's or Report's own module.

Third, is the "Me!articleID" to which you refer actually a Control on the
Report, or a Field in the RecordSource of the Report? If the latter, add the
table containing the additional information to the Query, join on the
ArticleID fields, drag those fields into the Query grid, and display them in
Text Boxes.

If I've given you the correct answer above, please note which, for the
benefit of others who read this. If not, please clarify and perhaps someone
can be of help.

Larry Linson
Microsoft Access MVP
 
Back
Top