Modify product description on form

[pdtit]

Hi,

I'm developing a basic invoice system in Access 2003.

The invoice data is entered using a productcode from table products; automatically, product price and description is shown in the form (duh!!).

What I want to do now is give the user the possibility to modify the product description if needed for this particular invoice.
I also want him to be able to add "free text lines", ie. enter lines not based on the products' table.

How could I achieve this ?

Regards,

Peter

 

[GingerR]

So you must have a table with InvoiceID with unique InvoiceID's. Then another table with InvoiceID and ProductID, where there are multiple ProductID's for each InvoiceID, right?

In that second table, add a field called ProdDescLocal. Then you have a coupla options:

1) when a user chooses a product for an invoice, also add the product description from the product table to the InvoiceProducts table to that new field. Then display THIS field on the form,and the user can edit it. Drawback: the reason you have the prod description in a separate Products table in the first place is so that you do not have to enter a prod desc each time, and so that if you change the prod desc one time, it displays the current desc on all reports, queries, etc. If last week it was called "Red Shoes", that will be hard-coded into each field, so if this week it's supposed to read "Red Shoes with White Laces", the old ones will read the old way instead of the new way.

or

2) display the orig product desc on the form like you have it now, but locked and greyed out so it's not changeable. Also then display the new "ProdDescLocal" field so users can put in their own description. Then in queries, reports, etc, your logic would be something like "iif(ProdDescLocal is null,OrigProdDesc,ProdDescLocal)" so if the local prodDesc has something in it, that's what will be displayed for that invoice; otherwise the 'real' prod desc will be displayed. Drawbacks: none, really, except remembering to make the queries/reports work properly. You could even write a function that you call throughout your database which does the same thing.

either of these appeal to you?

As for the second question, I don't understand what you mean. You want to "add lines"? do you mean "add rows"? to what? the invoice? or to the Invoice+Product?

 

[pdtit]

Ginger,

Thanks for your fast reply !!

I went for option 2 and it works great. I never thought it was that easy.

About my second question :
What I meant was adding an entry to my invoice without refering to a product code.
By means of solution 2, I created a default product code "99999" where I can update the "alternate description" and "alternate price"

As such, every line can have a different description and price, even if they keep using the "999999" code.

Kind regards and many thanks,

Peter

 

[GingerR]

glad it worked out for you!

g