G
Guest
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
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