Error 3001 recordset find sql

B

b99tw

I am new to VBA and am stumped by the error 3001 I'm getting for some data
values. I tried using the suggestions given for similar posts in this group,
but without success. Here is a summary of my problem: I'm using MSAccess
2007. From a form button click event, I am using the Find method to locate a
specific record in a table (cnaddres). The field name I want to search on is
Est Num; it is a text data type in the cnaddres table. I declared strSQL as
string variable and assigned it as strSQL = "[Est Num] = " & Me!EstNum (where
Me!EstNum is a current value from a form field). I then test for if EOF and
do stuff. Here's the strange part: The Find method works correctly when the
Est Num value starts with a number (e.g. 345p; 223; 45m6) However, I get the
3001 error message whenever the Est Num value starts with a non-digit
character (e.g. p99; F123).
At first I thought my problem had to do w/ SQL syntex for dealing with table
field names having spaces (e.g. Est Num), and I don't know SQL well. But I
don't think that's the problem since the Find method does work for some
values.
I've posted the code snippet below (please forgive my non-elegance...). I
would very very much appreciate any help on this. It is driving me a bit mad.
Thank you, Ellen

---------------
Private Sub bOpenBilling_Click()

'declare new connection object and instantiate new connection
Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

'declare new recordset and instantiate
Dim rsCNAddres As ADODB.Recordset
Set rsCNAddres = New ADODB.Recordset

Dim rsBillingActivity As ADODB.Recordset
Set rsBillingActivity = New ADODB.Recordset


'search for me.estNum and determine if found
Dim stDocName As String
Dim qry As String
Dim strSQL As String

Dim strEstNum As String

stDocName = "frmEnterBilling"
qry = "qrySortBillingActivityByCNDesc"

'search cnaddres for estnum
rsCNAddres.Open "cnaddres", cnCurrent, adOpenDynamic, adLockOptimistic,
adCmdTable 'point recordset to the CNAddres table
rsCNAddres.Filter = "" 'remove any filters

strSQL = "[Est Num] = " & Me!EstNum
rsCNAddres.Find strSQL 'THIS IS WHERE I GET THE 3001 ERROR FOR SOME VALUES

If rsCNAddres.EOF Then ' est num not found
'do stuff and exit Sub
Else 'est num found
'do stuff, insert new record into BillingActivity table, open Billing form

rsBillingActivity.Open "BillingActivity", cnCurrent, adOpenDynamic,
adLockOptimistic, adCmdTable 'point recordset to the BillingActivity table
rsBillingActivity.AddNew
rsBillingActivity!EstNum = strEstNum
rsBillingActivity.Update

DoCmd.OpenForm stDocName, WhereCondition:="[EstNum]=" & "'" & [EstNum]
& "'"

End If

Exit_bOpenBilling_Click:
Exit Sub

Err_bOpenBillinig_Click:
MsgBox Err.Description
Resume Exit_bOpenBilling_Click

rsCNAddres.Close 'close and destroy recordset
Set rsCNAddres = Nothing
rsBillingActivity.Close
Set rsBillingActivity = Nothing

End Sub
 
B

Brian

I think it is not space, but the quotes. Numeric values do not need to be
wrapped in single or double quotes, but literal values do, and perhaps the
leading digits are resulting in it being treated as a number instead of a
literal. As long as Me!EstNum will never have a single quote or apostraphe in
it to confuse matters, you should be able to do this:

strSQL = "[Est Num] = '" & Me!EstNum & "'"
MsgBox StrSQL 'do this here temporarily so you can see the SQL string before
you process it.

The MsgBox should show something like this: [Est Num] = 'p99'

You might even have to do this (try triple or quadruple sets of double
quotes if the single-quotes do not make it work quite right--I am just not in
a position to test it right now): strSQL = "[Est Num] = """" & Me!EstNum &
""""



b99tw said:
I am new to VBA and am stumped by the error 3001 I'm getting for some data
values. I tried using the suggestions given for similar posts in this group,
but without success. Here is a summary of my problem: I'm using MSAccess
2007. From a form button click event, I am using the Find method to locate a
specific record in a table (cnaddres). The field name I want to search on is
Est Num; it is a text data type in the cnaddres table. I declared strSQL as
string variable and assigned it as strSQL = "[Est Num] = " & Me!EstNum (where
Me!EstNum is a current value from a form field). I then test for if EOF and
do stuff. Here's the strange part: The Find method works correctly when the
Est Num value starts with a number (e.g. 345p; 223; 45m6) However, I get the
3001 error message whenever the Est Num value starts with a non-digit
character (e.g. p99; F123).
At first I thought my problem had to do w/ SQL syntex for dealing with table
field names having spaces (e.g. Est Num), and I don't know SQL well. But I
don't think that's the problem since the Find method does work for some
values.
I've posted the code snippet below (please forgive my non-elegance...). I
would very very much appreciate any help on this. It is driving me a bit mad.
Thank you, Ellen

---------------
Private Sub bOpenBilling_Click()

'declare new connection object and instantiate new connection
Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

'declare new recordset and instantiate
Dim rsCNAddres As ADODB.Recordset
Set rsCNAddres = New ADODB.Recordset

Dim rsBillingActivity As ADODB.Recordset
Set rsBillingActivity = New ADODB.Recordset


'search for me.estNum and determine if found
Dim stDocName As String
Dim qry As String
Dim strSQL As String

Dim strEstNum As String

stDocName = "frmEnterBilling"
qry = "qrySortBillingActivityByCNDesc"

'search cnaddres for estnum
rsCNAddres.Open "cnaddres", cnCurrent, adOpenDynamic, adLockOptimistic,
adCmdTable 'point recordset to the CNAddres table
rsCNAddres.Filter = "" 'remove any filters

strSQL = "[Est Num] = " & Me!EstNum
rsCNAddres.Find strSQL 'THIS IS WHERE I GET THE 3001 ERROR FOR SOME VALUES

If rsCNAddres.EOF Then ' est num not found
'do stuff and exit Sub
Else 'est num found
'do stuff, insert new record into BillingActivity table, open Billing form

rsBillingActivity.Open "BillingActivity", cnCurrent, adOpenDynamic,
adLockOptimistic, adCmdTable 'point recordset to the BillingActivity table
rsBillingActivity.AddNew
rsBillingActivity!EstNum = strEstNum
rsBillingActivity.Update

DoCmd.OpenForm stDocName, WhereCondition:="[EstNum]=" & "'" & [EstNum]
& "'"

End If

Exit_bOpenBilling_Click:
Exit Sub

Err_bOpenBillinig_Click:
MsgBox Err.Description
Resume Exit_bOpenBilling_Click

rsCNAddres.Close 'close and destroy recordset
Set rsCNAddres = Nothing
rsBillingActivity.Close
Set rsBillingActivity = Nothing

End Sub
Click to expand...
 
B

b99tw

Brian,
Your solution worked! It was those darn quotes. I can't thank you enough; I
really, really appreciate your help!
Sincerely, Ellen

Brian said:
I think it is not space, but the quotes. Numeric values do not need to be
wrapped in single or double quotes, but literal values do, and perhaps the
leading digits are resulting in it being treated as a number instead of a
literal. As long as Me!EstNum will never have a single quote or apostraphe in
it to confuse matters, you should be able to do this:

strSQL = "[Est Num] = '" & Me!EstNum & "'"
MsgBox StrSQL 'do this here temporarily so you can see the SQL string before
you process it.

The MsgBox should show something like this: [Est Num] = 'p99'

You might even have to do this (try triple or quadruple sets of double
quotes if the single-quotes do not make it work quite right--I am just not in
a position to test it right now): strSQL = "[Est Num] = """" & Me!EstNum &
""""



b99tw said:
I am new to VBA and am stumped by the error 3001 I'm getting for some data
values. I tried using the suggestions given for similar posts in this group,
but without success. Here is a summary of my problem: I'm using MSAccess
2007. From a form button click event, I am using the Find method to locate a
specific record in a table (cnaddres). The field name I want to search on is
Est Num; it is a text data type in the cnaddres table. I declared strSQL as
string variable and assigned it as strSQL = "[Est Num] = " & Me!EstNum (where
Me!EstNum is a current value from a form field). I then test for if EOF and
do stuff. Here's the strange part: The Find method works correctly when the
Est Num value starts with a number (e.g. 345p; 223; 45m6) However, I get the
3001 error message whenever the Est Num value starts with a non-digit
character (e.g. p99; F123).
At first I thought my problem had to do w/ SQL syntex for dealing with table
field names having spaces (e.g. Est Num), and I don't know SQL well. But I
don't think that's the problem since the Find method does work for some
values.
I've posted the code snippet below (please forgive my non-elegance...). I
would very very much appreciate any help on this. It is driving me a bit mad.
Thank you, Ellen

---------------
Private Sub bOpenBilling_Click()

'declare new connection object and instantiate new connection
Dim cnCurrent As ADODB.Connection
Set cnCurrent = CurrentProject.Connection

'declare new recordset and instantiate
Dim rsCNAddres As ADODB.Recordset
Set rsCNAddres = New ADODB.Recordset

Dim rsBillingActivity As ADODB.Recordset
Set rsBillingActivity = New ADODB.Recordset


'search for me.estNum and determine if found
Dim stDocName As String
Dim qry As String
Dim strSQL As String

Dim strEstNum As String

stDocName = "frmEnterBilling"
qry = "qrySortBillingActivityByCNDesc"

'search cnaddres for estnum
rsCNAddres.Open "cnaddres", cnCurrent, adOpenDynamic, adLockOptimistic,
adCmdTable 'point recordset to the CNAddres table
rsCNAddres.Filter = "" 'remove any filters

strSQL = "[Est Num] = " & Me!EstNum
rsCNAddres.Find strSQL 'THIS IS WHERE I GET THE 3001 ERROR FOR SOME VALUES

If rsCNAddres.EOF Then ' est num not found
'do stuff and exit Sub
Else 'est num found
'do stuff, insert new record into BillingActivity table, open Billing form

rsBillingActivity.Open "BillingActivity", cnCurrent, adOpenDynamic,
adLockOptimistic, adCmdTable 'point recordset to the BillingActivity table
rsBillingActivity.AddNew
rsBillingActivity!EstNum = strEstNum
rsBillingActivity.Update

DoCmd.OpenForm stDocName, WhereCondition:="[EstNum]=" & "'" & [EstNum]
& "'"

End If

Exit_bOpenBilling_Click:
Exit Sub

Err_bOpenBillinig_Click:
MsgBox Err.Description
Resume Exit_bOpenBilling_Click

rsCNAddres.Close 'close and destroy recordset
Set rsCNAddres = Nothing
rsBillingActivity.Close
Set rsBillingActivity = Nothing

End Sub
Click to expand...
Click to expand...
 

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

my recordset won't update 1
Help in Assigning value using recordset 2
ADO Recordset 1
Can't pass Recordset? 0
Type Mismatch Error: New to access programming 2
Dynamic SQL and ADO Recordset when table field names have spaces 3
Update table with VBA 3
SQL Syntax error 7

Top