Access help! "Simon Glencross" <simon.glencross[ at ]sg.consultancy.co.uk> 04.09.2006 22:53:23 Ok, I have looked at this and customised it I think correctly, do I put the code under the click event of a command button? Sorry new to this and getting to know the ins and outs. Thanks "ruralguy via AccessMonster.com" <u12102[ at ]uwe> wrote in message news:65cb79e35d4ab[ at ]uwe... [Quoted Text] > MVP Allen Browne covers this pretty well. > http://allenbrowne.com/AppInventory.html > > Simon Glencross wrote: >>I have created a database which hold customer details, stock control and >>sales. >> >>I am having difficulty with the following. >> >>When I sell and Item I need this to be deducted from the total stock for >>the >>item which has been sold, what is the best way to do this? >> >>I have the following tables. >> >>tblinvoice >>Invoice ID(pk) >>CustomerID(fk) >>InvoiceDate >>InvoiceNotes >> >>Tblinvoicedetails >>InvoiceDetailID (pk) >>InvoiceID(fk) >>Quantity >>PriceEach >>ItemCode(fk) >> >>tblproducts >>Itemcode (pk) >>Description >>productgroup >>rrprice >>salesprice >> >>tblcurrentstock >>Itemcode (pk) >>Quantity >> >>Any help would be much appreciated. > > -- > HTH - RuralGuy (RG for short) acXP WinXP Pro > Please post back to this forum so all may benefit. > > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200609/1 >

RE: Access help! Wayne-I-M 05.09.2006 06:57:01 Hi Simon Not really sure which code you are referring to and I can't find the original posts you have included. In reply to [Quoted Text] >>When I sell and Item I need this to be deducted from the total stock for >>the >>item which has been sold, what is the best way to do this? Not knowing the layout of your D Base it is quite hard to answer but you could look at including the equations in the query that the form is based on and then use the AfterUpdate event to requery the query/form to ensure your screen data is always up to date. -- Wayne Manchester, England. Enjoy whatever it is you do "Simon Glencross" wrote: > Ok, > > I have looked at this and customised it I think correctly, do I put the code > under the click event of a command button? Sorry new to this and getting to > know the ins and outs. > > Thanks > > > "ruralguy via AccessMonster.com" <u12102[ at ]uwe> wrote in message > news:65cb79e35d4ab[ at ]uwe... > > MVP Allen Browne covers this pretty well. > > http://allenbrowne.com/AppInventory.html > > > > Simon Glencross wrote: > >>I have created a database which hold customer details, stock control and > >>sales. > >> > >>I am having difficulty with the following. > >> > >>When I sell and Item I need this to be deducted from the total stock for > >>the > >>item which has been sold, what is the best way to do this? > >> > >>I have the following tables. > >> > >>tblinvoice > >>Invoice ID(pk) > >>CustomerID(fk) > >>InvoiceDate > >>InvoiceNotes > >> > >>Tblinvoicedetails > >>InvoiceDetailID (pk) > >>InvoiceID(fk) > >>Quantity > >>PriceEach > >>ItemCode(fk) > >> > >>tblproducts > >>Itemcode (pk) > >>Description > >>productgroup > >>rrprice > >>salesprice > >> > >>tblcurrentstock > >>Itemcode (pk) > >>Quantity > >> > >>Any help would be much appreciated. > > > > -- > > HTH - RuralGuy (RG for short) acXP WinXP Pro > > Please post back to this forum so all may benefit. > > > > Message posted via AccessMonster.com > > http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200609/1 > > > > > >

Re: Access help! "Simon Glencross" <simon.glencross[ at ]sg.consultancy.co.uk> 05.09.2006 07:42:16 Here is the code.... Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As Long 'Purpose: Return the quantity-on-hand for a product. 'Arguments: vProductID = the product to report on. ' vAsOfDate = the date at which quantity is to be calculated. ' If missing, all transactions are included. 'Return: Quantity on hand. Zero on error. Dim db As DAO.Database 'CurrentDb() Dim rs As DAO.Recordset 'Various recordsets. Dim lngProduct As Long 'vProductID as a long. Dim strAsOf As String 'vAsOfDate as a string. Dim strSTDateLast As String 'Last Stock Take Date as a string. Dim strDateClause As String 'Date clause to use in SQL statement. Dim strSQL As String 'SQL statement. Dim lngQtyLast As Long 'Quantity at last stocktake. Dim lngQtyAcq As Long 'Quantity acquired since stocktake. Dim lngQtyUsed As Long 'Quantity used since stocktake. If Not IsNull(vProductID) Then 'Initialize: Validate and convert parameters. Set db = CurrentDb() lngProduct = vProductID If IsDate(vAsOfDate) Then strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#" End If 'Get the last stocktake date and quantity for this product. If Len(strAsOf) > 0 Then strDateClause = " AND (StockTakeDate <= " & strAsOf & ")" End If strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake " & _ "WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _ ") ORDER BY StockTakeDate DESC;" Set rs = db.OpenRecordset(strSQL) With rs If .RecordCount > 0 Then strSTDateLast = "#" & Format$(!StockTakeDate, "mm\/dd\/yyyy") & "#" lngQtyLast = Nz(!Quantity, 0) End If End With rs.Close 'Build the Date clause If Len(strSTDateLast) > 0 Then If Len(strAsOf) > 0 Then strDateClause = " Between " & strSTDateLast & " And " & strAsOf Else strDateClause = " >= " & strSTDateLast End If Else If Len(strAsOf) > 0 Then strDateClause = " <= " & strAsOf Else strDateClause = vbNullString End If End If 'Get the quantity acquired since then. strSQL = "SELECT Sum(currentstock.Quantity) AS QuantityAcq " & _ "FROM currentstock INNER JOIN products ON product.itemcode = currentstock.itemcode " & _ "WHERE ((currentstock.itemcode = " & lngProduct & ")" If Len(strDateClause) = 0 Then strSQL = strSQL & ");" Else strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause & "));" End If Set rs = db.OpenRecordset(strSQL) If rs.RecordCount > 0 Then lngQtyAcq = Nz(rs!QuantityAcq, 0) End If rs.Close 'Get the quantity used since then. strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " & _ "FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _ "tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _ "WHERE ((tblInvoiceDetail.itemcode = " & lngProduct & ")" If Len(strDateClause) = 0 Then strSQL = strSQL & ");" Else strSQL = strSQL & " AND (tblInvoice.InvoiceDate " & strDateClause & "));" End If Set rs = db.OpenRecordset(strSQL) If rs.RecordCount > 0 Then lngQtyUsed = Nz(rs!QuantityUsed, 0) End If rs.Close 'Assign the return value OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed End If Set rs = Nothing Set db = Nothing Exit Function End Function "Wayne-I-M" <WayneIM[ at ]discussions.microsoft.com> wrote in message news:786AE519-7CBD-42A6-B636-BDC4C035C800[ at ]microsoft.com... [Quoted Text] > Hi Simon > > Not really sure which code you are referring to and I can't find the > original posts you have included. > > In reply to >>>When I sell and Item I need this to be deducted from the total stock for >>>the >>>item which has been sold, what is the best way to do this? > Not knowing the layout of your D Base it is quite hard to answer but you > could look at including the equations in the query that the form is based > on > and then use the AfterUpdate event to requery the query/form to ensure > your > screen data is always up to date. > > > -- > Wayne > Manchester, England. > Enjoy whatever it is you do > > > "Simon Glencross" wrote: > >> Ok, >> >> I have looked at this and customised it I think correctly, do I put the >> code >> under the click event of a command button? Sorry new to this and getting >> to >> know the ins and outs. >> >> Thanks >> >> >> "ruralguy via AccessMonster.com" <u12102[ at ]uwe> wrote in message >> news:65cb79e35d4ab[ at ]uwe... >> > MVP Allen Browne covers this pretty well. >> > http://allenbrowne.com/AppInventory.html >> > >> > Simon Glencross wrote: >> >>I have created a database which hold customer details, stock control >> >>and >> >>sales. >> >> >> >>I am having difficulty with the following. >> >> >> >>When I sell and Item I need this to be deducted from the total stock >> >>for >> >>the >> >>item which has been sold, what is the best way to do this? >> >> >> >>I have the following tables. >> >> >> >>tblinvoice >> >>Invoice ID(pk) >> >>CustomerID(fk) >> >>InvoiceDate >> >>InvoiceNotes >> >> >> >>Tblinvoicedetails >> >>InvoiceDetailID (pk) >> >>InvoiceID(fk) >> >>Quantity >> >>PriceEach >> >>ItemCode(fk) >> >> >> >>tblproducts >> >>Itemcode (pk) >> >>Description >> >>productgroup >> >>rrprice >> >>salesprice >> >> >> >>tblcurrentstock >> >>Itemcode (pk) >> >>Quantity >> >> >> >>Any help would be much appreciated. >> > >> > -- >> > HTH - RuralGuy (RG for short) acXP WinXP Pro >> > Please post back to this forum so all may benefit. >> > >> > Message posted via AccessMonster.com >> > http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200609/1 >> > >> >> >> >>