Keep getting format errors with this code... Need some Insight...

[Kumba1]

I'm trying to run the following code to determine if I have parts available on my Order Screen... so far when I try to do the SQL Portions I get a general message saying Syntax Error... But it's function is simple, run a query based on a text-string, and a qty, with some filtering parameters, and if there are no results, exit, if there are, display a message box that says parts are available...

Private Sub OP_AfterUpdate()
Dim y As String
Dim z As Integer
y = Me!StockNumber
z = Me!Qty
If IsNull(DoCmd.RunSQL "SELECT [tblPOParts].[StockNumber], [tblPOParts].[Stock], [tblPOParts].[Received], [tblPOParts].[BackOrder], [tblPOParts].Quantity FROM tblPOParts WHERE ((([tblPOParts].[StockNumber])="" & y & "&quot And (([tblPOParts].[Stock])=-1) And (([tblPOParts].[Received])=0) And (([tblPOParts].[BackOrder])=0) And (([tblPOParts].Quantity)>" & z & &quot);&quot Then
Exit Sub
Else
MsgBox ("Parts are Available&quot
End Sub

Sorry abou the messy SQL Parts, but not sure where i'm messing up...

 

[MichaelRed]

At first glance, I don't see the problem as being an "SQL" issue. I DO note that the "runSql" statement is attempting to ('illegally') refer to a SELECT query - a clear violation of MS (uncle Bob) NO-Nos.

A SIMPLISTIC variation would be to used the unnamed recordset and simply check that BOF and EOF are (BOTH) True (or not -as you desire).

A somewhat CRUDE DAO version:

Public Function basChkOnStk(StkNum As String, Qty As Integer)

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    Set dbs = CurrentDb
    
    strSQL = "SELECT [tblGrade_II].[StuName], " & _
             "[tblGrade_II].[Asgn01] " & _
             "FROM tblGrade_II " & _
             "WHERE  " & _
             "((([tblGrade_II].[Asgn01]) >= " & Qty & _
             "));"

    Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

    If ((rst.BOF = True) And (rst.EOF = True)) Then
        Exit Function
     Else
        MsgBox ("Parts are Available")
    End If

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over