473,225 Members | 1,368 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,225 software developers and data experts.

help with relationships?

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

Nov 12 '05 #1
2 2525
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

Nov 12 '05 #2
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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

33
by: Joe | last post by:
I'm designing a company website. I'm relatively new to CSS and I'm having trouble creating what seems to me a very simple design: - body background: fixed full page image - banner: fixed, 100...
18
by: Jeremy Weiss | last post by:
I'm trying to build a database that will handle the monthly billing needs of a small company. I'm charting everything out and here's what I see: table for customers sub table to track payments...
21
by: Dan | last post by:
Hi, just ran into my first instance of a backend Access97 database not compacting. I'm getting the "MSACCESS.EXE has generated errors.." message on compact. I've narrowed it down to the largest...
20
by: Ed | last post by:
I am running Access 2002 and just ran the built in Access wizard for splitting a database into a back end (with tables) and front end (with queries, forms, modules, etc.). After running the...
2
by: James | last post by:
Hello all, In my music library database I have my stock categorised by media type; the types of media are: audio, book and score. My score type is the one I'm having difficulty with. Each...
6
by: Raji16 | last post by:
Hi, I am a new member. i am designing a simple judicial database system. however after creating tables i am a bit confused on setting the relationships between tables :confused: here is the link...
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
5
by: Kosmos | last post by:
Hey :) hopefully someone can help me with this...I decided to take on the task of programming an access database for my legal co-op/internship...I'm studying law and music production on the...
1
by: loner | last post by:
hey guys check dis link http://lonerattimessquare.blogspot.com/ maybe you all can help me build a reationship
13
by: ARC | last post by:
Hello all, Prior to going live with my app, I have questions on relationships theory. My prior app was done in Access 97, and I did NOT use relationships at all. I have 65 tables in my...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.