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.
Problem Adding Record to Form in Access 2007
-
- NewLounger
- Posts: 2
- Joined: 10 Feb 2012, 18:52
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Problem Adding Record to Form in Access 2007
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:
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
Best wishes,
Hans
Hans
-
- NewLounger
- Posts: 2
- Joined: 10 Feb 2012, 18:52
Re: Problem Adding Record to Form in Access 2007
Thanks so much. I tried your idea and it worked like a charm. I couldn't see the forest for the trees. :)