subform filter problem

[Sadukar]

Hi all,

Main form and Subform data is taken from the same table.
The subform is a grid used as an index for the main form in the same window.

I have a number of filters which I want to filter the subform and Main form. When I select a record in the subform, I want to go to that record in the the Main Form.

This is the code I used in the subform.
Private Sub Form_Current()
For i = 1 To 150
If Me.Form!ID = i Then
DoCmd.GoToRecord acDataForm, "LoomSpecs", acGoTo, i
Else
End If
Next
End Sub

With no filter this works perfect.

Because the subform filtered [ID] field does not match the Main form recordset it causes a crash.

Any suggestions would be appreciated.

Thanks
S.

 

[MakeItSo]

You have to at least temporarily deactivate the filter:

Dim filt as String
filt=Me.Filter
Me.FilterOn=False
...
put your code here
...
Me.Filter=filt
Me.FilterOn=True
End SUb

MakeItSo


Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[Sadukar]

My Code is for Subform_current

It wont update if I put it under the filter code.

Also The form ID field may be have a greater number than the filtered records.

For example.
If I have a filter on and the subform ID field is 40 but there are only 10 records filtered then the code will try go to record 40 that does not exist.

Is there any way I could use the recordset number as a reference rather than using a number field?

PS. Thanks for the reply

 

[MakeItSo]

Sure.

You could try with
Dim rs as Recordset
Set rs=currentdb.openrecordset _
("main form query",dbopensnapshot)
rs.findfirst "[ID]=" & Me!Id
Me.Parent.Bookmark)rs.bookmark
rs.close