Access: Automatic Updating Query

skippy11

Hi,

I have 3 tables in access, a customer table, a supplier table and an inventory table. I am trying to make a query that will automatically update the inventory table to say stock levels have increased or decreased whenever a purchase is made.

I already have an update query that does this but it only works if I run it myself, is it possible to have it so it automatically updates?

tiptap

skippy11 wrote: »

Hi,

I have 3 tables in access, a customer table, a supplier table and an inventory table. I am trying to make a query that will automatically update the inventory table to say stock levels have increased or decreased whenever a purchase is made.

I already have an update query that does this but it only works if I run it myself, is it possible to have it so it automatically updates?

Hi,

At a database level, MS Access does not have the capability of getting this to work, like a SQL trigger.
In the operation that sells/or buys an item, you can create a function or some VBA code to increase/decrease your stock in your database.

Hope that makes sense.

skippy11

Thank you for the quick reply,

I do have a book about VBA code so that would be no hassle, I was trying to avoid it if I could though because I hate coding.

Where would I enter this code, would it be in the SQL section of the update query?

tiptap

skippy11 wrote: »

Thank you for the quick reply,

I do have a book about VBA code so that would be no hassle, I was trying to avoid it if I could though because I hate coding.

Where would I enter this code, would it be in the SQL section of the update query?

Can you provide some more info on how the purchase is done and I should be able to help you ?

Is something been bought over a website and your access database been used as the database ? I doubt it but just a question.

Or is your access database just an export of customer and stock info from a "proper" database.

If you want this in real time then it's important to know what and how things are happening at the time of the transaction.

For instance, if someone buys 2 laptops, you need to update your inventory for that one particular product to be decreased by 2. So your update on your access database would be something like, in English,

update inventoryTBL set stocklevel = stocklevel - quantity purchased where productid = productid purchased. The actual code is very easy and only a few lines, it's just where to put it. Once I know more I could help.

Cheers

skippy11

tiptap wrote: »

Can you provide some more info on how the purchase is done and I should be able to help you ?

Is something been bought over a website and your access database been used as the database ? I doubt it but just a question.

Or is your access database just an export of customer and stock info from a "proper" database.

If you want this in real time then it's important to know what and how things are happening at the time of the transaction.

For instance, if someone buys 2 laptops, you need to update your inventory for that one particular product to be decreased by 2. So your update on your access database would be something like, in English,

update inventoryTBL set stocklevel = stocklevel - quantity purchased where productid = productid purchased. The actual code is very easy and only a few lines, it's just where to put it. Once I know more I could help.

Cheers

It's just a small database that Im looking into designing for my uncles small car partsbusiness, no website involved and I am only using access.

I have 5 tables, 2 tables are a list of customers, a list of suppliers, 2 more tables are for orders made and one for orders taken, and an inventory list, I have update querys that automatically update the inventory table and I havent started designing forms...yet.

I am going to use a form which will allow me to make a sale or a purchase which will either decrease or increase stock levels of a particular product.

I need to know where I could enter the VBA code for this.

tiptap

skippy11 wrote: »

It's just a small database that Im looking into designing for my uncles small car partsbusiness, no website involved and I am only using access.

I have 5 tables, 2 tables are a list of customers, a list of suppliers, 2 more tables are for orders made and one for orders taken, and an inventory list, I have update querys that automatically update the inventory table and I havent started designing forms...yet.

I am going to use a form which will allow me to make a sale or a purchase which will either decrease or increase stock levels of a particular product.

I need to know where I could enter the VBA code for this.

I gotcha.

So on your form, I expect you'll have a button of some sort which will finalise the transaction. In design view of the form, if you double click this button it will bring you into the OnClick event of the button. Here is where you'll need to enter in your vba code to increase/decrease the stock.

you can do something like docmd.runsql("SQL Query goes in here")

Hope that helps

SerialComplaint

skippy11 wrote: »

It's just a small database that Im looking into designing for my uncles small car partsbusiness, no website involved and I am only using access.

I have 5 tables, 2 tables are a list of customers, a list of suppliers, 2 more tables are for orders made and one for orders taken, and an inventory list, I have update querys that automatically update the inventory table and I havent started designing forms...yet.

I wonder if it is really a great idea to build a stock control system from scratch via Access. You might want to check out the minimal costs involved in systems like Sage REtail (http://www.sage.ie/retail/default.aspx) to look after the customers, the suppliers, the invoices and a whole lot more.

Would your Uncle try and make a spark plug by grinding down the metal from scratch?