Concept problem - use module? 1

[solun]

I've created an Access 2K db for an audit department to audit loan files. I've got nice tables, queries, data entry forms, and now I need to calculate how well each file does. A file has loan information (main table), multiple applications, properties, disbursements (all 1-to-many), etc.

I want to be able to check if loan1 has an application, if it does, does the application have x, y, z. If something is missing, there should be an error message generated on a report. I need to also be able to collect all the information about all the loans audited and sumarize it on another report. What is the best way to go about this? Should I be using a module that does all the checking then generates the report?

Thanks for the help!


Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com

 

[willir]

Hi Heidi

does the application have x, y, z

Although you have not indicated what the tests are, I would that this could be done. Moreover, I would handle this as a function that returns True/False, or a text string indicating what is missing.

Not knowing your design, something like this...
- tblLoanApp - loan application
- LoanAppID - primary for the loan
- CreditCheckAmount - amount applicant is allowed
- FixedAddressYears - years applicant has lived at address
- RiskLevel - level of risk

Then, from the hip...

Function CheckLoan(LoanAppID as Long) as String

Dim rst as DAO.Recordset
Dim strSQL as String, strMsg as String

Set rst = CurrentDB.OpenRecordset(strSQL)
strMsg = ""

With rst
   If .RecordCount Then
      .MoveFirst

          If Nz(!CreditCheckAmount, 0) = 0 Then
             strMsg = "Failed - Credit amount" & vbCrLf
          End If

          If Nz(!FixedAddressYears, 0) < 3 Then
             strMsg = strMsg & "Failed - address check: " _
             Nz(!FixedAddressYears, 0) & " years" & vbCrLf
          End If

          If Nz(RiskLevel, 10) > 5 Then
             strMsg = strMsg & "Failed - high risk / risk not accessed: " _
             &  Nz(RiskLevel, 0) & vbCrLf
          End If

   Else
      strMsg = "Failed - No loan on file"
   End If

   .Close

End With

Set rst = Nothing

If Len(strMsg) = 0 Then
   strMsg = "Passed"
End If

CheckLoan = strMsg

End Function

Several things...
- The tests could be more than a simple If ... Then
- You can use a Function in a form, subform, report or event in a query...
SELECT ApplicantName, LoanNumber, LoanDate, CheckLoan(LoanAppID) FROM tblLoan

- You can test the results fairly easily. In the CheckLoan returned True/False...

If CheckLoan(LoanAppID) Then
.... action if true
Else
.... action if false

Or in the above example...

If Left(CheckLoan(LoanAppID), 6) = "Failed" Then
... action if loan check failed

Hopefully, you have some ideas on where to head next.
Richard

 

[solun]

Yes! Thank you. I'm going to work with this and will probably have detail questions, but that's where I was thinking I should go.

Best,

Heidi

Heidi I. Jones
Ridgerunner Consulting
Questions Answered. Problems Solved.

http://www.ridgerunnerconsulting.com