Form from 7.0 to Access 10.0

[phillip9]

Hello,

I have VBA code on a form that works fine in Access 7.0, but when I import
the form and databases to Access 10.0 gives me the following error:

Compile Error: method or data member not found

rs.FindFirst "index = " & CInt(txtIndex)

This error happens anywhere I use the method .findfirst

Can anyone help me with this problem (full code listed below)?

thank you

--------------
Private Sub txtIndex_Change()
On Error GoTo errorhandler
Dim rs As RECORDSET
Dim rs2 As RECORDSET
Dim rs3 As RECORDSET
Dim strSQL As String
Dim strSQL2 As String
Dim intExcessColumn As Integer
Dim intNetColumn As Integer
Dim dblExcessIncomeIndex As Double
Dim dblNetIncomeIndex As Double



strSQL = "SELECT * FROM CorrespondenceData ORDER BY projnum"
strSQL2 = "SELECT * FROM proi ORDER BY projnum"

Set rs = CurrentDb().openrecordset(strSQL, dbopensnapshot)
Set rs2 = CurrentDb().openrecordset(strSQL2, dbopensnapshot)

txtnote.Value = ""

If txtIndex = "" Or txtIndex = " " Or IsNull(txtIndex) Then
' in case txtIndex becomes null or void, re-set it to 1 to prevent
any errors
txtIndex = 1
End If
rs.findfirst "index = " & CInt(txtIndex)

rs2.findfirst "projnum = '" & rs!PROJNUM & "'"

txtCORNUM.Value = ""
txtProjnum.Value = ""
txtProjname.Value = ""
txtServicer.Value = ""
txtSubject.Value = ""
txtCorFrom.Value = ""
txtCorRecDate.Value = ""
txtCorDueDate.Value = ""
txtReplySent.Value = ""
txtEnteredBy.Value = ""
txtEnteredOn.Value = ""
txtNetIncomeDate = ""
txtExcessIncomeDate = ""
chkExcessIncome = 0
chkNetIncome = 0

If rs.nomatch Then

txtnote.Value = "Index is not found"

Else
txtCORNUM.Value = rs!cornum
If rs2.nomatch Then

' enter data from database
txtProjnum.Value = rs!PROJNUM
txtServicer.Value = rs!SERVICER
txtSubject.Value = rs!CORSUB
txtCorFrom.Value = rs!CORFROM
txtCorRecDate.Value = rs!CORRECD
txtCorDueDate.Value = rs!CORDUED
txtReplySent.Value = rs!CORREPLY
txtEnteredBy.Value = rs!enteredby
txtEnteredOn.Value = rs!enteredon
txtnote.Value = "Project number is not in PROI Table"
chkExcessIncome = rs!ExcessIncome
chkNetIncome = rs!netincome
If IsNull(rs!ExcessIncomeIndex) Then
Else
dblExcessIncomeIndex = rs!ExcessIncomeIndex
End If
If IsNull(rs!netincomeindex) Then
Else
dblNetIncomeIndex = rs!netincomeindex
End If
If IsNull(rs!ExcessColumn) Then
Else
intExcessColumn = rs!ExcessColumn
End If
If IsNull(rs!NetColumn) Then
Else
intNetColumn = rs!NetColumn
End If

Else
' blank out the fields, clear data

' enter data from database
txtProjnum.Value = rs!PROJNUM
txtProjname.Value = rs2!PROJNAME
txtServicer.Value = rs2!SERVICER
txtSubject.Value = rs!CORSUB
txtCorFrom.Value = rs!CORFROM
txtCorRecDate.Value = rs!CORRECD
txtCorDueDate.Value = rs!CORDUED
txtReplySent.Value = rs!CORREPLY
txtEnteredBy.Value = rs!enteredby
txtEnteredOn.Value = rs!enteredon
chkExcessIncome = rs!ExcessIncome
chkNetIncome = rs!netincome

If IsNull(rs!ExcessIncomeIndex) Then
Else
dblExcessIncomeIndex = rs!ExcessIncomeIndex
End If
If IsNull(rs!netincomeindex) Then
Else
dblNetIncomeIndex = rs!netincomeindex
End If
If IsNull(rs!ExcessColumn) Then
Else
intExcessColumn = rs!ExcessColumn
End If
If IsNull(rs!NetColumn) Then
Else
intNetColumn = rs!NetColumn
End If

End If
End If






If chkExcessIncome Then
Set rs3 = CurrentDb().openrecordset("excessincome", dbopensnapshot)
rs3.findfirst "index = " & dblExcessIncomeIndex

Select Case intExcessColumn
Case 1
txtExcessIncomeDate = rs3!rpjanuary
Case 2
txtExcessIncomeDate = rs3!rpfebruary
Case 3
txtExcessIncomeDate = rs3!rpmarch
Case 4
txtExcessIncomeDate = rs3!rpapril
Case 5
txtExcessIncomeDate = rs3!rpmay
Case 6
txtExcessIncomeDate = rs3!rpjune
Case 7
txtExcessIncomeDate = rs3!rpjuly
Case 8
txtExcessIncomeDate = rs3!rpaugust
Case 9
txtExcessIncomeDate = rs3!rpseptember
Case 10
txtExcessIncomeDate = rs3!rpoctober
Case 11
txtExcessIncomeDate = rs3!rpnovember
Case 12
txtExcessIncomeDate = rs3!rpdecember

End Select
rs3.Close
End If


If chkNetIncome Then
Set rs3 = CurrentDb().openrecordset("netincome", dbopensnapshot)
rs3.findfirst "index = " & dblNetIncomeIndex

Select Case intNetColumn
Case 1
txtNetIncomeDate = rs3!rpjanuary
Case 2
txtNetIncomeDate = rs3!rpfebruary
Case 3
txtNetIncomeDate = rs3!rpmarch
Case 4
txtNetIncomeDate = rs3!rpapril
Case 5
txtNetIncomeDate = rs3!rpmay
Case 6
txtNetIncomeDate = rs3!rpjune
Case 7
txtNetIncomeDate = rs3!rpjuly
Case 8
txtNetIncomeDate = rs3!rpaugust
Case 9
txtNetIncomeDate = rs3!rpseptember
Case 10
txtNetIncomeDate = rs3!rpoctober
Case 11
txtNetIncomeDate = rs3!rpnovember
Case 12
txtNetIncomeDate = rs3!rpdecember

End Select
rs3.Close
End If




intcorrespondenceindex = CInt(txtIndex.Value)


rs.Close
rs2.Close
txtIndex.SetFocus
Exit Sub
errorhandler:
'error handler
MsgBox "error in frmCorrespondenceDAta-MULTI ( INDEX Lost Focus )"
Resume errorhandlerdone

errorhandlerdone:
Exit Sub

End Sub

 

[Ken Snell [MVP]]

Access 10.0 (Access 2002) does not have a reference to DAO library set by
default. It has a reference set to ADO library, and ADO recordsets do not
have a FindFirst method; they have a Find method only.

So, open References (in VBE, Tools | References) and select the DAO 3.6
library reference. If you don't plan to use ADO, then unselect that library.
If you want to keep the ADO library reference, move DAO higher in priority
than ADO.

Also, we usually recommend that you "disambiguate" Dim statements that are
using Types that are found in both ADO and DAO (such as Recordset,
Parameter, etc.):

Dim rs As DAO.Recordset

That will fix your problem!

 

[phillip9]

Thank you very much, that fixed my problem.

Also, thanks for the extra information about moving the DAO higher in
priority, that was some extra help that headed off some other problems.

Was there something I was missing, or would a person just need to know this?
The error message did not seem to provide me with enough information to
figure this out on my own.

Also, Could you or anyone recommend a good book?


thank you,

phill

 

[Ken Snell [MVP]]

No, you didn't miss something easy. This is one of the most common questions
posted to the newsgroup (as a Google search will confirm). The error message
is what it is...no more descriptive than what you saw. You usually only get
burned once before you learn!
< g >

Good books for 2002? Access 2002 Developers Handbook; Running Microsoft
Access 2002; Access 2002 Bible; Beginning Access 2002 VBA; Access 2002 VBA
Handbook. And there are others.