rst.Find '3001' problem....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to delete records from a table using the rst.Delete and I can't
seem to get the rst.Find to work below is the code. and below that is the
error message i'm getting.

Public Sub codDeleteEmployee()

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection

Dim sqlString As String

Dim strCompanyName As String, strCustEmpFirstName As String,
strCustEmpLastName As String
'Dim strCustEmpPhone As Variant, strExtNum As Variant,
memOptionalPhoneNumbers As Variant
'Dim strCustEmpEmail As Variant, memCustEmpMemo As Variant, strEmpIDNum As
Variant

strCompanyName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCompanyName
strCustEmpFirstName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpFirstName
'strCustEmpLastName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpLastName
'strCustEmpPhone =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpPhone
'strExtNum =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strExtNum
'memOptionalPhoneNumbers =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memOptionalPhoneNumbers
'strCustEmpEmail =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpEmail
'memCustEmpMemo =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memCustEmpMemo

'0101 Code below deletes data
Set cnn = CurrentProject.Connection
rst.Open "tblCompaniesEmployees", cnn, adOpenStatic, adLockOptimistic,
adCmdTableDirect
sqlString = "[strCompanyName] = '" & strCompanyName & "' AND " &
"[strCustEmpFirstName] = '" & strCustEmpFirstName & "'" & ""
' The line below returns this ([strCompanyName] = 'Contech - Aub' AND
[strCustEmpFirstName] = 'Carlos') minus perenthasis
Debug.Print sqlString
rst.MoveFirst
rst.Find sqlString
MsgBox rst!strCompanyName
End Sub

I like to use msgboxs and Debug.Prints for debuging so I know what data im
using so that is why those lines probably don't make a lot of good sense to
you pro's.

The error message i get is Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
When I select Debug it goes to the rst.Find sqlString
Any reasons why or tips?
 
From Help file for ADO recordset's Find method:
"Only a single-column name may be specified in criteria. This method does
not support multi-column searches."

I assume that this is the reason for your error. Otherwise, does the table
tblCompaniesEmployees contain a field named "strCompanyName" and a field
named "strCustEmpFirstName"?

--

Ken Snell
<MS ACCESS MVP>


yooper_ssm said:
I'm trying to delete records from a table using the rst.Delete and I can't
seem to get the rst.Find to work below is the code. and below that is the
error message i'm getting.

Public Sub codDeleteEmployee()

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection

Dim sqlString As String

Dim strCompanyName As String, strCustEmpFirstName As String,
strCustEmpLastName As String
'Dim strCustEmpPhone As Variant, strExtNum As Variant,
memOptionalPhoneNumbers As Variant
'Dim strCustEmpEmail As Variant, memCustEmpMemo As Variant, strEmpIDNum As
Variant

strCompanyName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCompanyName
strCustEmpFirstName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpFirstName
'strCustEmpLastName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpLastName
'strCustEmpPhone =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpPhone
'strExtNum =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strExtNum
'memOptionalPhoneNumbers =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memOptionalPhoneNumbers
'strCustEmpEmail =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpEmail
'memCustEmpMemo =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memCustEmpMemo

'0101 Code below deletes data
Set cnn = CurrentProject.Connection
rst.Open "tblCompaniesEmployees", cnn, adOpenStatic, adLockOptimistic,
adCmdTableDirect
sqlString = "[strCompanyName] = '" & strCompanyName & "' AND " &
"[strCustEmpFirstName] = '" & strCustEmpFirstName & "'" & ""
' The line below returns this ([strCompanyName] = 'Contech - Aub' AND
[strCustEmpFirstName] = 'Carlos') minus perenthasis
Debug.Print sqlString
rst.MoveFirst
rst.Find sqlString
MsgBox rst!strCompanyName
End Sub

I like to use msgboxs and Debug.Prints for debuging so I know what data im
using so that is why those lines probably don't make a lot of good sense
to
you pro's.

The error message i get is Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
When I select Debug it goes to the rst.Find sqlString
Any reasons why or tips?
Click to expand...
 
Ken thanks for responding!

Yes and actually i really need to add a third. Ultimately I need to match
customer & First Name & Last Name

Some of the family owned business we deal with will have one company with
multiple employees with the same last names and if i were to just match the
first name then that could be bad also. Then here we have rampant nepotism
from multiple famlies.

so the fields I need to match up are as follows
strCompanyName
strCustEmpFirstName
strCustEmpLastName



Ken Snell said:
From Help file for ADO recordset's Find method:
"Only a single-column name may be specified in criteria. This method does
not support multi-column searches."

I assume that this is the reason for your error. Otherwise, does the table
tblCompaniesEmployees contain a field named "strCompanyName" and a field
named "strCustEmpFirstName"?

--

Ken Snell
<MS ACCESS MVP>


yooper_ssm said:
I'm trying to delete records from a table using the rst.Delete and I can't
seem to get the rst.Find to work below is the code. and below that is the
error message i'm getting.

Public Sub codDeleteEmployee()

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection

Dim sqlString As String

Dim strCompanyName As String, strCustEmpFirstName As String,
strCustEmpLastName As String
'Dim strCustEmpPhone As Variant, strExtNum As Variant,
memOptionalPhoneNumbers As Variant
'Dim strCustEmpEmail As Variant, memCustEmpMemo As Variant, strEmpIDNum As
Variant

strCompanyName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCompanyName
strCustEmpFirstName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpFirstName
'strCustEmpLastName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpLastName
'strCustEmpPhone =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpPhone
'strExtNum =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strExtNum
'memOptionalPhoneNumbers =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memOptionalPhoneNumbers
'strCustEmpEmail =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpEmail
'memCustEmpMemo =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memCustEmpMemo

'0101 Code below deletes data
Set cnn = CurrentProject.Connection
rst.Open "tblCompaniesEmployees", cnn, adOpenStatic, adLockOptimistic,
adCmdTableDirect
sqlString = "[strCompanyName] = '" & strCompanyName & "' AND " &
"[strCustEmpFirstName] = '" & strCustEmpFirstName & "'" & ""
' The line below returns this ([strCompanyName] = 'Contech - Aub' AND
[strCustEmpFirstName] = 'Carlos') minus perenthasis
Debug.Print sqlString
rst.MoveFirst
rst.Find sqlString
MsgBox rst!strCompanyName
End Sub

I like to use msgboxs and Debug.Prints for debuging so I know what data im
using so that is why those lines probably don't make a lot of good sense
to
you pro's.

The error message i get is Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
When I select Debug it goes to the rst.Find sqlString
Any reasons why or tips?
Click to expand...
Click to expand...
 
Do you need to use an ADO recordset? If you can use a DAO recordset, the
FindFirst method for it will let you do what you want.

--

Ken Snell
<MS ACCESS MVP>

yooper_ssm said:
Ken thanks for responding!

Yes and actually i really need to add a third. Ultimately I need to match
customer & First Name & Last Name

Some of the family owned business we deal with will have one company with
multiple employees with the same last names and if i were to just match
the
first name then that could be bad also. Then here we have rampant nepotism
from multiple famlies.

so the fields I need to match up are as follows
strCompanyName
strCustEmpFirstName
strCustEmpLastName



Ken Snell said:
From Help file for ADO recordset's Find method:
"Only a single-column name may be specified in criteria. This method does
not support multi-column searches."

I assume that this is the reason for your error. Otherwise, does the
table
tblCompaniesEmployees contain a field named "strCompanyName" and a field
named "strCustEmpFirstName"?

--

Ken Snell
<MS ACCESS MVP>


yooper_ssm said:
I'm trying to delete records from a table using the rst.Delete and I
can't
seem to get the rst.Find to work below is the code. and below that is
the
error message i'm getting.

Public Sub codDeleteEmployee()

Dim rst As New ADODB.Recordset
Dim cnn As New ADODB.Connection

Dim sqlString As String

Dim strCompanyName As String, strCustEmpFirstName As String,
strCustEmpLastName As String
'Dim strCustEmpPhone As Variant, strExtNum As Variant,
memOptionalPhoneNumbers As Variant
'Dim strCustEmpEmail As Variant, memCustEmpMemo As Variant, strEmpIDNum
As
Variant

strCompanyName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCompanyName
strCustEmpFirstName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpFirstName
'strCustEmpLastName =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpLastName
'strCustEmpPhone =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpPhone
'strExtNum =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strExtNum
'memOptionalPhoneNumbers =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memOptionalPhoneNumbers
'strCustEmpEmail =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!strCustEmpEmail
'memCustEmpMemo =
Forms!frmMainPhonListForm092203!frmCompanyEmployeePhoneCard110403.Form.Controls!memCustEmpMemo

'0101 Code below deletes data
Set cnn = CurrentProject.Connection
rst.Open "tblCompaniesEmployees", cnn, adOpenStatic,
adLockOptimistic,
adCmdTableDirect
sqlString = "[strCompanyName] = '" & strCompanyName & "' AND " &
"[strCustEmpFirstName] = '" & strCustEmpFirstName & "'" & ""
' The line below returns this ([strCompanyName] = 'Contech - Aub' AND
[strCustEmpFirstName] = 'Carlos') minus perenthasis
Debug.Print sqlString
rst.MoveFirst
rst.Find sqlString
MsgBox rst!strCompanyName
End Sub

I like to use msgboxs and Debug.Prints for debuging so I know what data
im
using so that is why those lines probably don't make a lot of good
sense
to
you pro's.

The error message i get is Run-time error '3001':
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.
When I select Debug it goes to the rst.Find sqlString
Any reasons why or tips?
Click to expand...
Click to expand...
Click to expand...
 
I guess maybe if i would have assigned a autonumber field to this table this
would have been a lot easier to match. I used the DAO recordset and with a
little bit of trial and error it works great. I also need to find out the
difference between ADO, DAO, and ADODB when to use what.

THANKS
Very Much
Mike S.
 
ADO and ADODB are the same type of recordset.

DAO is the object model that best fits the Jet database engine on which
ACCESS is based. Except when absolutely necessary (because of built-in
things by Microsoft), I always use DAO.

--

Ken Snell
<MS ACCESS MVP>
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Login Code to Open BRANCHES FORM 4
Public Sub to run Private Function 1
listbox on tab control to update mainform recordset using ADO 2
File in use error 5
Having Like 16
Copy and create recordset 2
LOGIN email passwords 4
Find Method and Forms 2

Back
Top