Problem displaying related data in Report

[zakrocz]

Hi

I have a quotation form that allows the user to enter up to 20 products (20 corresponding fields numbered "FieldName" + 1 to 20) from the products table onto the quotation form/table.

In the form I use Dlookup to display the product name into a text box that is only for screen display purposes. All that's stored in the form's record is the ProductID numbers.

My problem is when I try to base a report on the form I have no way of displaying the product names. I could do with some kind of lookup feature in reports but there doesn't appear to be any way to get a field in a report to do something similar to Dlookup.

The only other way I can think of doing it would be to include 20 new fields in my Quotation table for corresponding product names, but this would lead to huge records, an extra 1000 bytes per record to store the extra info.

Can anyone help?

 

[Maquis]

Well, I'm going to assume you have a table somewhere in your database which contains product ids and product names.

You can base your report on this table and include fields on the report for the Id and Name. (Just 1 field for each, not 20 fields).

Then have your form call the report and pass it the ids of the products that your user has entered. Maq
<insert witty signature here>

 

[zakrocz]

Sorry you've lost me.

What would be the point of basing my report on the products table?

My report is based on my Quotations table, which holds 80 fields for the user to enter up to 20 products, qty, Price $, Price £.

On the Quotation form I have 20 combo boxes that allows the user to select a product from the product table, each time the user does this, I use DLookup to display the product name.

But Access Reports wont allow me to use the same method to print out the product names on a report. This is such a simple task in Visual Basic, but in Access it is like finding the answer to the meaning of life.

 

[Maquis]

Ok, I missed the point. I thought that the only fields you wanted on the report were just Product id and name.

Do you have a product id field in your quotations table? If so, all you need is a query that joins it to the Product table. Just include all your quotation fields + the Product name in the query. You can base your form and report on that query to eliminate all your Dlookups.

I hope that made more sense to you. Maq
<insert witty signature here>

 

[zakrocz]

My fault this time not explaining the following

My product table is made up of 3 fields

1. ProductID
2. Product Code
3. Product Name

My form has 20 combo box fields for the user to enter up to 20 products to quote.

When the user selects a product from the combobox all the combobox displays is the product code, not the product name.

That is why I use DLookup to display the product name, I can't display it any other way.

So when I go to reports then I have the problem of displaying the product name.

Displaying the product code on the report works, but it's the displaying/printing of the Product Name that is the problem.

When the user clicks the command button Print Quotation (cmdPrint), I ask the user to enter the Quotation Number via the query that the report is based on. (I had to create a multiple table query to be able to get the report to include all the fields because I have too many fields for the Report Wizard to work, and if I try and create the report in design view as soon as I start to mess about with the forms control source to be able to add all the fields from the 3 tables I get asked to enter parameter values for every field on the report.)

I then open the report in cmdPrint and add some code which uses Dlookup to add the product name to an unbound text box on the report, and sometimes it works, and then sometimes it doesn't!!!!!!!!!!!!!!!!!!!!

This is the problem with so called higher programming visual languages, the programmer loses control of the flow of the program and as a result receives totally illogical results due to events happening that the programmer is totally unaware of.

I feel I am one tiny step away from achieving success, but that tiny step is alluding me. I've fought tooth and nail to get this far and I don't want to break it up and start a new way. There must be a way of doing this.

If I was using a decent coding language, I would loop through all the ProductID numbers the user had entered, grab the ProductID number (which would also be the actual record number the way I do things) pull in the appropiate record from the Products Table and have access to all the fields for the Product in question. But I'm not using a decent coding language, I'm using VBA.

 

[Maquis]

Well, I guess I can't really give you good advice without actually seeing your tables and queries that the report is based on. I just don't understand why those queries can't include the product name since there is a valid relationship between the quotations table and the products table.

Perhaps you can change your combo boxes to store the Product id rather than the code, since you can't use the code to lookup the names properly. (The boxes can still display the code, just hold the id behind the scenes.)

Sorry, I couldn't help more. I hope you get it working. Try just walking away from it for the weekend and start again fresh Monday morning. You'd be surprised how often that gets your mind moving in the right direction. Maq
<insert witty signature here>

 

[zakrocz]

Thanks for the support Maquis.

I think the real problem lies in the fact that I've only been using Access for a few weeks. I'm designing a database for a new company that a friend of mine works for.

Not having a good foundation in using Access and using VBA is causing me problems. I'm sure it's a very good database tool when you get to grips with it.

Well at least you gave me a chance to vent my frustration.

I'll take your advice and give it a rest for a while

 

[zakrocz]

I found a solution.

I create a dummy record in a table only used for this purpose, I added this table to my original query, this then allows me to use the fields from the dummy record in my final report. When the user prints the quote I store all the unbound Product Names in the dummy record prior to the code that calls the report. I also store the current record number in the dummy record so that the query brings in the data from the right dummy record in case of multiuser issues.

Once the report has run I then delete the dummy record.

I'm sure there are easier ways of achieving this, but the beauty of development is there are many solutions to a problem, you don't have to follow a path laid in stone, you can create your own methods.

Hopefully this post will help future readers having problems displaying unbound controls on reports.