displaying the details until the last one

[Tech]

Hi there.

I have made a custom report (using the design view) I have set all the text boxes and labels.

Text boxes are in the details section and have been linked to a form, which includes a subform and some text boxes there...

it's a bit complicated but here goes:

This form, has a subform.

When you select something from a combo box, a new row appears, it's like those things like and order form, when u select something it automatically puts in a new row, incase if u wanted to order something else and u can fill the details there etc....

What I want is, in the report, how can I make it so that how ever many products this customer has ordered, i want that to appear in the report.

Is it possible? If so, how? I know for a fact that I linked the report text boxes to only the one text box in the subform, so it takes that value and prints it, but that's it.

I hope u follow, if not, please let me know.

thx

 

[directormac]

Don't pull your data from the form. Use the form to enter the data into your underlying tables, then have the report pull it from the tables.

 

[Tech]

um......how?

[edit] I know what u mean now, after re reading it, but there is a problem, not all the stuff is written to a table! and some stuff have been written to different tables [/edit]

 

[directormac]

Stuff written to different tables is not a problem. That's what Queries are good at. Read.

Stuff not written to any table is... what? Paramters for the report? What info is on your form that doesn't get stored anywhere?

 

[Tech]

DUH!!! Queries ..sorry...that may not work..but anyway:

things like Finishing don't get printed out or stored.....aaaaa..confused.

I just had an idea:

since the order details (or part of it) is linked with the customer details table, I can just make a report from that, fill in the rest of the blank fields and it should do it, but I NEED THE ANSWER TO A PROBLEM WITH VBA I AM HAVING!!!

(not shouting, just emphasising)

 

[Tech]

wait...that won't work damn

 

[Tech]

ok, can't get the query to work exactly

as soon as I put in the product details table in, it won't show anything at all, when i take it out, great, but it don't have the product item name..

 

[directormac]

What are "things like finishing" that don't get printed out and don't get stored?

It's very hard to help you with your VBA problem if you don't describe it.

As soon as you put the product details table in... what? The design of the query? How are you putting it in?

Upload the file.

 

[Tech]

ok, take a look

ftp://temp:temp@techftpserver.co.uk/mdffc.zip

the things I want printed in the report are (In the detail section):

Product Number
Product Name
Unit Price
Quantity Required
Total


I want it so that, whatever customer number (EG. 1234) it is at, it should print out all the orders they want, the products they wish to order, so this customer may have ordered 4 products, it should display them from product 1 they ordered, to product 4 and so on.

as for the VBA...there is a topic on the VBA section but heck to save time :

How do I make it so that, when the subform has done it's calculation and displayed it in the Amount due box (t1-t2), it should go to the main form and do the command161_click event?
or any other command button in the main form?

 

[directormac]

Having only look at your table structures, here's what I have for you so far.

GENERAL:
You have your primary key set as a text field on several of your tables. Why? Unless there is some REALLY compelling reason, always set your primary key to a field specifically for that purpose, typed as AutoNum. This is going to make everything else easier.

TABLE: Customer Details
Postal Code and Telephone Number - Access has built-in input masks & formats for making these neater. Use them.

TABLE: Customer Order History
Has nothing BUT a PK field. What's the point?

TABLE: Product Details
Well, yes, "finishing" DOES get stored after all, because there's a "finishing" field here on this table. "NumberinStock" doesn't belong on this table, since (1) it's a caluclated value and (2) it's about inventory, not about a product

TABLE: Products Purchased
Seems to replicate the info found in the OrderForm table. If this is a record of what each customer purchased, you can throw this table away because you're already storing that information in the OrderForm table.

Tech, you need to start over. First, turn OFF the computer. Sit down with some good old paper and pencil and make a list of all the different pieces of information you want to keep track of. Then organize that info into tables, selecting datatypes appropriately. THEN you can worry about forms and reports and queries and VBA and everything else.

--Finished Mac

 

[Tech]

hmm..thanks...

i can't afford to start over.....

 

[directormac]

Your choice, but I promise you it will take more time to correct all the things that are wrong than it will to do it right.

 

[Rich]

You need to look at some of the examples supplied with Access, Northwind and Orders are two excellent starting points

 

[Tech]

yeh, thanks.

aa lol. nearly done all of this and this happens.

firstly, can anyone plz tell me the command line on how to tell a subform to click a command button in the main form plz?

 

[Tech]

well, i got the query working.

but it shows twice the values! how come?

And how do I tell it to print the current record displayed on the screen? (like the first one except it didn't print all the ordering details)

The Query has these tables:

CustomerDetails
OrderForm
ProductDetails
ProductPurchased

these are the fields in the query:

Customer Number (from CustomerDetails)
Name

OrderNumber (from Orderform)
OrderTotal

Productnumber (from productDetails)
productname
unitprice

QTYPurchased (From productspurchased)

If I run it, it shows the records twice as much, so if I enter a name, it shows it 2 times, otherwise, everything else there is fine.

 

[Tech]

cool, got it to show only the current record but it's still displaying the records 2 times, why?

 

[Tech]

hmm...apart from this, it shows a all the product details! eg:

if I order a bookshelf and a chair, it shows this:

bookshelf
chair
dining table

bookshelf
chair
dining table


the dining table is the last entry in the product details table/form.

Any ideas on y it does this and how to over come the data being displayed twice?