Auto populate subform

[coyote]

Hi guys,
I have a loan issue form
with a subform.
In the main loan issue form i have

• LoanIssueID (assume 1)
• LoanIssueDate (assume date now)
• EmployeeID (assume John Doe)
• LoanAmount ( assume 1000)

Then I have unbound textboxes

• txtPayTimes (assume 2)
• txtAmount (500)
• txtBalance

The subform has

• LoanIssueID (Link Field)
• PayMonth (Combo with the monthname but month stored as number)
• PayYear (Combo with years)
• PayAmount

What am doing is: txtAmount=[LoanAmount]/[txtPayTimes] the figure i get i want to automatically populate the subform field PayAmount.
Assuming the current month is January, i want to populate field PayAmount=500 then the Balance goes to February=500 and the calculation should consider the year so that if the last month is new year it should be able to handle.

 

[CBrighton]

Had to create a database and play with it to get my head around it.

Give the attached database a look / try. I think it does roughly what you want it to do.

:edit:

Really any instance of the Date() function in the VBA should be replaced with a reference to the LoanIssueDate field on the main form, otherwise it will always calculate the payments due based on todays date rather than the loan date.

 

[coyote]

Works so nicely thanks a lot.
One thing if i put an amount like 5600 and payable in 3 months
i get decimals. Is there a way to calculate the amount in even numbers
and the last balance is inputed in the last month.
But the db works for me .
Thanks a lot

 

[CBrighton]

Round([FieldName],NumberOfDecimals) is the round function, however I'd advise checking the results the first few times to see when it rounds up and when it rounds down (its been a while since I used it).

As for the last month, that's more tricky. Personally I'd add more VBA code within the For...Next loop to check when the last month is being populated, it could then add up the amounts in the previous months (or keep a running total in an int variable) and minus that from the total amount to get the remainder.

Possibly it could be easier if you use the Mod operator to calculate the remainder, though this would involve editing the existing VBA and it's not something I have used.

 

[CBrighton]

Updated database attached.

Attachment refreshed to add rounding to the textbox on the main form too.

 

[coyote]

Thanks
Checking the new update.
Question though
Assume a loan of 12,000 is given in November 2011 and is payable in
6 months how do i make the year jump to 2012 after the December payment.
Thanks

 

[CBrighton]

You'd need an extra field to store the year for starters!

The code contains a For...Next loop, within that loop the variable "n" is the month. It should be a simple matter of adding a variable to hold the year, which starts out populated by Year([LoanIssueDate]), and if n = 12 then increase the variable by 1 after adding the record for December.

You would also add a line in the For...Next statement to set the field value to the variable value.

Something like (pseudocode):

PayMonth = n
PayYear = intYear      'This is your new variable
PayAmount =      'use the current PayAmount expression
If n = 12 then
     intYear = intYear + 1
End if

That way it will correctly set the year for the Dec payment, then change the year, then use the new year for the Jan payment if there is one.

 

[coyote]

Tried but am getting an error can't go to the specified record.
CBrighton can you attach the code in the sample dbase you sent me.

 

[CBrighton]

Updated database attached.

 

[coyote]

Works fine except there is an overflow error when
for example 40,000 payable in 6 months starting november.
Thanks a lot

 

[CBrighton]

It's intAdded, it needs to be a larger data type for larger values.

I tried changing it from Integer to Long and was able to use values as high as £100k without having any problems.

You may need to try the database with the largest loan amount possible and try different data types for intAdded. Google can tell you the limitations of each numeric data type for your version of Access.