Cant Add New Loans to Amort21 for Client or Prov - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Cant Add New Loans to Amort21 for Client or Prov

Dave Clark

15 months ago

Can't add new loans to Amort2.1 for client or Provider.

Downloaded Amort2.1 to trusted Location on a 64-bit and a 32-bit office installation and I'm having an issue adding new loans. I can't add from either The Client Form or the Provider Form.

I get: Run-time error '3075':
Syntax error (missing operator in query expression 'LoanID='.

Not sure what I am missing or need to change.

Thak You in advance for any help.

	John Davy @Reply 15 months ago
Hi Dave, Show us the query. We must be able to see what is going on in order to help you. We need details related to the issue. John

Dave Clark OP @Reply

15 months ago

Hey John,

Thank you for responding.

Here is the private sub Access says the error is in: By the way this is the Amort 2.1 Template (unedited freshly downloaded).

Private Sub SetLifeOfLoanDates()

    Dim LoanStartDate As Date, LoanEndDate As Date, MinPaymentDate As Date, MaxPaymentDate As Date

    ' Determine loan start/end dates
Error -     LoanStartDate = Nz(DMin("DueDate", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID), TempVars("MINDATE"))
    LoanEndDate = Nz(DMax("DueDate", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID), TempVars("MAXDATE"))

Dave Clark OP @Reply

15 months ago

Lets Start Over,

When I click the ClientF Add New Loan Button or the ProviderF Add New Loan Button I get the same error I see that they have the same code.
The button forwards me to this Private Sub SetLifeOfLoanDates()

Dim LoanStartDate As Date, LoanEndDate As Date, MinPaymentDate As Date, MaxPaymentDate As Date

    ' Determine loan start/end dates
    LoanStartDate = Nz(DMin("DueDate", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID), TempVars("MINDATE"))
    LoanEndDate = Nz(DMax("DueDate", "ScheduleT", "LoanID=" & Forms!LoanF!LoanID), TempVars("MAXDATE"))

    ' see if there is a payment before the loan start date or after the loan end date
    MinPaymentDate = Nz(DMin("PaidDate", "PaymentT", "LoanID=" & Forms!LoanF!LoanID), TempVars("MAXDATE")) ' yes, MAXDATE
    MaxPaymentDate = Nz(DMax("PaidDate", "PaymentT", "LoanID=" & Forms!LoanF!LoanID), TempVars("MINDATE")) ' yes, MINDATE
    If MinPaymentDate < LoanStartDate Then LoanStartDate = MinPaymentDate
    If MaxPaymentDate > LoanEndDate Then LoanEndDate = MaxPaymentDate

	Dave Clark OP @Reply 15 months ago
Also this is one of Richards Templates.

Kevin Robertson @Reply

15 months ago

The error is occurring because there is not yet a LoanID for the new loan.
Here's the fix:

In SetLifeOfLoanDates in the Global Module, simply add the NZ Function

    ' Determine loan start/end dates

    LoanStartDate = Nz(DMin("DueDate", "ScheduleT", "LoanID=" & Nz(Forms!LoanF!LoanID, 0)), TempVars("MINDATE"))

    LoanEndDate = Nz(DMax("DueDate", "ScheduleT", "LoanID=" & Nz(Forms!LoanF!LoanID, 0)), TempVars("MAXDATE"))

    

    ' see if there is a payment before the loan start date or after the loan end date

    MinPaymentDate = Nz(DMin("PaidDate", "PaymentT", "LoanID=" & Nz(Forms!LoanF!LoanID, 0)), TempVars("MAXDATE")) ' yes, MAXDATE

    MaxPaymentDate = Nz(DMax("PaidDate", "PaymentT", "LoanID=" & Nz(Forms!LoanF!LoanID, 0)), TempVars("MINDATE")) ' yes, MINDATE

	Dave Clark OP @Reply 15 months ago
That Worked! Thanks Kevin!

	Kevin Robertson @Reply 15 months ago
You're welcome.

	Alex Hedley @Reply 15 months ago
Richard

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View