Msgbox does not show qty from sum in query

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

Hi Can anyone advise me as to why the following code when
executed does not show the sum results - stkavailable? The
msgbox opens but only shows the "This item is in stock.
The stock in hand is ". Thank you in advance.

Private Sub MUI_Cat_Num_Click()
Dim MUI_Stk_Transact_temp As New ADODB.Recordset,
strSQLStmt As String

strSQLStmt = "SELECT Sum([PO_Qty_Received]-([PO_Qty]+
[PO_Qty_fromStk])) AS stkavailable FROM MUI_PO_List WHERE
MUI_PO_List.Cat_Num = " & [MUI_Cat_Num]

MUI_Stk_Transact_temp.Open strSQLStmt,
CurrentProject.Connection, adOpenForwardOnly,
adLockReadOnly


If Not MUI_Stk_Transact_temp.EOF Then
MsgBox "This item is in stock. The stock in hand is " &
MUI_Stk_Transact_temp!stkavailable, vbInformation +
vbOKOnly
Else
MsgBox "This item is not in stock.", vbInformation +
vbOKOnly

End If
MUI_Stk_Transact_temp.Close
Set MUI_Stk_Transact_temp = Nothing
Exit Sub

End Sub
 
Sam,

You need to put the record pointer back to the beginning
of the recordset. Put MUI_Stk_Transact_temp.MoveFirst
after your MUI_Stk_Transact_temp.Open str...... line. If
your query returns more than one line of results you are
only going to display the field result in the first
recordset.

JimB
 
IS Mui_Cat_Num a number field or a text field? If it is a text field you will
need to add apostrophes around the SQL Statement.


.... MUI_PO_List.Cat_Num = '" & [MUI_Cat_Num] & "'"
 
Back
Top