Shop Database - Finding a Product Via Serial Number and Marking as Sold

[tomself1]

Hey,

I have a bit of a query regarding a database for a shop.

Basically, I have 3 tables: one for customers, one for stock, and one for all the transactions. Each product on the stock table has its own unique serial number. There is also a tickbox field on the stock table, which indicated whether it has been sold or not.

I also have a new transaction form, where new transactions are written down. The user selects the product and customer from the list.

Is it possible, so that when a new transaction is saved, the database searches the stock sheet for that particular serial number, and ticks the sold box for that record?

I'm looking for any solution, preferably with as little code as possible, although I don't think that's possible, really!

Cheers for any help you can give!

Tom.

 

[NigelShaw]

hi,

it is possible but i have a question or 2?

Is it possible, so that when a new transaction is saved, the database searches the stock sheet for that particular serial number, and ticks the sold box for that record?

why have a tick box in the table? have a quantity instead unless of course you only ever have ONE product for sale at any one time

you can do a query based on the serial number to only show info relating to the serial number. from there, you can deduct the quantity by the amount of products you sell and save the back to the table. you can also advance the query to show only items with more than or less than 1 item. if you made the query to show only items with 0 quantity, you would only get the result if you had no stock.

regs

nigel

 

[JPearson]

You could call an update query for that serial number and to update that particular field. There might be a little code that would call that query and set the variable for the serial number.
Atleast point you in the right direction. Might be able to find and toss some code your way in a few.

 

[tomself1]

It's a piano shop, so normally only one piano is sold at a time. And in the stock table, I've put each piano as a separate record, as each one has a unique serial number.

Ah cheers, I'll have a go at an update query...

Tom.