Results 1 to 6 of 6

Thread: functions

  1. #1
    Join Date
    Apr 2009
    Posts
    8

    Red face Unanswered: functions

    i am fairly new to access and have never used functions. i copied the following code from Allen Browne - Inventory Control: Quantity on Hand web site as i am interested in inventory control. i made the tables he said you needed and entered some data, however, he did not explain how to incorporate the code into the forms. is there someone who can explain how to do it. i am using access 2003. thanks

    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(tblAcqDetail.Quantity) AS QuantityAcq " & _
    "FROM tblAcq INNER JOIN tblAcqDetail ON tblAcq.AcqID = tblAcqDetail.AcqID " & _
    "WHERE ((tblAcqDetail.ProductID = " & 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.ProductID = " & 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




    Exit_Command0_Click:
    Exit Function

    Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click

    End Function

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For instance you can have a form with a button: Button_ComputeOnHand and a text box: Text_OnHand. Somewhere on the same form there is also another text box: Text_ProductID that is linked to a table or a query and that display the product ID of an item into the database.

    In the OnClick event code of the button you call the OnHand function and assign its return value to the text box Text_OnHand, like this:

    Code:
    Private Sub Button_ComputeOnHand_Click
        Me.Text_OnHand.Value = OnHand(Me.Text_ProductID.Value)
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Apr 2009
    Posts
    8
    sinndho, thanks for the reply. i put the code in a module and tried that. do not know if i put the function in the right place. would you mind taking a look. thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2009
    Posts
    8

    Talking sum of quantity

    hey, i was able to get a total by using a query to sum all of items in inventory, sum all the items on invoices, group them and minus them in a form. is this as good a way as using the function? thanks

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I've corrected some things into the code but the main problem is related to the data type you use for ProducID which is a text while the function expects to receive a number.

    Search into code for comments beginning with ' *** sinndho

    Have a nice day!
    Attached Files Attached Files

  6. #6
    Join Date
    Oct 2009
    Posts
    5

    This has to bwe th most frustrating thing i have ever had to do in Access i understand the button adn the command but when i try to runn it it fails on me Suggestions?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •