Tech Support Guy banner
Status
Not open for further replies.
1 - 8 of 8 Posts

·
Registered
Joined
·
59 Posts
Discussion Starter · #1 ·
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.
 

·
Registered
Joined
·
11,755 Posts
"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?
 

·
Registered
Joined
·
59 Posts
Discussion Starter · #3 ·
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.
 

·
Registered
Joined
·
21,334 Posts
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?
 

·
Registered
Joined
·
260 Posts
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
 

·
Registered
Joined
·
59 Posts
Discussion Starter · #7 ·
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"!
 
1 - 8 of 8 Posts
Status
Not open for further replies.
Top