Function FindFirst

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I use to have a program running under Access 98, and since we moved to
Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or Me![CC4] <> "")
Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
 
Try changing this:

Dim tblLetter As Variant

to this:

Dim tblLetter as DAO.Recordset

Better would be the following, because naming an object so that its type is
readily identifiable makes your code easier to understand:

Dim dbsLetter As Database
Dim rsLetter As DAO.Recordset 'changed
Set dbsLetter = CurrentDb
Set rsLetter = dbsLetter.OpenRecordset("Letter_3_Table") 'changed
 
Also, you need to add the following code before your "dbsLetter.close"
statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing
 
Hi,
Thanks for responding my question.
I did try to use this code:
It give me the following error code:
Run time error 3251,
Operation is not supported for this type of object.

Any idea why???

Cheryl Fischer said:
Also, you need to add the following code before your "dbsLetter.close"
statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing


--

Cheryl Fischer, MVP Microsoft Access



Paul said:
I use to have a program running under Access 98, and since we moved to
Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or Me![CC4] <> "")
Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...
 
Paul said:
Hi,
Thanks for responding my question.
I did try to use this code:
It give me the following error code:
Run time error 3251,
Operation is not supported for this type of object.

Any idea why???

Cheryl Fischer said:
Also, you need to add the following code before your
"dbsLetter.close" statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing


Paul said:
I use to have a program running under Access 98, and since we moved
to Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or Me![CC4]
<> "") Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...
Click to expand...

In addition to the changes Cheryl recommended, change this:

to this:

Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table",
dbOpenDynaset)

Or better yet, if you're only opening the recordset to update a single
record, the only one with Issue_ID = Me!IssueID, revise the whole thing
like this:

'----- start of suggested revision -----
Private Sub Command30_Click()

Dim strSQL As String

If Me![ToCC] <> "" _
Or Me![CC2] <> "" _
Or Me![CC3] <> "" _
Or Me![CC4] <> "" _
Then

strSQL = _
"UPDATE Letter_3_Table SET " & _
"CCSend = '" & Me![ToCC] & "', " & _
"CC2Send = '" & Me![CC2] & "', " & _
"CC3Send = '" & Me![CC3] & "', " & _
"CC4Send = '" & Me![CC4] & "' " & _
"WHERE Issue_ID = " & Me![IssueID]

CurrentDb.Execute strSQL, dbFailOnError

End If

DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")

End Sub

'----- end of suggested revision -----
 
Paul,

Which line of code produced the failure/error message? Please post all of
the revised code in the sub for Command_30 and indicate which line of code
produced the error.

--

Cheryl Fischer, MVP Microsoft Access



Paul said:
Hi,
Thanks for responding my question.
I did try to use this code:
It give me the following error code:
Run time error 3251,
Operation is not supported for this type of object.

Any idea why???

Cheryl Fischer said:
Also, you need to add the following code before your "dbsLetter.close"
statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing


--

Cheryl Fischer, MVP Microsoft Access



Paul said:
I use to have a program running under Access 98, and since we moved to
Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or Me![CC4] <> "")
Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...
Click to expand...
 
I did a very small DB to explain:
1 table with field name FieldA
And the code that give error 3251.

Private Sub TestFindFirst()
Dim dbsTestFindFirst As Database
Dim RsTestFindFirst As DAO.Recordset
Set dbsTestFindFirst = CurrentDb
Set RsTestFindFirst = dbsTestFindFirst.OpenRecordset("TblTestFindFirst")

RsTestFindFirst.FindFirst (FieldA = "a") ' *********** Error 3251
***********

dbsTestFindFirst.Close
End Sub

Cheryl Fischer said:
Paul,

Which line of code produced the failure/error message? Please post all of
the revised code in the sub for Command_30 and indicate which line of code
produced the error.

--

Cheryl Fischer, MVP Microsoft Access



Paul said:
Hi,
Thanks for responding my question.
I did try to use this code:
It give me the following error code:
Run time error 3251,
Operation is not supported for this type of object.

Any idea why???

Cheryl Fischer said:
Also, you need to add the following code before your "dbsLetter.close"
statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing


--

Cheryl Fischer, MVP Microsoft Access



I use to have a program running under Access 98, and since we moved to
Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or Me![CC4]
Click to expand...
Click to expand...
"")
Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...
Click to expand...
 
That should be:

RsTestFindFirst.FindFirst "FieldA = 'a'"

Should you want to use a variable strSearch instead of the constant a, it
would be

RsTestFindFirst.FindFirst "FieldA = '" & strSearch & "'"

Exagerated for clarity:

RsTestFindFirst.FindFirst "FieldA = ' " & strSearch & " ' "

If there's a change strSearch might contain an apostrophe (O'Riley), use

RsTestFindFirst.FindFirst "FieldA = " & Chr$(34) & strSearch & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paul said:
I did a very small DB to explain:
1 table with field name FieldA
And the code that give error 3251.

Private Sub TestFindFirst()
Dim dbsTestFindFirst As Database
Dim RsTestFindFirst As DAO.Recordset
Set dbsTestFindFirst = CurrentDb
Set RsTestFindFirst = dbsTestFindFirst.OpenRecordset("TblTestFindFirst")

RsTestFindFirst.FindFirst (FieldA = "a") ' *********** Error 3251
***********

dbsTestFindFirst.Close
End Sub

Cheryl Fischer said:
Paul,

Which line of code produced the failure/error message? Please post all of
the revised code in the sub for Command_30 and indicate which line of code
produced the error.
Click to expand...
moved
to
Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or
Click to expand...
Click to expand...
Me![CC4]
"")
Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your input,
In the original program I use a variable, that variable look for a value in
a form. I did this example with a constant to make it as simple as possible
to illustrate that the function FindFirst give a error message.
Paul
Douglas J. Steele said:
That should be:

RsTestFindFirst.FindFirst "FieldA = 'a'"

Should you want to use a variable strSearch instead of the constant a, it
would be

RsTestFindFirst.FindFirst "FieldA = '" & strSearch & "'"

Exagerated for clarity:

RsTestFindFirst.FindFirst "FieldA = ' " & strSearch & " ' "

If there's a change strSearch might contain an apostrophe (O'Riley), use

RsTestFindFirst.FindFirst "FieldA = " & Chr$(34) & strSearch & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paul said:
I did a very small DB to explain:
1 table with field name FieldA
And the code that give error 3251.

Private Sub TestFindFirst()
Dim dbsTestFindFirst As Database
Dim RsTestFindFirst As DAO.Recordset
Set dbsTestFindFirst = CurrentDb
Set RsTestFindFirst = dbsTestFindFirst.OpenRecordset("TblTestFindFirst")

RsTestFindFirst.FindFirst (FieldA = "a") ' *********** Error 3251
***********

dbsTestFindFirst.Close
End Sub

Cheryl Fischer said:
Paul,

Which line of code produced the failure/error message? Please post
Click to expand...
all
of
the revised code in the sub for Command_30 and indicate which line of code
produced the error.

--

Cheryl Fischer, MVP Microsoft Access



Hi,
Thanks for responding my question.
I did try to use this code:
It give me the following error code:
Run time error 3251,
Operation is not supported for this type of object.

Any idea why???

Also, you need to add the following code before your "dbsLetter.close"
statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing


--

Cheryl Fischer, MVP Microsoft Access



I use to have a program running under Access 98, and since we
Click to expand...
moved
to
Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or
Click to expand...
Me![CC4]
"")
Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dirk,
This is a quick fix, it work and solve my actual problem.Thank you very
much, but still I am wondering why the function findfirst do not work as it
was working with the previous version of access (98 vs 2000)

Best regards,
Paul

Dirk Goldgar said:
Paul said:
Hi,
Thanks for responding my question.
I did try to use this code:
It give me the following error code:
Run time error 3251,
Operation is not supported for this type of object.

Any idea why???

Cheryl Fischer said:
Also, you need to add the following code before your
"dbsLetter.close" statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing


I use to have a program running under Access 98, and since we moved
to Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or Me![CC4]
<> "") Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...

In addition to the changes Cheryl recommended, change this:

to this:

Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table",
dbOpenDynaset)

Or better yet, if you're only opening the recordset to update a single
record, the only one with Issue_ID = Me!IssueID, revise the whole thing
like this:

'----- start of suggested revision -----
Private Sub Command30_Click()

Dim strSQL As String

If Me![ToCC] <> "" _
Or Me![CC2] <> "" _
Or Me![CC3] <> "" _
Or Me![CC4] <> "" _
Then

strSQL = _
"UPDATE Letter_3_Table SET " & _
"CCSend = '" & Me![ToCC] & "', " & _
"CC2Send = '" & Me![CC2] & "', " & _
"CC3Send = '" & Me![CC3] & "', " & _
"CC4Send = '" & Me![CC4] & "' " & _
"WHERE Issue_ID = " & Me![IssueID]

CurrentDb.Execute strSQL, dbFailOnError

End If

DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")

End Sub

'----- end of suggested revision -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Paul said:
Thanks Dirk,
This is a quick fix, it work and solve my actual problem.Thank you
very much, but still I am wondering why the function findfirst do not
work as it was working with the previous version of access (98 vs
2000)
Click to expand...

I thought I answered that question by implication when I told you:
In addition to the changes Cheryl recommended, change this:


to this:

Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table",
dbOpenDynaset)
Click to expand...

From the looks of your code, your recordset is being opened on a local
table, which means that by default you get a table-type recordset. The
FindFirst, FindNext, FindPrevious, and FindLast methods are not
available on a table-type recordset. To use them when opening a
recordset on a local table, you must specify the dbOpenDynaset or
dbOpenSnapshot option to get the OpenRecordset method to return a
different type of recordset.

If this code worked in Access 97 exactly as quoted, then the table
"Letter_3_Table" must have been a linked table, not a local one.
 
You are right, many thanks,
Paul

Dirk Goldgar said:
I thought I answered that question by implication when I told you:


From the looks of your code, your recordset is being opened on a local
table, which means that by default you get a table-type recordset. The
FindFirst, FindNext, FindPrevious, and FindLast methods are not
available on a table-type recordset. To use them when opening a
recordset on a local table, you must specify the dbOpenDynaset or
dbOpenSnapshot option to get the OpenRecordset method to return a
different type of recordset.

If this code worked in Access 97 exactly as quoted, then the table
"Letter_3_Table" must have been a linked table, not a local one.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Simplifying is fine. However, what you had in your sample code was
incorrect.

You had

RsTestFindFirst.FindFirst (FieldA = "a")

whereas it needs to be

RsTestFindFirst.FindFirst "FieldA = 'a'"

(that's RsTestFindFirst.FindFirst "FieldA = ' a ' " )

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


Paul said:
Thanks for your input,
In the original program I use a variable, that variable look for a value in
a form. I did this example with a constant to make it as simple as possible
to illustrate that the function FindFirst give a error message.
Paul
Douglas J. Steele said:
That should be:

RsTestFindFirst.FindFirst "FieldA = 'a'"

Should you want to use a variable strSearch instead of the constant a, it
would be

RsTestFindFirst.FindFirst "FieldA = '" & strSearch & "'"

Exagerated for clarity:

RsTestFindFirst.FindFirst "FieldA = ' " & strSearch & " ' "

If there's a change strSearch might contain an apostrophe (O'Riley), use

RsTestFindFirst.FindFirst "FieldA = " & Chr$(34) & strSearch & Chr$(34)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Paul said:
I did a very small DB to explain:
1 table with field name FieldA
And the code that give error 3251.

Private Sub TestFindFirst()
Dim dbsTestFindFirst As Database
Dim RsTestFindFirst As DAO.Recordset
Set dbsTestFindFirst = CurrentDb
Set RsTestFindFirst = dbsTestFindFirst.OpenRecordset("TblTestFindFirst")

RsTestFindFirst.FindFirst (FieldA = "a") ' *********** Error 3251
***********

dbsTestFindFirst.Close
End Sub

Paul,

Which line of code produced the failure/error message? Please post all
of
the revised code in the sub for Command_30 and indicate which line
Click to expand...
of
code
produced the error.

--

Cheryl Fischer, MVP Microsoft Access



Hi,
Thanks for responding my question.
I did try to use this code:
It give me the following error code:
Run time error 3251,
Operation is not supported for this type of object.

Any idea why???

Also, you need to add the following code before your "dbsLetter.close"
statement to close the recordset.

rsLetter.Close
set rsLetter = Nothing


--

Cheryl Fischer, MVP Microsoft Access



I use to have a program running under Access 98, and since we moved
to
Access 2000 the following code do not work, I have the message:
Run time error 3251,
Operation is not support for this type of object.
There is the code:
Private Sub Command30_Click()

Dim dbsLetter As Database
Dim tblLetter As Variant
Set dbsLetter = CurrentDb
Set tblLetter = dbsLetter.OpenRecordset("Letter_3_Table")
If (Me![ToCC] <> "" Or Me![CC2] <> "" Or Me![CC3] <> "" Or Me![CC4]
<>
"")
Then
tblLetter.FindFirst ("Issue_ID = " & Me![IssueID])
tblLetter.Edit
tblLetter![CCSend].Value = Me![ToCC]
tblLetter![CC2Send].Value = Me![CC2]
tblLetter![CC3Send].Value = Me![CC3]
tblLetter![CC4Send].Value = Me![CC4]
tblLetter.Update
End If
dbsLetter.Close
DoCmd.RunMacro ("send email")
MsgBox ("File has been sent by email")
End Sub
Any help would be appreciate.
Paul
Click to expand...
Click to expand...
Click to expand...
 
Back
Top