Hello,

I'm trying to connect certain parameters in excel to Microsoft access database by a click of an activeX button. I'm getting the following error when trying to execute

run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

The issue is with my rs.filter. Anyone know of a better way to vba code multiple criteria?

My Code below:

Private Sub CommandButton2_Click()

Dim cn As ADODB.Connection, rs As ADODB.Recordset
Dim sVendor As String, sDocumentDate As String, sInvoice As String, sAmount As String, sLocation As String, sProject As String, sStore As String, sCipfas As String, sLine As String, sCode As String, sDc As String, sPeriod As String
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=C:\Users\rschell\Desktop\Database1.accdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "APDatabase", cn, adOpenKeyset, adLockOptimistic, adCmdTable

Range("J88").Activate ' row J88 contains column headings
Do While Not IsEmpty(ActiveCell)
sVendor = ActiveCell.Value
sDocumentDate = ActiveCell.Offset(0, 1).Value
sInvoice = ActiveCell.Offset(0, 2).Value
sAmount = ActiveCell.Offset(0, 3).Value
sLocation = ActiveCell.Offset(0, 4).Value
sProject = ActiveCell.Offset(0, 5).Value
sStore = ActiveCell.Offset(0, 6).Value
sVendor = ActiveCell.Offset(0, 7).Value
sCipfas = ActiveCell.Offset(0, 8).Value
sLine = ActiveCell.Offset(0, 9).Value
sCode = ActiveCell.Offset(0, 10).Value
sDc = ActiveCell.Offset(0, 11).Value
sPeriod = ActiveCell.Offset(0, 12).Value


rs.Filter = "Vendor='" & sVendor & "' AND Document Date='" & sDocumentDate & "' AND Invoice#='" & sInvoice & "' AND Amount='" & sAmount & "' AND Location='" & sLocation & "' AND Project#='" & sProject & "' AND Store#='" & sStore & "' AND Vendor#='" & sVendor & "' AND CIP/FAS='" & sCipfas & "' AND Line Item='" & sLine & "' AND Code='" & sCode & "' AND DC='" & sDc & "' AND Period='" & sPeriod & "'"
If rs.EOF Then
Debug.Print "No existing record - adding new..."
rs.Filter = ""
rs.AddNew
rs("Vendor").Value = sVendor
rs("Document Date").Value = sDocumentDate
rs("Invoice#").Value = sInvoice
rs("Amount").Value = cAmountAsVariant
rs("Location").Value = sLocation
rs("Project#").Value = sProject
rs("Store#").Value = sStore
rs("Vendor#").Value = sVendor
rs("CIP/FAS").Value = sCipfas
rs("Line").Value = sLine
rs("Code").Value = sCode
rs("DC").Value = sDc
rs("Period").Value = sPeriod
Else
Debug.Print "Existing record found..."
End If
rs("Period").Value = sPeriod
rs.Update
Debug.Print "...record update complete."

ActiveCell.Offset(1, 0).Activate ' next cell down
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub