video items order question

[samy]

Hi

First let me tell you about my system, these are some of my tables


tblMovie
MovieID,MovieTitle,MovieDate,MovieDescription,MovieActors, CategoryID,MovieLength,MovieStatus,RatingID,MovieRentCount ,MoviePoster

tblItem
ItemID,MovieID,FormatID,ItemCondition

tblSuppliers
SupplierID,SupplierName,SupplierCity,SupplierAddress SupplierPhone,SupplierMobile

tblPurchase
PurchaseID,SupplierID,PurchaseDate,PurchaseTime PurchaseCost

tblPurchaseDetails
PurchaseID,ItemID,Quantity,CostPerItem


my question is how can i make an order and then update the item table with the new copies that i ordered

 

[ScottGem]

Your table structure needs work.

tblMovie
MovieID, MovieTitle, MovieDate, MovieDescription, CategoryID, MovieLength, MovieStatus, RatingID, MoviePoster, MovieRentCount (don't know what that is)

tblItem
ItemID, MovieID, FormatID, ItemCondition, PurchaseID

tblSuppliers
SupplierID, SupplierName, SupplierCity, SupplierAddress, SupplierPhone, SupplierMobile

tblPurchase
PurchaseID, SupplierID, PurchaseDate

tblPurchaseDetails
PurchaseDetailsID, PurchaseID, MovieID, Quantity, CostPerItem

Your Item table lists each physical tape (or disk). Therefore you don't purchase items you purchase movies. So you don't link Purchases to Items, you link Items to Purchases.

With this structure you would run code once a Purchase has been received, that would loop thru the Purchase Details for the Purchase and a record to Items for each movie purchased.

 

[samy]

Ok Scott I fixed the tables as you suggest
Now what about

“the code that would loop thru the Purchase Details for the Purchase and a record to Items”

Can you direct me to the road just something to start with

Thanks

 

[ScottGem]

lookup Looping and Recordsets in Access HELP, you will find some examples.

 

[samy]

Scott and every one here i tried so bad to get this right but i couldnt
if any one can but a sample or small code to start with that will be super

 

[ScottGem]

Why not show us what you got. The VBA Help should have given you sample code.