Product Available and sold.

[gsrajan]

I have
TblStock with fields ProductCode (String) and ProdAvailable (Number).

I have another table
TblSales with ProductCode (String).

I wish to check the record count of ProductCode in TblSales whether it is equal or less than ProductAvailable in TblStock.

I wish to prevent the user from selling more than what is available.

Thanks for your help.

 

[plog]

I wish to prevent the user from selling more than what is available.

The table structure you have isn't the correct one for this. You shouldn't have a seperate table to tell you your inventory balance, you should derive that from a Transactions table.

As a rudimentary example, let's use this table:

StockTransactions
TransID, ID_Stock, TransDate, TransQuantity
1, 31, 1/31/2015, 15
2, 31, 2/1/2015, -7
3, 209, 2/1/2015, 18
4, 31, 2/5/2015, -4
5, 209, 2/5/2015, -6
6, 31, 2/6/2015, 10

From that data, at the end of 2/6/2015 Stock_ID=31 has 14 available (15-7-4+10) and Stock_ID=209 has 12 available (18-6). That information would be generated in a query:

SELECT Stock_ID, SUM(TransQuantity) AS StockAvailable FROM Transactions GROUP BY Stock_ID

Now, when using that data on a form, what you would do is use a drop down to control which stock a user could select. That drop down would be based on a query that limits the selections to just those with a StockAvailable value >0.

 

[jdraw]

Please post a jpg of your tables and relationships.

What part of this -exactly - is the issue? Is this an operational database?

You want to do a SELECT query.

 

[gsrajan]

Thank you for your replies

Table: TblStock
Fields: ProductCode, ProductAvaiable, ProductDescription

Table: TblSales
Fields: ProductCode, Customer, SaleDate

Form: SalesForm with Recordsource-TblSales with the ComboBox for ProuctCode
I wish to prevent the sales person to prevent sales if the ProductAvailable in tblStock is equal to the count of ProductCode in Tblsales

I tried the below code in the Combobox before update event, I get the error message " Data type mismatch"

Dim Dbs as Database
Dim rstTotal as Recordset
Dim rstUsed as Recordset
Dim strTotal as string
Dim strUsed as String
Set Dbs =CurrentDB()
strTotal="Select ProductAvailable from TblStock where ProductCode='" & forms!SalesForm!Combo115 & "'"
strUsed="Select ProductCode from TblSales where ProductCode='" & forms!SalesForm!Combo115 & "'"
Set rstTotal=Dbs.OpenRecordset(strTotal)
set rstUsed=Dbs.OpenRecordset(strUsed)
rstUsed.MoveFirst
rstUsed.MoveLast
If rstUsed.RecordCount=CInt(rstTotal) then
Msgbox ("You have used all")
Cancel=True
End if

Thanks for your help

 

[plog]

Thank you for your replies

Don't thank us for our replies, respond to them. I gave you the correct method (based on the limited information you initially provided) and jdraw asked for more information.

You spewed back a bunch of code without addressing either of our posts except for acknowledging we posted something.