RecordSetWrapper multiple criteria not working - type mismatch

[saudkazia]

im trying to openrecordset using rsw - Northwind custom module that parses the select statement-
If rsw.OpenRecordset("Inventory Transactions", "[Customer Order ID] = " & OrderID And "[transaction type]" <> 1) Then

So this basically is saying to open Inventory Transactions table where customer order ID = OrderID and Transaction type is not 1.

Should I be using different syntax for this or did I mess up on a quotation or & somewhere.

Thanks,
Saud

[June7]

Needs to be an SQL statement that sets the rsw object variable, assuming you declared the rsw variable. Is it ADO or DAO recordset? Should post the full procedure.

For DAO:
Set rsw = CurrentDb.OpenRecordset("SELECT * FROM[ Inventory Transactions] WHERE [Customer Order ID] = " & Me.OrderID & " And [transaction type]<>1;")

For ADO:
rsw.Open "SELECT * FROM [Inventory Transactions] WHERE [Customer Order ID] = " & Me.OrderID & " And [transaction type]<>1;"

Now what do you want to do with the recordset?

Since you tried this within an If Then conditional, maybe you just need to use DLookup()?

[saudkazia]

June. I have a feeling you have not had much time with the Northwind database. RecordSetWrapper is a module. Let me paste it in for you

Option Compare DatabaseOption Explicit


Private m_rs As DAO.Recordset2




Public Function GetRecordsetClone(rs As DAO.Recordset2) As DAO.Recordset2
    If Not m_rs Is Nothing Then
        Debug.Assert False ' This is only designed to be used once
    Else
        Set m_rs = rs.Clone
        Set GetRecordsetClone = m_rs
    End If
End Function




Public Function OpenRecordset(Domain As String, _
                              Optional Criteria As String = "1=1", _
                              Optional OrderBy As String, _
                              Optional RecordsetType As DAO.RecordsetTypeEnum = dbOpenDynaset, _
                              Optional RecordsetOptions As DAO.RecordsetOptionEnum _
                              ) As Boolean
    
    
    If Not m_rs Is Nothing Then
        ' Close the recordset so it can be re-used
        CloseRecordset
    End If
    
    Dim strSQL As String
    strSQL = "SELECT * FROM [" & Domain & "] WHERE " & Criteria
    
    If OrderBy <> "" Then
        strSQL = strSQL & " ORDER BY " & OrderBy
    End If
    
    On Error GoTo ErrorHandler
    Set m_rs = CurrentDb.OpenRecordset(strSQL, RecordsetType, RecordsetOptions)
    OpenRecordset = True


Done:
    Exit Function
ErrorHandler:
    ' verify the private Recordset object was not set
    Debug.Assert m_rs Is Nothing
    
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.OpenRecordset", "strSQL = " & Chr(34) & strSQL & Chr(34)) Then Resume
End Function




Public Function Delete() As Boolean
    On Error GoTo ErrorHandler
    
    m_rs.Delete
    Delete = True
    
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.Delete") Then Resume
End Function




Public Function AddNew() As Boolean
    On Error GoTo ErrorHandler
    
    m_rs.AddNew
    AddNew = True
    
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.AddNew") Then Resume
End Function




Public Function Edit() As Boolean
    On Error GoTo ErrorHandler
    
    m_rs.Edit
    Edit = True
    
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.Edit") Then Resume
End Function




Public Function Update() As Boolean
    On Error GoTo ErrorHandler
    
    m_rs.Update
    Update = True
    
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.Update") Then Resume
End Function




Public Function MoveNext() As Boolean
    On Error GoTo ErrorHandler
    
    m_rs.MoveNext
    MoveNext = True
    
Done:
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.MoveNext") Then Resume
End Function




Public Function CloseRecordset() As Boolean
    On Error GoTo ErrorHandler
    
    m_rs.Close
    CloseRecordset = True
    
Done:
    Set m_rs = Nothing
    Exit Function
ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("RecordsetWrapper.CloseRecordset") Then Resume
End Function




Public Property Get Recordset() As DAO.Recordset2
    Set Recordset = m_rs
End Property




Private Sub Class_Terminate()
    If Not m_rs Is Nothing Then
        m_rs.Close
        Set m_rs = Nothing
    End If
End Sub

and the code that used this function

Function Delete(OrderID As Long) As Boolean
        Dim rsw As New RecordSetWrapper
        
            If rsw.OpenRecordset("order details", "[Order ID] = " & OrderID) Then
            With rsw.Recordset
                If Not .EOF Then
                    Do While Not .EOF
                       Delete = rsw.Delete
                        
                        .MoveNext
                    Loop
                End If
            End With
        End If
        If rsw.OpenRecordset("Inventory Transactions", "[Customer Order ID] = " & OrderID And "[transaction type]" <> 1) Then
            With rsw.Recordset
                If Not .EOF Then
                    Do While Not .EOF
                     Delete = rsw.Delete
                        
                        .MoveNext
                    Loop
                End If
            End With
        End If
        Set rsw = Nothing
        
    
          If rsw.OpenRecordset("Orders", "[Order ID] = " & OrderID) Then
            Delete = rsw.Delete
        End If
        End Function

Basically it works with single critera as in the 1st and 3rd ifs. But I can't seem to get it to work with the 2nd if with the multiple criteria

Also Please check my databse attached for the other issues i have posted. I have compacted and repaired it and made it under 2mb

Please let me know if there is anything I can do to make the database more efficient. Somehow it seems like im using hacks for what should be properly coded.

Appreciate your help.

[June7]

True, I am aware of Northwind (I think there are several variations) but never worked with.

Try:

If rsw.OpenRecordset("Inventory Transactions", "[Customer Order ID] = " & Me.OrderID & " And [transaction type] <> 1") Then

[saudkazia]

Thank you for your help this works - If rsw.OpenRecordset("Inventory Transactions", "[Customer Order ID] = " & OrderID & " And [transaction type]<>1")

it was just a matter of the placement of the & and "" which you illustrated in the DAO

[saudkazia]

i can't use me.X in a public function. is this normal. always says "invalid use of ME keyword"

[saudkazia]

may I know the logic behind the quotations and & as to when to use them.

[ssanfu]

i can't use me.X in a public function. is this normal. always says "invalid use of ME keyword"

"Me" is a short cut for "Forms!FormName" or "Reports!ReportName". Therefore, it will not work in a standard module - only in code behind forms/reports

If rsw.OpenRecordset("Inventory Transactions", "[Customer Order ID] = "

I would have also used brackets around "[Inventory Transactions]" because of the space in the name.