Command button to Linked Form not working

[zack]

Being fairly new to database design, I would be grateful for any advice
or help.

I want the result of 2 queries to appear in a form, however because of
the large amount of information that needs to be viewed I have used
Access's wizard to produce a main form with a linked subform. This
form also produces a command button in the header that toggles to the
subform when you run the query.

The issue is that this command button doesn't always work. When I
first open the application I am prompted by a parameter query to enter
a course leaflet number.
This opens the main form with all the relevant text boxes populated.
However when I click on the command button to the subform, nothing
happens.

If I swop to design view and go into the properties of the button and
simply look at the code behind the on click event, and then go back to
form view the button now works correctly.

It continues to work until I exit the application and re-open it the
problem starts again.

Below is the code that the wizrd produced when creating the linked
form.
Sub ToggleLink_Click()
On Error GoTo ToggleLink_Click_Err

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

ToggleLink_Click_Exit:
Exit Sub

ToggleLink_Click_Err:
MsgBox Error$
Resume ToggleLink_Click_Exit

End Sub
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![frmExtraInfoSub].DataEntry = True
Else
Forms![frmExtraInfoSub].Filter = "[LeafletID] = " &
Me![LEAFLET]
Forms![frmExtraInfoSub].FilterOn = True
End If

End Sub
Private Sub OpenChildForm()

DoCmd.OpenForm "frmExtraInfoSub"
If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I know very little about VB so this doesn't mean much to me yet!!!!
Any help or advice as usual would be greatly appreciated.
Zack

 

[Guest]

Firstly lets get the terminology correct. What you have is a linked form not
a subform. A subform is one embedded in a parent form, not a separate one
linked to the parent form.

Having got that out of the way you can actually do this a more simply than
the way the wizard does it. Firstly make sure it’s a command button, not a
toggle button you use; from the code I think you might have the latter. In
the Click event procedure of the button put this:

Dim strForm as String, strCriteria As String

strForm = "frmExtraInfoSub"
strCriteria = "LeafletID = " & Nz(Me!Leaflet,0)

' open linked form filtered to current LeafletID
DoCmd.OpenForm strForm, WhereCondition:=strCriteria

It doesn't matter in fact if the second form is open when you click the
button; if its already open it will be filtered to the relevant record(s).

To keep the two forms in sync put the following in the first form's Current
event procedure to filter the second form to the current form's LeafletID if
the user navigates to another record while the second form is open:

' filter frmExtraInfoSub form to current LeafletID
' ignoring error if not open
On Error Resume Next
Forms! frmExtraInfoSub.Filter = "LeafletID = " & Nz(Me!Leaflet,0)

You could, however, do this in a single form by using a tab control, putting
the main form's controls on the first page of the tab control and the
frmExtraInfoSub form as a true subform on the second page. The
LinkMasterFields property for the subform control would be Leaflet and its
LinkChildFields property would be LeafletID. A subform control is the
control in the parent form which contains the subform. With this setup you
wouldn't need any code whatsoever.

Being fairly new to database design, I would be grateful for any advice
or help.

I want the result of 2 queries to appear in a form, however because of
the large amount of information that needs to be viewed I have used
Access's wizard to produce a main form with a linked subform. This
form also produces a command button in the header that toggles to the
subform when you run the query.

The issue is that this command button doesn't always work. When I
first open the application I am prompted by a parameter query to enter
a course leaflet number.
This opens the main form with all the relevant text boxes populated.
However when I click on the command button to the subform, nothing
happens.

If I swop to design view and go into the properties of the button and
simply look at the code behind the on click event, and then go back to
form view the button now works correctly.

It continues to work until I exit the application and re-open it the
problem starts again.

Below is the code that the wizrd produced when creating the linked
form.
Sub ToggleLink_Click()
On Error GoTo ToggleLink_Click_Err

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

ToggleLink_Click_Exit:
Exit Sub

ToggleLink_Click_Err:
MsgBox Error$
Resume ToggleLink_Click_Exit

End Sub
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![frmExtraInfoSub].DataEntry = True
Else
Forms![frmExtraInfoSub].Filter = "[LeafletID] = " &
Me![LEAFLET]
Forms![frmExtraInfoSub].FilterOn = True
End If

End Sub
Private Sub OpenChildForm()

DoCmd.OpenForm "frmExtraInfoSub"
If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I know very little about VB so this doesn't mean much to me yet!!!!
Any help or advice as usual would be greatly appreciated.
Zack

 

[zack]

Thanks for this, being new to VBA your bit of code worked a treat.
Many thanks
Zack

Firstly lets get the terminology correct. What you have is a linked form not
a subform. A subform is one embedded in a parent form, not a separate one
linked to the parent form.

Having got that out of the way you can actually do this a more simply than
the way the wizard does it. Firstly make sure it's a command button, not a
toggle button you use; from the code I think you might have the latter. In
the Click event procedure of the button put this:

Dim strForm as String, strCriteria As String

strForm = "frmExtraInfoSub"
strCriteria = "LeafletID = " & Nz(Me!Leaflet,0)

' open linked form filtered to current LeafletID
DoCmd.OpenForm strForm, WhereCondition:=strCriteria

It doesn't matter in fact if the second form is open when you click the
button; if its already open it will be filtered to the relevant record(s).

To keep the two forms in sync put the following in the first form's Current
event procedure to filter the second form to the current form's LeafletID if
the user navigates to another record while the second form is open:

' filter frmExtraInfoSub form to current LeafletID
' ignoring error if not open
On Error Resume Next
Forms! frmExtraInfoSub.Filter = "LeafletID = " & Nz(Me!Leaflet,0)

You could, however, do this in a single form by using a tab control, putting
the main form's controls on the first page of the tab control and the
frmExtraInfoSub form as a true subform on the second page. The
LinkMasterFields property for the subform control would be Leaflet and its
LinkChildFields property would be LeafletID. A subform control is the
control in the parent form which contains the subform. With this setup you
wouldn't need any code whatsoever.

Being fairly new to database design, I would be grateful for any advice
or help.

I want the result of 2 queries to appear in a form, however because of
the large amount of information that needs to be viewed I have used
Access's wizard to produce a main form with a linked subform. This
form also produces a command button in the header that toggles to the
subform when you run the query.

The issue is that this command button doesn't always work. When I
first open the application I am prompted by a parameter query to enter
a course leaflet number.
This opens the main form with all the relevant text boxes populated.
However when I click on the command button to the subform, nothing
happens.

If I swop to design view and go into the properties of the button and
simply look at the code behind the on click event, and then go back to
form view the button now works correctly.

It continues to work until I exit the application and re-open it the
problem starts again.

Below is the code that the wizrd produced when creating the linked
form.
Sub ToggleLink_Click()
On Error GoTo ToggleLink_Click_Err

If ChildFormIsOpen() Then
CloseChildForm
Else
OpenChildForm
FilterChildForm
End If

ToggleLink_Click_Exit:
Exit Sub

ToggleLink_Click_Err:
MsgBox Error$
Resume ToggleLink_Click_Exit

End Sub
Private Sub FilterChildForm()

If Me.NewRecord Then
Forms![frmExtraInfoSub].DataEntry = True
Else
Forms![frmExtraInfoSub].Filter = "[LeafletID] = " &
Me![LEAFLET]
Forms![frmExtraInfoSub].FilterOn = True
End If

End Sub
Private Sub OpenChildForm()

DoCmd.OpenForm "frmExtraInfoSub"
If Not Me![ToggleLink] Then Me![ToggleLink] = True

End Sub

I know very little about VB so this doesn't mean much to me yet!!!!
Any help or advice as usual would be greatly appreciated.
Zack