Access 2000 Form/Sub-form

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

nwinchel

Thread Starter
Joined
Mar 19, 2001
Messages
59
This ought to be pretty simple to do. I have a small table that contains contact information for a number of businesses: name, address, etc. I have an “ID” field that is an auto number and is the PK. I have created another table that contains the business name, date of a donation and a memo field to hold the donation info (since it’s stuff instead of money.) In the second table, “ID” field is also an auto number and is PK. I have related the tables with a one to many: theoretically one business with many possible dates/donations which is the basic premise. I created a main form to input the business data and created a sub-form for the donation data based on their respective tables. Each business will be unique but a business may give multiple donations, say one each month or more – whatever.

I can’t get it to work. I’ve got that big, honkin’ Access 2000 Developer’s Handbook but am still getting up to speed using it…. but I’ll keep looking until I hear from someone! :D

Thanks.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
"I can't get it to work"

What does that mean, Nancy? Are you getting an error; can't figure out how to put it together, or what?

Did you open the main form in design view, then drag the subform onto (presumably) the bottom of the main form?
 

nwinchel

Thread Starter
Joined
Mar 19, 2001
Messages
59
Originally posted by Dreamboat
"I can't get it to work"

What does that mean, Nancy? Are you getting an error; can't figure out how to put it together, or what?

Did you open the main form in design view, then drag the subform onto (presumably) the bottom of the main form?
I apologize for not being more clear. Yes, I opened the main form in design view and dragged the subform onto it. According to the book I referenced I should have been presented with a Wizard but that did not happen.

When I try to enter data into the subform, say, an additional donation for a business already listed, I get the following error:

"You cannot add or change a record because a related record is required in table 'BusinessContactInfo'"

My form/subform works great with a new or initial business contact and donation. The problem is when I want to add a subsequent donation to an already-listed business.
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Do you have your wizards installed? It's fairly common for people to find out that they don't.
 
Joined
Jul 29, 2001
Messages
21,334
Is the record source for the main form defined? Sounds like your one-to-many relationship is going the wrong way because it is thinking it needs to add a record to the 'BusinessContactInfo' table also. In your sub form is there a parent-child relationship?
 

Guy

Joined
Feb 19, 1999
Messages
260
What two colums are being related to each other in the one-to-many relationship? It sounds like you wna the ID fields related but that will not work if they are both autonumber. What you may need to do is add a column to your subtable called "Link_ID" or similar and make it number format, and indexed with duplicates allowed. You can then select both the link_ID field and the ID field in the subtable's design view and make them primary keys.

On your main form, check your subform's properties. The "Link Child Field" property should be "Link_ID", and the "Link Master Field" property should be "ID"

Test this out and let know how it works.

- Guy
 

nwinchel

Thread Starter
Joined
Mar 19, 2001
Messages
59
Originally posted by Guy
What two colums are being related to each other in the one-to-many relationship? It sounds like you wna the ID fields related but that will not work if they are both autonumber. What you may need to do is add a column to your subtable called "Link_ID" or similar and make it number format, and indexed with duplicates allowed. You can then select both the link_ID field and the ID field in the subtable's design view and make them primary keys.

On your main form, check your subform's properties. The "Link Child Field" property should be "Link_ID", and the "Link Master Field" property should be "ID"

Test this out and let know how it works.

- Guy
Thank you for your help. I apologize for not getting back to you due to illness and the holidays. Yes, this solution worked nicely and I have added your advice to my ever-growing file of "Access Nuggets"!
 

Anne Troy

Anne
Joined
Feb 14, 1999
Messages
11,746
Are your Access nuggets pure Access or do they also include by-products?

LOL
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top