My formatting got hosed the first time I posted this so here goes again:
I've been working on an Access 2000 database for a couple of weeks now. I
took a course in access about a year ago, a crash course, and I learned a
ton, but I didn't touch Access for the year since then so I forgot
everything I learned : ( So this little project I envisioned has turned out
to be much harder than I thought. But I think I'm close with it, so I want
to see it through.
What I wanted to create is a database that will track my bill payments.
Personal bill tracking. I tried Quicken and MS Money demos and they are just
way more than I'd ever want/need, and quite honestly I don't think they do a
very good job of simple bill tracking/payments from a user standpoint. I was
keeping track of this stuff in Excel, but since I wanted to re-learn Access
I wanted to make something cool with this.
I *think* I finally got my table design correct (this is the 2nd
iteration...the first was basically a flat database, just one table!). So
here are the tables (primary key has asterisk next to it, foreign key has +
next to it):
tblPayees: *PayeeID, PayeeName, PayeeNotes
tblPayeeAccounts: *AccountID, AccountBalance, AccountInterestRate, +PayeeID
tblBills: *BillID, BillName, PaymentFrequency, BillPaymntAddr1, 2, 3,
BillCity, BillState, BillZIP, BillNotes, BillPaymentMethod, +AccountID,
BillDueOnDay
tblBillPayments: *PaymentID, *+BillID, *PayeeID, *AccountID, PaymentDate,
PaymentAmtDue, PaymentAmt, PaidIndicator, PaymentDueDate
Note that tblBillPayments' primary key is a compound key among the four
indicated. I also have lookup tables for PaymentFrequency, PaymentMethod,
and
BillNames.
I think I have the above correct...I am probably wrong about that though.
Basically, each table is a one-to-many: One payee to Many Accounts; One
Account to Many Bills; One Bill to Many BillPayments.
I entered in test data and then used the form wizard to create a form that
does the following:
when I open it up, I want to have a combo-box that will allow me to select a
Payee Name. Then, the subforms below the main form will populate with the
Payee's associated accounts, bills, and bill payments.
I can't figure out what I'm doing wrong. I"ve tried everything I could think
of. I've gotten as far as being able to have what appears to be the right
form layout. When I open it up initially, I'll get the first Payee listed in
the combo-box, and the correct account/bill/billpayment info, but then I
couldn't select any other payees. Whenevr I set the source for the combo box
to Payee Name all I can select is the PayeeID, and when I do select the
PayeeID the sub-forms don't update with that PayeeID's information (its
associated account(s), bill(s), payments).
What am I doing wrong? ANY help would be greatly appreciated. Responses on
the NG would be great, but if you prefer I can be reached at
ma******@verizonTAKETHISOUT.net
Thanks so much in advance. I'm at my wits end with this.
mark