launching form with filter from other form

[iankerry]

Hi

I am probably missing something obvious perhaps someone can help?

I have a form for dvd's. On it i have a barcode field, and when i scan a dvd it enters the ID number of the film and goes and finds the record. here is the code for it:

Private Sub Text107_AfterUpdate()
If Text107 <> "" Then

Me.RecordsetClone.FindFirst "dbo_films.[id] = " & DLookup("tblfilms_id", "dbo_filmcopies", "ID = " & Text107.Value)
Me.Bookmark = Me.RecordsetClone.Bookmark
[frmFilmSub - Alternative].Form.FilterOn = True
[frmFilmSub - Alternative].Form.Filter = "id = " & Text107.Value
[frmFilmSub - Alternative].Form.Requery
End If
End Sub

This works well. But what i would like to do is have a barcode search box on another form, that loads another form with the correct dvd showing. so i thought i could use the same code, tweak it and hey presto, but no.

Private Sub Text107_AfterUpdate()
If Text107 <> "" Then
DoCmd.OpenForm "frmFilmSub - Alternative"
Me.RecordsetClone.FindFirst "dbo_films.[id] = " & DLookup("tblfilms_id", "dbo_filmcopies", "ID = " & Text107.Value)
Me.Bookmark = Me.RecordsetClone.Bookmark
[frmFilmSub - Alternative].Form.FilterOn = True
[frmFilmSub - Alternative].Form.Filter = "id = " & Text107.Value
[frmFilmSub - Alternative].Form.Requery
End If
End Sub

All I have changed is really opening the form. The code falls down because it can't find the dbo_films.id field (r/t error 3070). Which of course is on the form, as i use it in the other routine.



have i not referenced the new form correctly?

thanks
ian

[RuralGuy]

Are you aware of the fact that the WhereCondition argument of the OpenForm function applies a filter to the target form?

[iankerry]

Are you aware of the fact that the WhereCondition argument of the OpenForm function applies a filter to the target form?

I was but didnt think of it - thanks.

replacing all that code with

DoCmd.OpenForm "frmFilmSub - Alternative", acNormal, , "[ID] =" & Text107.Value

works a treat!

Thanks

[RuralGuy]

You're welcome. It was the old forest hiding behind the trees bit.

[iankerry]

it's funny how often i get lost in lost in those trees!