Problem Adding Record to Form in Access 2007

[pclutts]

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.

[HansV]

Welcome to Eileen's Lounge!

The lines

stDocName = "frmCommentRespMatrix"
stLinkCriteria = "[tblEnvDocID]=" & Me![tblEnvDocID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

will open frmCommentRespMatrix with only records whose tblEnvDocID is the same as that in the current record in sfrmEnvRvwCmnts. But it won't automatically set the value of tblEnvDocID for new records. To do that, you need to pass the value to the form in the OpenArgs argument, and use that in the On Load (or On Open) event.

Change the line that opens the form to

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me![tblEnvDocID]

and create an On Load event procedure for frmCommentRespMatrix:

Code: Select all

Private Sub Form_Load()
    If Not IsNull(Me.OpenArgs) Then
        Me.tblEnvDocID.DefaultValue = Me.OpenArgs
    End If
End Sub

[pclutts]

Thanks so much. I tried your idea and it worked like a charm. I couldn't see the forest for the trees. :)