Requery Subform from a different form?

[Bryan Hughes]

Hello

I have a form for adding new clients. The form has several subforms.
Several clients can belong to a single case file, so some information is
entered once for the case file, which is shown in the subform, after it is
entered. To enter this information, I have a quick data entry form pop up.

Once the data is entered and saved, How can I requery the subform to show
the new data entered?

I have tried several things but so far no success.

Here is what I have been working with:

'************************************
'Save and Close
Private Sub cmdSave_Click()
On Error GoTo ErrorHandler
Dim frm As Access.Form

If Me.Dirty = True Then
DoCmd.RunCommand acCmdSaveRecord
End If

If CurrentProject.AllForms("frmNew_Client_Details").IsLoaded = True Then
Set frm =
Forms![frmNew_Client_Details]![fsubClient_Primary_Contact].Form

'Requery Primary Contact subform
strSearch = "[CFID] = " & Chr$(39) & _
Me![frmNew_Client_Details]![fsubClient_Primary_Contact].Form &
Chr$(39)

frm.Requery
frm.RecordsetClone.FindFirst strSearch
If frm.NoMatch Then
Debug.Print "No match Found"
Else
frm.Bookmark = frm.RecordsetClone.Bookmark
End If
End If
DoCmd.Close acForm, "fdqPrimary_Contact", acSaveNo
Exit Sub
ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Please help

-Bryan

 

[Allen Browne]

Suggestions:

1. Is there any chance that
Forms![frmNew_Client_Details]![fsubClient_Primary_Contact].Form
could be dirty (so the requery would fail)?

2. Has strSearch declared? (Option Explicit is a good idea.)

3. strSearch is being assigned a fieldname equal to a form-object in
quotes??? Should there be a text box name in this line:
strSearch = "[CFID] = " & Chr$(39) & _
Me![frmNew_Client_Details]![fsubClient_Primary_Contact].Form & Chr$(39)

4. Does the popup form really have the same subforms as the original form?

5. Did you intend to test the NoMatch of frm's RecordsetClone, rather than
the NoMatch of the form?
With frm.RecordsetClone
.FindFirst strSearch
If .NoMatch Then
...
Else
frm.Bookmark = .Bookmark
End If
End With

 

[Bryan Hughes]

Hi Allen,

I was able to get it work after I posted. Almost everything you mention was
the problem. Here is what I did to make it work. I was not saving current
record on main form before opening the quick data form. I did not have a
textbox in the strSearch.

Private Sub cmdSave_Click()
On Error GoTo ErrorHandler
Dim frm As Access.Form
Dim rst1 As DAO.Recordset
Dim strSearch As String

If Me.Dirty = True Then
DoCmd.RunCommand acCmdSaveRecord
End If

strSearch = "[CFID] =" & Chr$(39) & _
Me![txtCFID] & Chr$(39)

Set frm = Forms!frmNew_Client_Details!fsubClient_Primary_Contact.Form
frm.Requery
Set rst1 = frm.RecordsetClone
rst1.FindFirst strSearch
If rst1.NoMatch Then
Forms!frmNew_Client_Details!fsubClient_Primary_Contact.Visible =
False
Forms!frmNew_Client_Details!fsubClient_Primary_Contact.Locked =
True
Forms!frmNew_Client_Details.cmdAddPrimaryContact.Visible = True
Forms!frmNew_Client_Details.cmdAddPrimaryContact.Enabled = True
Else

Forms!frmNew_Client_Details!fsubClient_Primary_Contact.Form.Bookmark =
rst1.Bookmark
Forms!frmNew_Client_Details!fsubClient_Primary_Contact.Visible =
True
Forms!frmNew_Client_Details!fsubClient_Primary_Contact.Locked =
False
Forms!frmNew_Client_Details.cmdPage4.SetFocus
Forms!frmNew_Client_Details.cmdAddPrimaryContact.Visible = False
Forms!frmNew_Client_Details.cmdAddPrimaryContact.Enabled = False
End If
rst1.Close
frm.Refresh
DoCmd.Close acForm, "fdqPrimary_Contact", acSaveNo
Exit Sub
ErrorHandlerExit:
Exit Sub

ErrorHandler:
MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
Resume ErrorHandlerExit
End Sub

Thank you for the help.