Problem with linked forms

[Guest]

I used the wizard to link two forms by means of a command button
(cmdOpenSubs) on one form (frmAgents) that opens a second form
(frmSubclients) filtered to display the records that relate to a control
value on the currently displayed first form.

So when the value for the field (PrmClntID) in the relevant control on the
first form is the value for "Agent X", the second form opens filtered for
records with "Agent X".

This works, except that if I add new records in the filtered second form
then the form is no longer specific to the “Agent X†value. I get the
existing records for "Agent X", but if I enter a new records the filter falls
away and I have to re-enter the value for "Agent X" each time.

Same problem occurs if I use a form and subform, though linked forms are
more suitable here.

Here is the code the wizard generated for the command button. I know very
little about VBA, but would it be possible to tweak this code to get around
the problem?

Private Sub cmbOpenSubs_Click()
On Error GoTo Err_cmbOpenSubs_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSubclients"

stLinkCriteria = "[PrmClntID]=" & Me![txtPrmCntID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmbOpenSubs_Click:
Exit Sub

Err_cmbOpenSubs_Click:
MsgBox Err.Description
Resume Exit_cmbOpenSubs_Click

End Sub

 

[Duane Hookom]

Using a form and subform with the Link Master/Child properties should always
default the foreign key field in the subform to the Primary key value on the
main form.

You can add to your code to set the default value of PrmClntID. I prefer to
send and use OpenArgs in the form but this method might work if you have a
control on frmSubclients named txtPrmClnID bound to PrmClntID.

Private Sub cmbOpenSubs_Click()
On Error GoTo Err_cmbOpenSubs_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSubclients"

stLinkCriteria = "[PrmClntID]=" & Me![txtPrmCntID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!frmSubClients.txtPrmClnID.DefaultValue = Me![txtPrmCntID]
Exit_cmbOpenSubs_Click:
Exit Sub

Err_cmbOpenSubs_Click:
MsgBox Err.Description
Resume Exit_cmbOpenSubs_Click

End Sub

 

[Guest]

Thanks for the suggestion, but it hasn't worked. The only difference is that
when the second form opens from the command button I also get an error
message saying "Application-defined or object-defined error." I click on OK
on the error message and it goes away, then the second form opens and works
just as before. Still doesn't preserve the default value for the PK of the
first form for new records.

I'm afraid I don't know enough about VBA to follow your comment about
preferring to send and use OpenArgs on the form.

 

[Duane Hookom]

You would open the form with similar code:
Private Sub cmbOpenSubs_Click()
On Error GoTo Err_cmbOpenSubs_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSubclients"

stLinkCriteria = "[PrmClntID]=" & Me![txtPrmCntID]
DoCmd.OpenForm stDocName, , , stLinkCriteria,,,Me!txtPrmCntID

Exit_cmbOpenSubs_Click:
Exit Sub

Err_cmbOpenSubs_Click:
MsgBox Err.Description
Resume Exit_cmbOpenSubs_Click

End Sub

Then in frmSubclients, use code in the On Open like:

If Len(Me.OpenArg & "") >0 Then
Me.txtPrmCntlID.DefaultValue = Me.OpenArgs
End If

 

[Guest]

Your first bit of code is OK, but the second:

Then in frmSubclients, use code in the On Open like:

If Len(Me.OpenArg & "") >0 Then
Me.txtPrmCntlID.DefaultValue = Me.OpenArgs
End If

doesn't work. The error message I get is
"Compile error: Method or data member not found" and ".OpenArg" is
highlighted in the vba window.

 

[Duane Hookom]

You didn't fix my typo ;-)

If Len(Me.OpenArgs & "") >0 Then
Me.txtPrmCntlID.DefaultValue = Me.OpenArgs
End If

 

[Guest]

Thanks very much Duane.

Looking long and hard at your code (I'm on a steep learning curve here) I
did eventually figure that there might be a typo so I tried fixing it, only
to find that the debug then picked up other things (so at least I knew I'd
got it right fixing your typo!). I gave up for a while, then came back and
discovered, looking really hard at the screen, that I was introducing new
typos of my own. And now at last it's all working just as it should. So
that's good, and the extremely useful little lesson in coding made it a
double bonus!

 

[Duane Hookom]

Glad to hear you got it working and learned about code.