Continuous form help

[pjh85]

Hi,

I am pretty much begginer when it comes to databases and was wondering if anyone can help with a basic overview of how to create a continuous form in access 2007.

what i am trying to do is basically create a form with invoice information such as customer ID, invoice ID, total etc then use a continuous sub form to select products and quantity etc and store this in the invoice detail table.

I was hoping to use a combo box to select the product then have the remaining fields such as description and price update automatically. Then i could just enter the quantity and do a calculated field for the total.

The product information will come obviously from the product table but i then need to save this information in the invoice detail table.

I downloaded a sample database and have been trying to work it out from that but i'm not having much luck so far.
(link to the sample database: http://www.blueclaw-db.com/download/continuous_dependent_combo_box.htm)

the main form part is easy enough to do but I am struggling with the continuous form part, any help will be EXTREMELY appreciated.

Paul

 

[HiTechCoach]

Paul,

This template may help:

Customer Order Entry Template

 

[pjh85]

Hi again,

thanks for the reply that example helped alot more than the one i had. I have made the continuous sub form part now but i have one last problem before its fully working.

Basically i have created a calculated field which uses a formula like me.line_total = (me.unit_price*me.quantity) on an after update condition, only problem is whenever i add a new product it recalculates the total fine but it also updates all other records in the form with the same total.

any ideas??

 

[pjh85]

Hi,

I have fixed the previous isssue, i should have just done the calculation in the control source and not in vb didnt think that would be a problem but nevermind its fixed now.

One last question though, if i enter a product on the form and selct the quantity etc i want it to deduct that amount from the stock levels in the product table. Im kind of lost as to where to start with this one so whats the easiest way i could do this?

i think it will have to update when the record is saved to avoid input errors etc but thats as much as i can figure out

all help is appreciated

 

[HiTechCoach]

Ah ..... inventory control ... definitely possible to do ... but it is not easy.

See:
Inventory Control: Quantity on Hand

 

[gemma-the-husky]

stock control

hitech has it

you DON'T store a quantity in the products table at all - you calculate the quantitiy when you need it, based on all the transactions you have for that product. the transactions are stored in a transactions table.

note that you will have receipts and issues - the best treatment is to have a single table storing all the transactions, distinguishing between transaction types by a suitable flag or look up field.

if you are already storing a stock on hand qty, then you may need some database redesign. it might seem a pain, but it will be worthwhile in the long run.