Stock control system

[tom1252]

Bump!!!!!!!!!

 

[tom1252]

The code i used dosent work but not sure why?

I attached it

 

[pbaldy]

Define "doesn't work". Does it error, and if so what is the error and where does it occur? Does it run without error but produce an incorrect result?

 

[tom1252]

Is comes up with Compile Error: Invalid Outside Procedure

When i click on a form

 

[pbaldy]

I see an "End Function" line, but not the first line. Was that a typo or is it not there?

 

[tom1252]

Cheers i forgot to include that bit.

The follwoing statement comes up in red

"WHERE ((tblPurchaseOrder.ItemCode = " & Product & ")"

Not sure why?

 

[pbaldy]

Not sure which instance of it, but in this one:

        SQL = "SELECT Sum(tbltblPurchaseOrder.Quantity) AS QtyAcq " & _
            "FROM tblPurcahseOrder
	    "WHERE ((tblPurchaseOrder.ItemCode = " & Product & ")"

You never close off the string on the line before and you don't have a line continuation on the line before.

 

[tom1252]

Thanks i think that must have been a typing mistake. The bit of code below i dont know whether i am wrinting it right. What does SELECT TOP 1 mean?

SQL = "SELECT TOP 1 " & _
        "tblProduct.DateOfStockTake, " & _
        "tblProduct.Quantity" & _
        "FROM tblProduct" & _
        "WHERE ((ItemCode = " & Product & ")" & DateClause & _
        ") ORDER BY DateOfStockTake DESC;"

 

[pbaldy]

You may want to review this tutorial:

http://www.baldyweb.com/BuildSQL.htm

You're making the mistake discussed under "Spacing" right at the beginning. From Help, the TOP predicate "Returns a certain number of records that fall at the top or the bottom of a range specified by an ORDER BY clause". In your case, it should be getting the date and quantity of the last stock take.

 

[tom1252]

I have sorted out the spacing issue but there is a problem with setting the recordset as the SQL statement. The error is the following "Data type mismatch in criteria expression" I have gone throught the rest of the tutorial but cant see where i have gone wrong.

        SQL = "SELECT TOP 1 " & _
        "tblProduct.DateOfStockTake, " & _
        "tblProduct.Quantity " & _
        "FROM tblProduct " & _
        "WHERE ((ItemCode = " & Product & ")" & DateClause & _
        ") ORDER BY DateOfStockTake DESC; "
        

        Set rs = db.OpenRecordset(SQL)

 

[pbaldy]

Add a

Debug.Print SQL

after the string is built but before it runs, so you can examine the finished string in the Immediate window. If that doesn't point you to the error, post the SQL here along with the data types of any field in the criteria.

 

[tom1252]

No luck

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim Product As Long
    Dim AsOf As String
    Dim STDateLast As String
    Dim DateClause As String
    Dim SQL As String
    Dim QtyLast As Long
    Dim QtyAcq As Long
    Dim QtyUsed As Long

Data types

 SQL = "SELECT TOP 1 " & _
        "tblProduct.DateOfStockTake, " & _
        "tblProduct.Quantity " & _
        "FROM tblProduct " & _
        "WHERE ((ItemCode = " & Product & ")" & DateClause & _
        ") ORDER BY DateOfStockTake DESC; "
        

    
        
        Set rs = db.OpenRecordset(SQL)
        
        
        
        With rs
        
            If .RecordCount > 0 Then
                STDateLast = "#" & Format$(!DateOfStockTake, "dd\/mm\/yyyy") & "#"
                QtyLast = Nz(!Quantity, 0)
            End If
        End With
        
        rs.Close

 

[pbaldy]

What does "no luck" mean? What is the result of the Debug.Print SQL? I don't see how posting part of the code tells me what the data types of the two fields are. I need the data type of the fields in the table.

 

[tom1252]

Data mismatch come up in the window. Sorry didnt know thats what you meant.

Product - text

Item code - text

quantity - number

date - date/time

 

[pbaldy]

If ItemCode is text, it will need to be delimited, as discussed in that tutorial. You don't seem to want to use the Debug.Print, which is a vital tool when trying to create SQL in VBA.

 

[tom1252]

I am using it.

I place Debug.Print SQL after the sql statement. I then open the relevent form and the message comes up saying the error in this case data mismatch then i press debug.

 

[pbaldy]

If you're using it, you should be able to see the finished SQL and post it here. Can you post the db?

 

[tom1252]

Here is the db