Originally Posted by
rlsublime
I am trying to alter the following code to add this additional criteria:
and where where B_loan_number='" & BLoanNumber & "'"
I am trying to incorporate the BLoan as well but I am getting errors when i try to add it to my sql statements. Any ideas what i am doing wrong here? thanks
Code:
Dim rs As Recordset
SQL = "select * from investor_request where A_loan_number='" & ALoanNumber & "'"
Set rs = CurrentDb.OpenRecordset(SQL)
If (rs.RecordCount > 0) Then
SQL = "Update Investor_Request set Status = 'Closed' where A_Loan_Number ='" & ALoanNumber & "'"
CurrentDb.Execute SQL, dbFailOnError
End If
You didn't specify whether or not one or both of the SQL statements should be changed. Here's your code with both statements modified. I also added some more-robust code to the recordset section to deal with the possibility of an empty set -- I've encountered errors when I play with recordsets unless I explicitly check first to make sure they're not empty, and the .BOF/.EOF check is more reliable for me than testing for RecordCount > 0:
Code:
Dim rs As DAO.Recordset
SQL = "select * from investor_request where ((A_loan_number='" & ALoanNumber & "') AND (B_loan_number='" & BLoanNumber & "'));"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenDynaset)
With rs
If not (.BOF = True AND .EOF = True) Then
SQL = "Update Investor_Request set Status = 'Closed' where ((A_loan_number='" & ALoanNumber & "') AND (B_loan_number='" & BLoanNumber & "'));"
CurrentDb.Execute SQL, dbFailOnError
End If
End With
The other important thing is to double-check your criteria for the WHERE clause: Do you want BOTH loan numbers to match, or EITHER one? If it's either, then change the AND between the two loan numbers to OR. Using AND will only build a recordset or perform the update if both numbers match.
Steve