Subform CurrentRecord Filter

[kculpepper77]

I have a Form with 2 subforms within it- both bound by same table. I would like the user to browse a more expanded list in one subform, and select a line(record), and then have that particular record filtered in the other subform; and this is where the user will make any changes.

So how can I filter the later subform?

Appreciate any help. Thanks.

 

[willir]

kculpepper77

Is the one subform contineous and the other suform single?

The way I handle record selection in a contineous form is to add a small command button that opens up a form and finds the specific record.

You could use a similar approach for you second subform - same idea, but different coding.

When you select a record in the contineous form, the current data set is available to you.

For the On Current event for the first subform you could pass on the primary key for the cuurent record to the second subform, and reference the Filter and FilterOn properties.

On Current record property...

Dim MyKey as Long 'Assuming nuermic for this example

If NZ(Me.MySubFrmPrimaryKey, 0) > 0 Then
   MyKey = Me.MySubFrmPrimaryKey
   forms!YourSecondSubForm!Filter = "[MySubFrmPrimaryKey] = " & MyKey
   forms!YourSecondSubForm!FilterOn = True
   'You can also set focus - commented out
   'forms!YourSecondSubForm!SetFocus
End If

For a string primary key

Dim MyKey as String, strQ as String

If Len(NZ(Me.MySubFrmPrimaryKey, "")) > 0 Then
   MyKey = Me.MySubFrmPrimaryKey
   forms!YourSecondSubForm!Filter = "[MySubFrmPrimaryKey] = " & strQ & MyKey &strQ
   forms!YourSecondSubForm!FilterOn = True
   'You can also set focus - commented out
   'forms!YourSecondSubForm!SetFocus
End If

You will have to tweak the form reference.

Richard

 

[kculpepper77]

Works Great- Thanks much-