I have a 2 tables (tblCase and tblCaseDetail) linked via CaseID. CaseID is the Primary Key in tblCase and a foreign key in tblCaseDetail. Referential integrity is enforced and there are currently no records in tblCaseDetail. I have a form (frmCase) that is used to display data from tblCase. On frmCase I have a command button that is used to open a form (frmCaseDetail) and add data to tblCaseDetail. I keep getting the following error when I try to add data though: You cannot add or change a record because a related record is required in table 'tblCase'. I'm not sure what the issue is. I have the foreign key field set to default to the value of primary key from whatever record is open on frmCase. I purposely made the foreign key field visible on frmCaseDetail so that I could make sure it was defaulting correctly and therefore I can see the Primary Key value of the record it says doesn't exist. So, obviously the record it's saying doesn't exist DOES exist. What am I missing here?
Error: You cannot add or change a record
- Thread starter twimms
- Start date
You have a Form and SubForm.?
The Form should use tblcase as it's data source and the SubForm use tblcasedetail as it's data source.
Link the two with Master Child fields.
set the properties of the forms to allow additions.
If your data source is a query that uses more then one table then you will not be able to make changes to the Record source. Only Tables or better still, Queries that just use one table will allow this.
The Form should use tblcase as it's data source and the SubForm use tblcasedetail as it's data source.
Link the two with Master Child fields.
set the properties of the forms to allow additions.
If your data source is a query that uses more then one table then you will not be able to make changes to the Record source. Only Tables or better still, Queries that just use one table will allow this.
Thanks for the suggestions. Bob - I tried your solution, but it didn't work for me. PNGBill - No, this isn't a form/subform situation. I'm using a command button to open frmCaseDetail and trying to insert the required ID number from tblCase (that is - the current record on frmCase) into the foreign key field in tblCaseDetail and then add additional data into that record. I've tried doing it 2 ways. One way is by using the following code in the command button:
Private Sub CmdDetail_Click()
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
stDocName = "frmCaseDetail"
stLinkCriteria = "[CaseID]=" & Me![CaseID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , CaseID
End Sub
Another way is by the following code in the On Load event of frmCaseDetail:
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.CaseID.DefaultValue = Me.OpenArgs
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
End Sub
Like I said, the correct value shows up, but I still get the error message.
Private Sub CmdDetail_Click()
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
stDocName = "frmCaseDetail"
stLinkCriteria = "[CaseID]=" & Me![CaseID]
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , CaseID
End Sub
Another way is by the following code in the On Load event of frmCaseDetail:
Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.CaseID.DefaultValue = Me.OpenArgs
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End If
End Sub
Like I said, the correct value shows up, but I still get the error message.
boblarson
Smeghead
- Local time
- Today, 13:41
- Joined
- Jan 12, 2001
- Messages
- 32,040
First of all, you should not use:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
over what I supplied. That is OLD outdated code.
Second you don't need to set the default value.
You just set the value in a new record:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
over what I supplied. That is OLD outdated code.
Second you don't need to set the default value.
You just set the value in a new record:
Code:
Private Sub Form_Load()
If Me.NewRecord = False Then
DoCmd.RunCommand acCmdRecordsGoToNew
End If
If Not IsNull(Me.OpenArgs) Then
Me.CaseID = Me.OpenArgs
End If
End Subtry remove,,,CaseID above.
Bob - I appreciate the fact that your code is more up-to-date. I simply stated what I had used first. I tried my code first. I then took it out and tried the code you supplied with no better luck. So, while I will use the code you provided in the future, I'm afraid it had no impact on my current issue.
The code you provided above doesn't fix the issue either unfortunately. Do you have any other suggestions?
boblarson
Smeghead
- Local time
- Today, 13:41
- Joined
- Jan 12, 2001
- Messages
- 32,040
Yes, I have a suggestion - upload a copy of the database because I think there is something there that you don't know to say and we're not remembering to ask.
Thank you for the offer of help, but actually that won't be necessary as I've figured it out. I'm completely embarrassed to even admit this, but apparently my ADD was kicking in hard today. I created tblCaseDetail in an older version of the backend that I use sometimes for development/testing purposes. I was originally working in that backend on an idea for different aspect of the database and went on to create tblCaseDetail there too without even thinking. I forgot to import the table into the "real" back-end before I linked it to the correct front-end. Obviously, I should've noticed when I linked the table that I was linking it from the wrong back-end, but apparently my mind was NOT on the job at hand when I did it. So, the record it was saying wasn't there, really wasn't there because it was looking in an older table.
I think we need a dunce cap smilie here for unbelievably asinine things like this. I certainly deserve to wear one today.
HI. I don't know if i am on the right page but my question is regarding the error message "You can't go to specified records".
I have created a 'remove duplicate query' to eliminate duplicated data entered by the user. But when a user try to enter a data which has already entered, this error message appears which at first i also not figured out immediately the problem.
Is it possible to "personalize an error message" such as this instead of "You can't go to specified record" the message can be "That data has already entered". So the user will immediately informed about the problem.
I have created a 'remove duplicate query' to eliminate duplicated data entered by the user. But when a user try to enter a data which has already entered, this error message appears which at first i also not figured out immediately the problem.
Is it possible to "personalize an error message" such as this instead of "You can't go to specified record" the message can be "That data has already entered". So the user will immediately informed about the problem.
Why is duplicate data being entered ?
Maybe you need to review your processes and forms so prior to entering data a check is done to see if it already exists.
We have 100,000's of records and have never come across duplicate data as a problem.
It is quite simple to have the data entry form to be a "search" form and if it comes up empty then it can become the data entry form either automatically or on button click.
Maybe you need to review your processes and forms so prior to entering data a check is done to see if it already exists.
We have 100,000's of records and have never come across duplicate data as a problem.
It is quite simple to have the data entry form to be a "search" form and if it comes up empty then it can become the data entry form either automatically or on button click.
