help with relationships?

[mark]

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 tblPayeeAccounts tblBills
tblBillPayments
*PayeeID *AccountID *BillID
*PaymentID
Payee Name AccountBalance Bill Name
*BillID
Payee Notes AccountInterestRate PaymentFrequency *PayeeID
+PayeeID BillPaymentAddr1
*AccountID

BillPaymentAddr2 PaymentDate

BillPaymentAddr3 PaymentAmountDue
BillCity
PaymentAmt
BillState
PaidIndicator
BillZIP
PaymentDueDate
BillNotes

BillPaymentMethod
+AccountID

BillDueDayofMonth

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

[Ken Ismert]

Mark,

Your post garbled your table/field lists, but from what I can make
out, your table structure seems to be OK. I think your real problem is
with form synchronization.

Some quick pointers:

1. The combo box that selects the Payee name can't be bound to the
form's RecordSource. In other words, set the combo's ControlSource
property to nothing. Use a hidden text box to hold a copy of the
current PayeeID.

2. Synchronize your form's query to the combo box. To do this, design
the query that the form uses, and in the Criteria row under PayeeID,
put:

[Forms]![yourPayeeForm]![cboPayee]

This will restrict the records returned to the current cboPayee Value,
which should be set to be the PayeeID.

3. In the combo's AfterUpdate Event, requery your form. To do this,
select the Event tab in the combo's property box, and click the [...]
button next to After Update. Select Event Procedure, and enter a
single line in the routine:

Private Sub cboPayee_AfterUpdate()
Me.Requery
End Sub

Now, when you select a new Payee in the combo, only the records for
that PayeeID will be displayed in the form.

You should be able to adapt this common technique to the other forms
you need to synchronize.

-Ken

"mark" <ma******@comcast.net> wrote in message news:<bl************@ID-130190.news.uni-berlin.de>...

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 tblPayeeAccounts tblBills
tblBillPayments
*PayeeID *AccountID *BillID
*PaymentID
Payee Name AccountBalance Bill Name
*BillID
Payee Notes AccountInterestRate PaymentFrequency *PayeeID
+PayeeID BillPaymentAddr1
*AccountID

BillPaymentAddr2 PaymentDate

BillPaymentAddr3 PaymentAmountDue
BillCity
PaymentAmt
BillState
PaidIndicator
BillZIP
PaymentDueDate
BillNotes

BillPaymentMethod
+AccountID

BillDueDayofMonth

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

[Ken Ismert]

Mark,

Some more clarification:

When writing DB applications, you have to make a distinction between
user controls and data controls:
* User controls run the application. Buttons like "Save", "Close" or
"Open Report" are examples. These controls are not bound to data.
* Data controls allow the user to edit the data. They are ultimately
bound to a particular field in a particular table.

Your combo box allows the user to select another payee, and causes the
form to requery to display the appropriate data. Therefore, it is a
user control, and shouldn't be bound to any data. Since you have two
subforms, your code might look more like this:

Private Sub cboPayee_AfterUpdate()
Me.Requery
yourSubForm1.Requery
yourSubForm2.Requery
End Sub

(Actually, if you set the Link Child and Link Master Fields properties
properly for the subforms, the two subform Requery lines aren't
necessary)

Also, I was using a common convention in these groups when I chose my
form and control names. Since I didn't know the actual names, I made
up my own. Using this convention, it is implied that you will replace
"yourPayeeForm", "yourSubForm1" and "cboPayee" with the actual names
of your forms and controls.

It is hard to avoid using VBA code when building your DB app.
Fortunately, you can get away with using very little.

Now that we're discussing VBA, its time to touch on more conventions.
The VB and Access programming communities have come up with naming
conventions to make programs easier to understand by providing context
information. This takes the form of 1 to 3 letter prefixes, which give
you clues about what is being worked with. Here is a (partial) list:

txt - Text Box
cbo - Combo Box
frm - Form
lbl - Label
cmd - Command Button

cboPayee was so named to make it clear in code that we are dealing
with a combo box. You can do a search on "Naming Convention" in this
group and learn a lot more.

Building your first DB app can be a challenge. All I can offer is: be
persistent. Keep using the help files, searching the Internet, and
researching these groups. You will get there, although maybe not as
fast as you'd like.

-Ken

ki*****@texassystems.com (Ken Ismert) wrote in message news:<c7**************************@posting.google. com>...

Mark,

Your post garbled your table/field lists, but from what I can make
out, your table structure seems to be OK. I think your real problem is
with form synchronization.

Some quick pointers:

1. The combo box that selects the Payee name can't be bound to the
form's RecordSource. In other words, set the combo's ControlSource
property to nothing. Use a hidden text box to hold a copy of the
current PayeeID.

2. Synchronize your form's query to the combo box. To do this, design
the query that the form uses, and in the Criteria row under PayeeID,
put:

[Forms]![yourPayeeForm]![cboPayee]

This will restrict the records returned to the current cboPayee Value,
which should be set to be the PayeeID.

3. In the combo's AfterUpdate Event, requery your form. To do this,
select the Event tab in the combo's property box, and click the [...]
button next to After Update. Select Event Procedure, and enter a
single line in the routine:

Private Sub cboPayee_AfterUpdate()
Me.Requery
End Sub

Now, when you select a new Payee in the combo, only the records for
that PayeeID will be displayed in the form.

You should be able to adapt this common technique to the other forms
you need to synchronize.

-Ken

"mark" <ma******@comcast.net> wrote in message news:<bl************@ID-130190.news.uni-berlin.de>...

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 tblPayeeAccounts tblBills
tblBillPayments
*PayeeID *AccountID *BillID
*PaymentID
Payee Name AccountBalance Bill Name
*BillID
Payee Notes AccountInterestRate PaymentFrequency *PayeeID
+PayeeID BillPaymentAddr1
*AccountID

BillPaymentAddr2 PaymentDate

BillPaymentAddr3 PaymentAmountDue
BillCity
PaymentAmt
BillState
PaidIndicator
BillZIP
PaymentDueDate
BillNotes

BillPaymentMethod
+AccountID

BillDueDayofMonth

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