In my FE-BE database, I have a form named frmMain with record source a table named “Projects”. The key field for “Projects” is ProjectID. On frmMain there are a number of tabbed pages. One of these pages is named “Environmental Document Review”. On this page there is a subform named sfrmEnvRvwCmnts with record source a table named tblEnvDoc. The key field for tblEnvDoc is tblEnvDocID. ProjectID is also in this table. Subform sfrmEnvRvwCmnts is in single form mode and it has multiple records per ProjectID. On this subform, there is a command button titled OpenCommentRespMatrix. The “On Click” event procedure for this button opens the form frmCommentRespMatrix which is in the continuous form mode and it has multiple records per tblEnvDocID. The record source for this form is a table named tblSubstComments with key field named SubstantiveID. tblEnvDocID is also in this table. The code for the On-Click event procedure is shown below.
Private Sub OpenCommentRespMatrix_Click()
On Error GoTo Err_OpenCommentRespMatrix_Click
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.RunCommand acCmdSaveRecord
stDocName = "frmCommentRespMatrix"
stLinkCriteria = "[tblEnvDocID]=" & Me![tblEnvDocID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_OpenCommentRespMatrix_Click:
Exit Sub
Err_OpenCommentRespMatrix_Click:
MsgBox Err.Description
Resume Exit_OpenCommentRespMatrix_Click
End Sub
When I enter data on the form frmCommentRespMatrix, the system will add a record to the table tblSubstComments. However, when I close the form and then open it back up, the data I typed no longer shows on the form. I checked and all the data I enter is being stored in the table tblSubstComments, except the system isn't automatically loading tblEnvDocID. That's why when I close and reopen the form frmCommentRespMatrix, the data doesn't reappear. However, if I open tblSubstComments and type in the number for tblEnvDocID, then the record does indeed reappear when I reopen frmCommentRespMatrix. I have yet to figure out how to make the system automatically load the number for tblEnvDocID.
Private Sub OpenCommentRespMatrix_Click()
On Error GoTo Err_OpenCommentRespMatrix_Click
Dim stDocName As String
Dim stLinkCriteria As String
DoCmd.RunCommand acCmdSaveRecord
stDocName = "frmCommentRespMatrix"
stLinkCriteria = "[tblEnvDocID]=" & Me![tblEnvDocID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_OpenCommentRespMatrix_Click:
Exit Sub
Err_OpenCommentRespMatrix_Click:
MsgBox Err.Description
Resume Exit_OpenCommentRespMatrix_Click
End Sub
When I enter data on the form frmCommentRespMatrix, the system will add a record to the table tblSubstComments. However, when I close the form and then open it back up, the data I typed no longer shows on the form. I checked and all the data I enter is being stored in the table tblSubstComments, except the system isn't automatically loading tblEnvDocID. That's why when I close and reopen the form frmCommentRespMatrix, the data doesn't reappear. However, if I open tblSubstComments and type in the number for tblEnvDocID, then the record does indeed reappear when I reopen frmCommentRespMatrix. I have yet to figure out how to make the system automatically load the number for tblEnvDocID.
