You cannot add or change a record...

[Trestkon]

I have a bit of a problem that's driving me absolutely nuts...so I'm hoping someone can set me straight

In my database (

http://www.offtopicproductions.com/Lawrence/public/LawrenceDB.zip),

I have a one to many relationship between tblProgramInfo and tblESPAssignmentBrief, and then a one to one relationship between tblESPAssignmentBrief and tblAssignmentDetails.

The user starts out on frmProgramInfo (which works nicely!) and then clicks the "View ESP Assignments" button at the bottom to get to a more detailed form. On that form I'm trying to set it up so that you can enter brief info about the upcoming assignments. However, whenever I attempt to enter anything in that subform, and upon attempting to exit that form, I get this error:

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

In addition, when I click the "Details" button to get to the details form and attempt to enter any data there, I receive the same error, but one table up on the tree:

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

I suppose that I'm doing something fundamentally incorrect, but I have no idea what it might be. In fact, for a while today the whole thing worked just fine, and then randomly started up with the error again. Any thoughts would be *most* appreciated!

Oh, and I realize some of the data is yet to be normalized properly (the budget stuff), I'm going to work on that after I sort out these critical errors.

 

[dhookom]

If a new form opens with the "View ESP Assignments" button there should be a foreign key field that needs to get populated with the primary key value from the frmProgramInfo. This is generally done by setting the default value of field.

If you are simply viewing a subform on the main form, you set the link master/child properties to populate the foreign key field.

Duane
Hook'D on Access
MS Access MVP

 

[Trestkon]

Ah, that makes sense. So I can set the default value of the foreign key field to inherit the primary key value of the frmProgramInfo?

 

[dhookom]

Yes. If you don't set a default, the child table won't have a clue what value to store in the Foreign Key field.

Duane
Hook'D on Access
MS Access MVP

 

[Trestkon]

That worked splendidly, thanks!

My follow up question is, how do I specify the default value when it's coming form a continuous form? The frmAssignmentSubform is a continous form with command buttons for each record that open a new form (frmAssignmentDetails). In order to sync it all up I'm going to have to pass the AssignmentID from the record where the button was pressed into the AssignmentDetails form.

I imagine it's just a bit of code, I'm poking around looking for it but hadn't had any luck just yet.

 

[Trestkon]

Got it! I just needed to call the subform in the proper order, thusly:

=[Forms]![frmAssignments]![frmAssignmentSubform]![AssignmentID]

Please let me know if that is, for some reason, stupid.

I really appreciate all the help, it greatly reduces angry monitor glaring

 

[Trestkon]

Hrm, it turns out I'm still getting the original error, even though the values seem to be getting assigned just fine. I'll tinker with it some more and upload it again if I can't bend it into shape in the next few hours.

 

[Trestkon]

Correction, I only get the error when attempting to add records to the AssignmentDetails form:

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

Someone please let me know if it's considered annoying to continuously update a thread like this, normally I'd just edit my last post but that doesn't seem to be an option.

 

[Trestkon]

Triumphant update!

I realized that the problem was probably because the foreign key wasn't inheriting the value of the primary key properly. It turns out that the reason for that was the values in the frmAssignmentSubform weren't actually being written to the record before the button was pressed. The solution seems to be adding a Me.Refresh as the first thing in the On_Click function for the button.

Woo!

 

[dhookom]

I think most of us use Me.Dirty = False to save the current record. If Me.Refresh works for you then use it.

Duane
Hook'D on Access
MS Access MVP

 

[Trestkon]

Discovered another little issue. When I open the database fresh and the main Capital Program form comes up, it doesn't have any entries and clicking on anything in the pick list triggers an error message in the code because it could not find those records. If I then change to Design view and back to form view, everything works fine.

I suspect it's not loading the recordset properly when the form first opens, so that my code can't grab the info it needs for the pick list. The code I have for my picklist is thusly:

Private Sub SiteNumNavBox_AfterUpdate()
Dim rst As DAO.Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "SiteID=" & SiteNumNavBox.Column(0) & ""

If rst.NoMatch Then
MsgBox "Could not find the record for some random reason, better consult Lawrence. Or throw your computer out the window, which is a rather nice solution for stress relief.", vbInformation
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
End Sub

 

[Trestkon]

Hmm, I should add that this only started happening after I set the Switchboard to be my main form, so that they Capital Program form is opened from a Switchboard button. Everything seems to work fine if I set the Capital Program form to be the one that opens automatically upon opening the database, but I'd like to use the switchboard.

 

[Trestkon]

Got it! The switchboard was opening it in Add mode, and it needed to be in Edit mode.

This thread is becoming somewhat of a chronical of my bumblings, sorry about that.