By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,501 Members | 2,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,501 IT Pros & Developers. It's quick & easy.

Linking subforms - error message

P: 61
I have a database that tracks the marketing source of new cases (law firm) and flags potential new cases that need to be followed up on until they are accepted, declined, the client stopped responding, or they are referred out to another firm. I have a lot of “feeder” tables that populate drop down boxes, but have four main tables. I have a Form (frmMaster) that collects the initial data on the marketing source (tblIntakeMaster), the client info (tblClient), the accident info (tblAccident), and if there is any follow up needed (tblFollowUp).

There is also a table (tblReferIn) that tracks info on cases referred in from another lawyer and a table (tblReferOut) that tracks case info on cases referred out to other firms. I have not been able to implement these because my main form (frmMaster) is not working. So this will be the next phase. However, I keep getting the following error message on the frmMaster when trying to enter new records:

“You cannot add or change a record because a related record is required in table ‘tblClient’.

My main Form (frmMaster) is made up of 3 Pages: Intake Info, Client Info & Accident Info, with Accident Info having another subform for follow ups.

The Record source for frmMaster is tblIntakeMaster (PK=IntakeMasterID) and is Page1 = Intake Info.

The 2nd Page is Client Info, Record source is sfrmClient, which is tied to tblClient (PK=ClientID, FK=IntakeMasterID) I added AccidentID as another FK because I kept getting the above error, but it still didn’t work. The Link Master & Child Fields on sfrmClient is IntakeMasterID.

The 3rd Page is Accident Info, Record source is sfrmAccident, which is tied to tblAccident (PK=AccidentID, FK=ClientID & IntakeMasterID). The Link Master is IntakeMasterID and the Link Child is ClientID.

At the bottom of Page 3 is another subform named sfrmFollowUp, which is tied to tblFollowUp (PK=FollowUpID, FK=AccidentID). The Link Master & Child Fields on sfrmFollowUp is AccidentID.


I have added fields to my tables, changed the Link records, and nothing seems to work. I even created a sample database and it worked! Could it be that I have to rebuild this database once again in order to make it work because I didn’t have the structure setup the first time? I am attaching my database if anyone wants to take a look at. The problem is in frmMaster when you try to add a record and has something to do with tblClient and tblAccident. I really appreciate any help!

Thanks,
Mike
Attached Files
File Type: zip IntakeMktgDB_ver2.zip (224.6 KB, 70 views)
Oct 16 '12 #1

✓ answered by Rabbit

It probably has something to do with the way you set up your relationships. But I can't be sure without test data. And I can't open the database you attached. So either you can make a copy of your database in 2003 or 2007 format or hopefully someone with whatever version you're using comes along and takes a look.

Share this Question
Share on Google+
20 Replies


Rabbit
Expert Mod 10K+
P: 12,315
I can't open that database. It must be in a newer version. Either save it in 2003 format or describe the relevant structures here.
Oct 16 '12 #2

P: 61
see attached PDF copy of table structure
Attached Files
File Type: pdf Table Structure ver2.pdf (99.7 KB, 158 views)
Oct 16 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
I would also need to see the form setup. Not everything, just the few properties related to the subforms and how they're being linked.
Oct 16 '12 #4

P: 61
Ok, let me know if this helps.

Expand|Select|Wrap|Line Numbers
  1. sfrmClient (Page 2)
  2.    Source Object = sfrmClient
  3.    Link Master Field = IntakeMasterID
  4.    Link Child Field = IntakeMasterID
  5.    Filter on Empty Master = Yes (same for all subforms)
  6.    Enabled = Yes (same for all subforms)
  7.    Locked = Yes (same for all subforms)
  8.  
  9. sfrmAccident (Page 3)
  10.    Source Object = sfrmAccident
  11.    Link Master Field = IntakeMasterID
  12.    Link Child Field = ClientID
  13.  
  14. sfrmFollowUp (within Page 3)
  15.    Source Object = sfrmFollowUp
  16.    Link Master Field = AccidentID
  17.    Link Child Field = AccidentID
Thanks
Oct 16 '12 #5

Rabbit
Expert Mod 10K+
P: 12,315
It probably has something to do with the way you set up your relationships. But I can't be sure without test data. And I can't open the database you attached. So either you can make a copy of your database in 2003 or 2007 format or hopefully someone with whatever version you're using comes along and takes a look.
Oct 16 '12 #6

Seth Schrock
Expert 2.5K+
P: 2,931
Here is the database in 2003.
Attached Files
File Type: zip IntakeMktgDB_ver2.zip (179.6 KB, 92 views)
Oct 17 '12 #7

twinnyfo
Expert Mod 2.5K+
P: 3,061
I can "open" Seth's version of the DB, but get errors for missing DLL's, so I am unable to open frmMaster to try to induce the errors..... However, it is most likely, as Rabbit surmises, a problem with the relationships, which I have been studying in your DB, trying to see if I can identify anything "visually"....
Oct 17 '12 #8

P: 61
I am sorry but I only have Access 2010. I was not able to save in a lower version. I believe the problem is with relationships. I am going to export all tables to a new database and see what happens. After that, I will start deleting fields and/or tables. I believe that some of the problem might be with the AttorneyID with is in the IntakeMaster table and Accident table. I guess it's going to be another long day.

Thanks for your help. Still trying to figure this out.
Oct 17 '12 #9

zmbd
Expert Mod 5K+
P: 5,287
V2010 to save in earlier format:
{Ribbon}
{file}
-leftpane (mid-bottom)- {save&publish}
-rightpane- "save database as"
select the [2002 - 2003 (*.mdb)]
click on the [saveas] button under the pane
follow prompts
Caution: This will strip anything from the database that is not available in the olderversions... I've found that alot of forms will suffer this fate.
Oct 17 '12 #10

zmbd
Expert Mod 5K+
P: 5,287
mburch2000
I've played with your main form and can not get your error...
Check that your office has the lastest patches.
Give me the baby-step-by-step as to how the error was generated.

What I have noted is that you can enter data into any of the tabs in any order; however, you can't get back to that data... for example, if you enter information in tab accident info and then move away from that record without information in the other two, then there is no way from your form to enter the missing customer information same thing with customer information... you have to start with the first tab and then move on to the second etc..
Oct 17 '12 #11

P: 61
@zmbd, you are right about saving in lower version. I was stupid on this point. As far as the error goes, I entered data on page1 (Intake Info), page2 (Client Info), and then page3 (Accident Info). When I got to the last field on sfrmAccident, either Refer Out Attorney or Intake Status, and hit tab to go to the Follow Up section at bottom, the error occurred.

And yes, you are right about the missing information if you skip a tab. I don't know how to tie them together yet, any suggestions?
Oct 17 '12 #12

zmbd
Expert Mod 5K+
P: 5,287
Nope.
Can't re-create the error even following the steps given.
As for getting back to orphaned records, i.e. tblaccident from the tab "accident info," the interface is quite complex and will take awhile to work thru.
Oct 17 '12 #13

Rabbit
Expert Mod 10K+
P: 12,315
Are you using the tab key to move through your forms and then clicking on the next tab after you've tabbed out of your last field? You do realize that the client form will move on to a new record if you tab on the last field right? And when you do finally reach that last field and tab out and it tries to save, there won't be an existing record for it to link to.
Oct 17 '12 #14

P: 61
Yes I am using the tab key to move through the forms. And yes I do realize that if you are on the last field and tab, it goes to the next record. These are some of the problems I need to fix after I get everything working.
Oct 17 '12 #15

Rabbit
Expert Mod 10K+
P: 12,315
So then that's probably what's causing the error you got. Because I don't get the error you're getting.
Oct 17 '12 #16

twinnyfo
Expert Mod 2.5K+
P: 3,061
Rabbit is so smart! I never would'a thought to tie those events together to cause the error..... Good thing to keep in mind, though, as I continue to improve my designs!
Oct 17 '12 #17

P: 61
Ok then, makes sense. So how do I control the tab key so that when it gets to last record on page, it automatically goes to next page, instead of next record? And when I get to last page, how do I get the tab key to go to first page of next record automatically? This sure would help my design unless you have another suggestion.
Oct 17 '12 #18

Seth Schrock
Expert 2.5K+
P: 2,931
There are a few options that you have. You could not tab out of the last control and just click on the next tab or you could put a save button on the form what would be the next tab stop. This would save the record without going on to the next record.
Oct 17 '12 #19

P: 61
Seth, I'm working on it now. Good suggestion! Thanks
Oct 17 '12 #20

P: 61
Unbelieveable, it works now! I modified several tables, the relationships between tables, and added/deleted fields in tables. I linked my main tables together and pulled queries to populate the feeder tables and removed any relationships to the main tables. The relationships is what created the problems. I really appreciate everyone's help. I'm sure I'll probably have other questions, but you guys are a great support group! Thanks a bunch.

Mike
Oct 17 '12 #21

Post your reply

Sign in to post your reply or Sign up for a free account.