Trying to Open subform on click event to current record from main form

[orographicfish via AccessMonster.com]

Hello, hoping for a little help on this one: I have a subform called 'Company
Info' in the main form. I want users to be able to click a button to open a
split form called 'Add Companies'. My current code opens to the appropriate
record, but filters the records. Any help on the proper way to open to the
current record from the 'Company Info' subform to the related record on the
'Add Companies' split form without filtering any records would be greatly
appreciated.

Private Sub EditCompanyInfo_Click()
On Error GoTo Err_EditCompanyInfo_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Add Companies"

stLinkCriteria = "[CompanyName]=" & "'" & Me![CompanyName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_EditCompanyInfo_Click:
Exit Sub

Err_EditCompanyInfo_Click:
MsgBox Err.Description
Resume Exit_EditCompanyInfo_Click

End Sub

 

[Jack Leach]

Right now you have the standard wizard boilerplate code opening the form for
you, which utilizes the use of a where condition to open the form. This
restricts all the records to only those which are returned by the condition.

To open the form with a full subset of records, remove the stLinkCriteria
code from your procedure. In fact, you can remove the stDocName code as
well, and just code the name into the DoCmd.OpenForm method, so you are left
with this:


DoCmd.OpenForm "Add Companies"


The next step is to pass the current ID to the Add Companies form, and use
that form's Open event to navigate to the record being passed to it. Here's
how to open the form with your CompanyName as an OpenArg:

DoCmd.OpenForm "Add Companies", OpenArgs:=Me![CompanyName]


Now that you have passed the CompanyName to the Add Company form, use the
Open event of the form to navigate to the record (using the forms recordset
and recordsetclone bookmark...

Private Sub Form_Open(Cancel As Integer)
If Len(Nz(Me.OpenArgs, "")) <> 0 Then
With Me.RecordsetClone
.FindFirst "[CompanyName] = """ & Me.OpenArgs & """"
If .NoMatch Then
MsgBox "Record Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub


hope this helps...


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[orographicfish via AccessMonster.com]

Jack,

This was exactly the solution I needed. Thanks, not only for resolving my
question, but also for the clear and concise explanation of HOW the changes
to the module are related to the behavior of my database objects.

Cheers,

Carolyn

Right now you have the standard wizard boilerplate code opening the form for
you, which utilizes the use of a where condition to open the form. This
restricts all the records to only those which are returned by the condition.

To open the form with a full subset of records, remove the stLinkCriteria
code from your procedure. In fact, you can remove the stDocName code as
well, and just code the name into the DoCmd.OpenForm method, so you are left
with this:

DoCmd.OpenForm "Add Companies"

The next step is to pass the current ID to the Add Companies form, and use
that form's Open event to navigate to the record being passed to it. Here's
how to open the form with your CompanyName as an OpenArg:

DoCmd.OpenForm "Add Companies", OpenArgs:=Me![CompanyName]

Now that you have passed the CompanyName to the Add Company form, use the
Open event of the form to navigate to the record (using the forms recordset
and recordsetclone bookmark...

Private Sub Form_Open(Cancel As Integer)
If Len(Nz(Me.OpenArgs, "")) <> 0 Then
With Me.RecordsetClone
.FindFirst "[CompanyName] = """ & Me.OpenArgs & """"
If .NoMatch Then
MsgBox "Record Not Found!!"
Else
Me.Bookmark = .Bookmark
End If
End With
End If
End Sub

hope this helps...

Hello, hoping for a little help on this one: I have a subform called 'Company
Info' in the main form. I want users to be able to click a button to open a

[quoted text clipped - 23 lines]

End Sub