winxmun
07-10-2017, 03:00 AM
Hi, I had converted my V2002 file to V2010. File successfully converted but some VBA doesnt work properly. The following VBA works perfectly in V2002 but it doesnt in v2010. I used the variables, ie 'applicants' and 'mortgr' in the Report. 'Applicants' and 'mortgr' was at Public in V2002, then hit error when generating the report (the field display as '#Name?'). After changing to Dim, no more error for the field, but prompted as parameter value box. Any help is much appreciated! =)
Option Compare Database
'Public applicants As String
'Public mortgr As String
Option Explicit
Private Sub Report_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim rstMR As DAO.Recordset
Dim sSQLM As String
Dim applicants As String
Dim mortgr As String
Set dbs = CurrentDb()
Set rstMR = dbs.OpenRecordset("MR", dbOpenDynaset)
sSQLM = "[Loan_No] Like '" & Forms!EnquiryList!Loan_No & "'"
rstMaster_Records.FindFirst sSQLM
If rstMR.NoMatch Then
MsgBox "Loan Number Not Found"
Exit Sub
Else
'------------To get the Borrower(s)string--------------
If Not IsNull(rstMR!Main_Applicant) Then
applicants = rstMR!Main_Applicant
End If
If Not IsNull(rstMR!Joint1_Name) Then
applicants = applicants & "/ " & rstMR!Joint1_Name
End If
End If
'--------------To get the Mortgagor(s) string------------
If (Nz(rstMR!Main_Applicant) = Nz(rstMR!Mortgr1_Name)) _
And (Nz(rstMR!Joint1_Name) = Nz(rstMR!Mortgr2_Name)) _
Then
mortgr = "SAME AS BORROWER(S)"
Else
If Not IsNull(rstMR!Mortgr1_Name) Then
mortgr = rstMR!Mortgr1_Name
End If
If Not IsNull(rstMR!Mortgr2_Name) Then
mortgr = mortgr & "/ " & rstMR!Mortgr2_Name
End If
End If
Option Compare Database
'Public applicants As String
'Public mortgr As String
Option Explicit
Private Sub Report_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim rstMR As DAO.Recordset
Dim sSQLM As String
Dim applicants As String
Dim mortgr As String
Set dbs = CurrentDb()
Set rstMR = dbs.OpenRecordset("MR", dbOpenDynaset)
sSQLM = "[Loan_No] Like '" & Forms!EnquiryList!Loan_No & "'"
rstMaster_Records.FindFirst sSQLM
If rstMR.NoMatch Then
MsgBox "Loan Number Not Found"
Exit Sub
Else
'------------To get the Borrower(s)string--------------
If Not IsNull(rstMR!Main_Applicant) Then
applicants = rstMR!Main_Applicant
End If
If Not IsNull(rstMR!Joint1_Name) Then
applicants = applicants & "/ " & rstMR!Joint1_Name
End If
End If
'--------------To get the Mortgagor(s) string------------
If (Nz(rstMR!Main_Applicant) = Nz(rstMR!Mortgr1_Name)) _
And (Nz(rstMR!Joint1_Name) = Nz(rstMR!Mortgr2_Name)) _
Then
mortgr = "SAME AS BORROWER(S)"
Else
If Not IsNull(rstMR!Mortgr1_Name) Then
mortgr = rstMR!Mortgr1_Name
End If
If Not IsNull(rstMR!Mortgr2_Name) Then
mortgr = mortgr & "/ " & rstMR!Mortgr2_Name
End If
End If