Limit Data Input

[Guest]

Probably simple but I can't figure this out.

I have three tables; jobs, invoices, and payments, all three are related by
a sales order number.

I have a form to enter each job in the jobs table with a subform for
invoices and a subform for payments. I want to limit entries for payments on
this form to match only the invoices related to the sales order number for
each job. Example: SO# 123 has invoice 456, only payment for 456 should be
posted in 123. Right now if someone mistakenly enters payment for 789 on SO#
123 the entry is still allowed because 789 exists in the table invoices. I
want a message to popup that says 789 is not related to SO# 123 and the entry
will not be allowed until the user goes to the SO# that 789 is entered under.

 

[Ron2006]

Probably simple but I can't figure this out.

I have three tables; jobs, invoices, and payments, all three are related by
a sales order number.

I have a form to enter each job in the jobs table with a subform for
invoices and a subform for payments. I want to limit entries for payments on
this form to match only the invoices related to the sales order number for
each job. Example: SO# 123 has invoice 456, only payment for 456 should be
posted in 123. Right now if someone mistakenly enters payment for 789 on SO#
123 the entry is still allowed because 789 exists in the table invoices. I
want a message to popup that says 789 is not related to SO# 123 and the entry
will not be allowed until the user goes to the SO# that 789 is entered under.

If the subforms are bound in a parent child relationship AND the SO#
is not even shown on the form (because it is not necessary) then the
payments will ONLY be for that SO#.

Ron

 

[Guest]

If the subforms are bound in a parent child relationship AND the SO#
is not even shown on the form (because it is not necessary) then the
payments will ONLY be for that SO#.

Ron

The subforms are bound in a parent child relationship.

The SO# is entered at the time a job is entered so it is neccessary to show
it on the form. The form has several tabs and includes a tab for invoices to
be entered and a tab for payments to be entered. When an invoice is sent out
a search is done to open the form based on the SO# so that the invoice data
is entered. When a payment is received same routine. The problem is that when
a payment is posted the person entering the payment may forget to search for
the next SO# and mistakenly enters the payment to the wrong SO#.

 

[Ron2006]

The subforms are bound in a parent child relationship.

The SO# is entered at the time a job is entered so it is neccessary to show
it on the form. The form has several tabs and includes a tab for invoices to
be entered and a tab for payments to be entered. When an invoice is sent out
a search is done to open the form based on the SO# so that the invoice data
is entered. When a payment is received same routine. The problem is that when
a payment is posted the person entering the payment may forget to search for
the next SO# and mistakenly enters the payment to the wrong SO#.- Hide quoted text -

- Show quoted text -

The description is not making sense.
If the tab/subform is in a parent child relationship then when they
are making payments they should not be entering another SO search.- at
least not from that tab since they are already sitting on an SO. But
then again I cannot see it so that may explain why I am getting
confused.

Are payments coming in and being posted to accounts by batches? In
other words when payments are being posted are they posting a lot of
payments at the same time.?

In general I would suggest you want the payment posting process to
require as few steps as possible. One form and no tabs (if possible)
or at least NOT having to go to one tab to search and another to post.
After they add the payment record, clear the form so that they are NOT
sitting on any SO or set a flag that will not allow two payments in a
row without another search (the search sets the flag off)

 

[Guest]

The search function is invoked by a command button that is on the form (not a
tab) the search closes the current job and opens a new job in the form.

Yes, they are typlically entering hundreds of payments against invoices at a
time. Problem is they forget to search for the next job to enter the payment.

The design of the form is neccasary as a business function. Management wants
to see everything related to a job in one place. Anybody reviewing a job can
click on a tab to see all materials received on that job, another tab to
review all invoices sent on that job, another tab to see all payments made on
that job. As the job progresses commission payments are made based on cost of
materials and payments received (automaticaly calculated in the background
but viewable on yet another tab). I know there is a lot going on on one form
but as I said, dictated by management. Everything else surprisingly (to me)
works very smoothly. Figuring out this part would relieve me of having to
audit every payment applied to make sure it is posted to the right job.

Thanks for your help.

 

[Ron2006]

Having a form where they are seeing all of the related information
etc. is fine. But it would seem that batch entering lots and lots of
payments (because that is a speciallized functioin) should have it's
own form and rules. Batch entering payments is a different function
then reviewing accounts even if they "can/could" enter single payments
on the larger form.

If you had a specialized form for it, you could reset the search box
and queries on that form after each payment so that they have to
search/enter another customer job number.

I have used / seen something like this.

Form with a subform query showing all payments made today.
unbound fields for each piece of information required for a payment
with a dropdown for the job number and unbound display fields that
will show pertinent information about the job/customer selected by the
dropdown.
button to accept payment.

When they press the button an append query is run using the data from
the form, the form is cleared, and focus is set back to the customer/
job combo. Also this lets me edit all the input fields BEFORE I even
enable the accept button. (check to see if this would make a credit
balance on the account, or anything else that would be nice to have.)
There could even be some other smaller subforms based on the selection
to "show" prior payments or total due or anything else that would
facilitate having the proper customer.

This would not affect whether they would / could also enter payments
from some other form, but would facilitate and control entering the
larger batches of payments.

Better application desine will design the forms and access by
function. One form for ALL functions will end up NOT covering all the
bases for ALL the functions that are being included in the form.

I have seen systems even post all payments as I described above to a
separte temporary file and include a balanceing function BEFORE
actually posting them to the account. That way a tape could be run of
all the payments the person thinks should be there with a total AND
the computer shows the user what they entered. If they balance then
they post the payments, otherwise they find the problem first and fix
it BEFORE posting.

Hope this gives you some ideas that you can sell.

Good luck.

Ron