Takeaway from a field

[liam_898]

Hey everyone,



Upon the completion of a sale, I would like the the 'Quantity' bought to be deducted from the stock level field. This done in a form.

I have no idea where to start.. been searching on google and couldn't find anything of use.

I can provide more information if needed of table structure... but basically the sale fields consist of:

• Sale ID
• Customer ID
• Product ID
• Quantity
• Sale Price
• Date

And the stock level is stored in the product table

any help or suggestion would be appreciated

[Bob Fitz]

IMHO I don't think you should be keeping a level of stock because this is a calculation. Take a look at this link for a better way: http://allenbrowne.com/AppInventory.html

[liam_898]

IMHO I don't think you should be keeping a level of stock because this is a calculation. Take a look at this link for a better way: http://allenbrowne.com/AppInventory.html

Ah, that makes perfect sense! I will give it a go and get back to you and let you know how I got on (I'm not that confident with vba)

Many thanks bob

[ssanfu]

FYI, "DATE" is a reserved word in Access and shouldn't be used as object names. In addition, "DATE" is not very descriptive. "Date" of what??? Birthday? "Date" Customer joined?? When product entered into table?

If you used a naming convention you wouldn't run into this problem. Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html
http://office.microsoft.com/en-us/ac...010030643.aspx

Also, using spaces in names causes problems in coding, queries reports..... See also: http://access.mvps.org/access/tencommandments.htm

[liam_898]

FYI, "DATE" is a reserved word in Access and shouldn't be used as object names. In addition, "DATE" is not very descriptive. "Date" of what??? Birthday? "Date" Customer joined?? When product entered into table?

If you used a naming convention you wouldn't run into this problem. Here is a list of reserved words:
http://allenbrowne.com/AppIssueBadWord.html
http://office.microsoft.com/en-us/ac...010030643.aspx

Also, using spaces in names causes problems in coding, queries reports..... See also: http://access.mvps.org/access/tencommandments.htm

Okay, I didn't realise that about the word 'DATE'. But I've been following the well accepted naming convention by Leszynski & Reddick (1994) and all my fields and tables are fully normalised (with underscores) lol. What I stated above was just an example as I didn't really want to be too specific.

In relation to the question, I decided not to commit to restructuring the tables and relationships as a lot of work has already gone into VBA, forms, queries etc and I would have to edit most for my DB. But next time I'll definitely follow the example provided.

My solution was to use a simple update query to deduct the quantity bought from from the stock level. I know its not the most efficient means but it works