A/R take 2- Scott McDaniel

[Annie]

Hi, there. I set my forms up based on your
invoices/payment design. But I seem to be violating
the "one" side of a many-to-one relationship. I think I'm
not taking the DuesHeader and PaymentHeader into account.
Maybe?

I have Members that are assigned a
Member Category, and are assessed dues based on that
category. For example, John Doe has an "Individual"
Member Category so he owes $80, and then he pays $80.

I've set up 2 forms, both in datasheet view, on the Member
form. I have MemberDues, which when you click on a "dues"
line, the form below, MemberPayments, shows all of the
payments for that dues. I used the model from Northwind
Customer Orders, which shows the order number on the top
screen, with the products for each order in the bottom
screen.

On the MemberDues form, I enter the Dues due date and then
select the PaymentType and receive this error message: The
current field must match the join key DuesHeader.DuesID in
the table that serves as the "one" side of the many-to-one
relationship. Enter a record in the "one" side table with
the desired key value, and then make an entry with the
desired join key in the "many-only" table. I appreciate
any suggestions you can give. Thanks for your time.

This is the record source for Dues:
SELECT [DuesHeader].[MemberID], [DuesHeader].[DuesID],
[DuesItemType].[DuesItemType], [DuesHeader].[DateDue],
[DuesLineItem].[DuesItemID], [DuesLineItem].[DuesID],
[DuesLineItem].[DuesItemTypeID], [DuesLineItem].
[DateCreated], Format([AmountDue],"$0.00") AS DollarAmt,
[Members].[MemberID] FROM DuesItemType INNER JOIN
((Members INNER JOIN DuesHeader ON [Members].[MemberID]=
[DuesHeader].[MemberID]) INNER JOIN DuesLineItem ON
[DuesHeader].[DuesID]=[DuesLineItem].[DuesID]) ON
[DuesItemType].[DuesItemTypeID]=[DuesLineItem].
[DuesItemTypeID] ORDER BY [DuesLineItem].[DateCreated]
DESC;

And the record source for Member Payments:
SELECT [PaymentsHeader].[PaymentID], [PaymentsHeader].
[DuesID], [PaymentsHeader].[DateCreated],
PaymentsLineItem.*, [PaymentTypes].[Type] FROM
PaymentTypes INNER JOIN (PaymentsHeader INNER JOIN
PaymentsLineItem ON [PaymentsHeader].[PaymentID]=
[PaymentsLineItem].[PaymentsID]) ON [PaymentTypes].[TypeID]
=[PaymentsLineItem].[TypeID];

tblMembers
============
lngMemberID
lngMemberCategory [FK to tblMemberCategory]
strName
strAddress
etc etc

tblMemberCategory
============
lngMemberCategoryID
strMemberCategory

tblDuesHeader
==========
lngDuesID
lngMemberID [FK to tblMembers - tells you who owes these
dues]
dteDateDue
etc etc

tblDuesLineItem
===============
tblDuesItemID
lngDuesID [FK to tblDuesHeader]
lngDuesItemTypeID [FK to tblDuesItemType - allows you to
differentiate between basic dues, late fees, addtional
items, etc etc]
curAmountDue
dteDateCreated
etc etc

tblDuesItemType
===========
lngDuesItemTypeID
strDuesItemType

tblPaymentsHeader
===========
lngPaymentID
lngDuesID [FK to tblDuesHeader]
dteDateCreated
etc etc

tblPaymentsLineItem
==============
lngPaymentItemID
lngPaymentsID [FK to tblPaymentsHeader]
lngMemberID [FK to tblMembers]
lngType [FK to tblPaymentTypes - allows you to
differentiate between different types of payments,
i.e. "fake" money]
curAmount
dteDateRemitted
etc etc

tblPaymentTypes
===========
lngTypeID
strType