Question about an Array in a form

[Joe Cilinceon]

What I working on is a payment posting form. The underlying tables are as
below.

Ledger
Transaction (auto number)
LedgerID (unique text field made up of CustNo, Unit and Date Rented
and linked to the Lease Table)
PaymentDate
the rest here is a brake down of what the payment is covering such as
rent and such

Payments
Transaction (linked to the above Transaction #)
PayAmount
PayType (# code from a lookup table like cash, check, credit card, etc)
Tracking (text field to hold the check no, ect

Both of these tables are updated with an SQL query in code as part of a Post
Payment button.

Now the problem is a single transaction can have several different payment
types for the whole transaction. It isn't uncommon for us to get a split
payment at the same time as we are posting it. What I'm asking is how to
best have the text boxes appear as needed to do this. It would need a
PayAmount fields, PayType field and Tracking field for each total payment
made. I sure hope that is clear.

I was thinking of setting up an Array to do this but have no idea how to do
this access. This is all pretty new to me.

 

[Albert D. Kallal]

I would suggest you use a side by side arrangement.

Take a look at the following screen shots. In fact, near the bottom I have
an example GL account screen where the funds need to be "split".

http://www.members.shaw.ca/AlbertKallal/Articles/Grid.htm

And, ...no, you don't need to use an array......

 

[Joe Cilinceon]

Thanks that helps a lot.

 

[Joe Cilinceon]

Hi Albert

I have a couple of questions about the very last screen shot on the page
listed below. How does the table on the right tie in with the balance show
at the bottom. I've found a way to get the next transaction number to use
and I'm using a Datasheet for the actual entry of the payment parts. However
I'm having a problem having the from update the total field. I also setup a
temp table for these payments and have it as a subform. Once I hit the Post
Payment button I have an append query move the payment to the payment table
and the details to the ledger table. I then use a delete query to clean out
the tmpPayment table which is all working pretty well. I'm also trying to
get the amount field to default to what is owed, for example:

If they owe $50 the first amount field in the grid will default to $50. Now
if I split the payment and they give me a check for 30 when I edit the first
field I would like it to start a second record with $20 and so on. It is
also possible that they could overpay, which is held in a Credit field.
Thanks again for your response Albert.

 

[Albert D. Kallal]

Sorry, my newsreader missed this...

listed below. How does the table on the right tie in with the balance show
at the bottom.

If you look at the form, on the left side, we have 123 "kallal" highlighted.
The total amount entered is $50.

On the right side, we will now will split up the amounts, and they must =
$50.

If I use the up-down arrow keys while in the left side, of course the right
sides shows the correct "split" amounts for the single entry.

If they owe $50 the first amount field in the grid will default to $50.

Yes, I also default to $50 in this example. If the user hits enter, they are
now on the next line. If they just hit enter again (and don't type
anything),
then I throw the cursor back to the left side. This means the whole process
of entering a amount, and splitting out the data can be done 100% via the
key board.

Now if I split the payment and they give me a check for 30 when I edit
the first field

Exactly what I do. And, if you enter a different amount, the green box on
the bottom turns red. So, that green total amount is just the current total
of
the amounts you are typing in. And, since I set the default amount..then
most of the time that box stays green...but if you enter a wrong amount,
then the box turns red.

I would like it to start a second record with $20 and so on. It is also
possible that they could overpay, which is held in a Credit field. Thanks
again for your response Albert.

You are welcome. In my example, the users are forced to balance...but you
can of do this however you wish.