Okay, I've screwed up somethng.....

K

Kim

Help! I thought my database was working great.... until I looked at the
tables. I have frmInvoices and frmInvoiceSubform. On the subform, I choose
the Product name and the description from my products table populates.
However, the description is not in my invoice details tables. What did i do
wrong? Thanks in advance

Kim
 
R

Rick Brandt

Kim said:
Help! I thought my database was working great.... until I looked at
the tables. I have frmInvoices and frmInvoiceSubform. On the subform,
I choose the Product name and the description from my products table
populates. However, the description is not in my invoice details
tables. What did i do wrong? Thanks in advance

Kim
Click to expand...

You actually did it correctly (but by accident). The ONLY field(s) from
your products table that should be copied to the invoice details table is
the primary key field and time-sensitive fields (like price). For any other
fields you want to see you should use lookup mechanisms to *display* them on
your forms and reports without copying that data redundantly all over the
place.

It sounds like you already have a mechanism in place to display the
description on the form without copying it. That is the correct way to do
it. Just delete that field from your invoice details table because you
don't need it.
 
D

Dirk Goldgar

Kim said:
Help! I thought my database was working great.... until I looked at
the tables. I have frmInvoices and frmInvoiceSubform. On the subform,
I choose the Product name and the description from my products table
populates. However, the description is not in my invoice details
tables. What did i do wrong? Thanks in advance
Click to expand...

Probably you've done nothing wrong. If your InvoiceDetails table stores
the product ID -- or a unique product name -- from the Products table,
there is no need for it to also store the product description, since
that information can always be retrieved from the Products table by a
query. In fact, it would be redundant for the same information to be
stored in both tables.
 
S

Sprinks

Kim,

The description can be displayed on your subform in one of two ways--either
the subform is based on a query that contains the Description field from the
Products table, or it uses the Column property of a combo or list box. In
either case, the control is not bound to the Description field of your
InvoiceItems table.

In *most* Invoice applications, you don't need the Description field because
this field is static--that is, a product is typically not "Bunch of Bananas"
this week and "Group of Grapes" the next. In this case, you would obtain the
value of the Description field from a query whenever you need it for printing
an invoice or other report. But not knowing the types of products you are
selling, you may have a motive to capture the Description *at the time of the
order*, so that it is specific to each particular order. More importantly,
you certainly have this motive for the unit price field, which will change
with time.

To handle these situations, include the Description and UnitPrice fields in
the RowSource of your combo or list box. Then set the ControlSource of two
textboxes to the Description and UnitPrice fields, respectively, of the
InvoiceItems table. Note that this may require you to change the query
underlying the subform. Then use the AfterUpdate event of the list or combo
box to set the values of the textboxes:

Me![txtDescription] = Me![MyComboOrListbox].Column(x)
Me![txtUnitPrice] = Me![MyComboOrListbox].Column(y)

where x and y are the column numbers from the combo box (starting from 0).

Hope that helps.
Sprinks
 
K

Kim

thanks for the quick responses! After all this time with Access, almost a
year, I may be actually getting it :)

thanks for the help
 
A

Arvin Meyer [MVP]

You did nothing wrong. You only need to describe the product once in your
database. Since you can always look at or reprint the invoice the only
details that you need to store in the order detail record are.

PrimaryKey
OrderID (FK)
ProductID (FK)
Qty
Price
 
Top