Best form design to link Master/Child fields

[John]

I have a payments table where I enter all payments in and out. I currently
have a payments form bound to the table and enter all payment info. I then
have to go to either the InvoiceOut form or the GoodsIn form to link the
payment to the appropriate Invoice. I do this using a combo box on each form
to fkPaymentID bound to pkPaymentID in the Payments table.
I want to be able to select the invoice connected to the payment from the
Payments form. I was thinking of a tabbed form with one tab for payments out
and one for payments in. What I'd like to know is the best way to select the
correct invoice and then display invoice table fields on the payments form
for that particular payment. Most payments are for several Invoices.
Any suggestions.
The table structure is
_____________
tblPayments
------------------
pkPaymentID
PaymentNumber
PaymentDate
PaymentAmount
TransReference (check number, credit card ref etc)
fkLodgementID
fkTransTypeID
fkCustomerID

_____________
tblkupTransType
-----------------
pkTransTypeID
TransactionType
____________
tblInvoiceID
---------------
pkInvoiceID
InvoiceNumber
InvoiceDate
OrderNumber
fkCustomerID
fkPaymentID

____________
tblGoodsIn
---------------
pkGoodsIn
DeliveryDate
SupplierInvoiceNumber
fkCustomerID
fkPaymentID
John

[Alan Webb]

John,
I'd probably set up my Invoice Out form and my Goods In form so they only
showed invoices and goods associated with the current payment. This would
require some care in coding to keep the amount of data being shoved about to
a minimum since each Form_Current event in Payments could potentially fire a
requery of Invoice Out and Goods In.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS"

"John" <br****@gofree.indigo.ie> wrote in message
news:d5**********@reader01.news.esat.net...

I have a payments table where I enter all payments in and out. I currently
have a payments form bound to the table and enter all payment info. I then
have to go to either the InvoiceOut form or the GoodsIn form to link the
payment to the appropriate Invoice. I do this using a combo box on each
form to fkPaymentID bound to pkPaymentID in the Payments table.
I want to be able to select the invoice connected to the payment from the
Payments form. I was thinking of a tabbed form with one tab for payments
out and one for payments in. What I'd like to know is the best way to
select the correct invoice and then display invoice table fields on the
payments form for that particular payment. Most payments are for several
Invoices.
Any suggestions.
The table structure is
_____________
tblPayments
------------------
pkPaymentID
PaymentNumber
PaymentDate
PaymentAmount
TransReference (check number, credit card ref etc)
fkLodgementID
fkTransTypeID
fkCustomerID

_____________
tblkupTransType
-----------------
pkTransTypeID
TransactionType
____________
tblInvoiceID
---------------
pkInvoiceID
InvoiceNumber
InvoiceDate
OrderNumber
fkCustomerID
fkPaymentID

____________
tblGoodsIn
---------------
pkGoodsIn
DeliveryDate
SupplierInvoiceNumber
fkCustomerID
fkPaymentID
John

[John]

"Alan Webb" <kn*******@hotSPAMmail.com> wrote in message
news:Yc********************@comcast.com...

John,
I'd probably set up my Invoice Out form and my Goods In form so they only
showed invoices and goods associated with the current payment. This would
require some care in coding to keep the amount of data being shoved about
to a minimum since each Form_Current event in Payments could potentially
fire a requery of Invoice Out and Goods In.
--

Thanks Alan
But my problem is how to select and display the invoice info from the
payments form. It is a credit business. Invoices are on the system for 30-60
days before payment. When a payment arrives, I enter the details onto the
payment form. I then want to select the invoice or invoices that the payment
is for. Also, when I write a check, I want to select the invoices I am
paying so they are taken off the monies owed list.

Any ideas appreciated

John