0

when double clicking on a value i need to open a form. the value gets passed to the opened form. I want the form to directly filter on that value instead of pushing on a button first. i tried filtering on change and on load but it doesn't work. when loading it doesn't know the value because it gets added after it opened the form.
this is the code for passing the value:

DoCmd.OpenForm "SubmenuRubrieken", acNormal
Forms!SubmenuRubrieken.Tv_rubrieknaam.Value = Me.Tekst14.Value

this is the code for filtering on that value in Tv_rubrieknaam:

 Dim filter As String
 filter = ""
 If Not IsNull(Tv_rubrieknaam) Then filter = filter & " AND rubrieknaam = '" & Tv_rubrieknaam.Value & "'"
 Me.filter = Right(filter, Len(filter) - 5)
 Me.FilterOn = True

for some reason it doesn't trigger the filter on changing the value of Tv_rubrieknaam. how do i need to solve this?

Appernicus
  • 434
  • 1
  • 4
  • 12

1 Answers1

0

I guess, the Form_Load() event is finished before you set the value (OpenForm is performed before value is set), so you would have to do the filtering in the OnChange() event of the Textfield or similar.

Better: pass the Me.Text14.Value with the DoCmd.OpenForm command either as a prepared whereCondition OR as OpenArgs with filtering option in the On_Load event.

A basic example:

  • I have a Form1 with a TextBox called Text0 on it. Text0 has a value of 2.
  • I have a Form2 with a Table called Table1 as Recordsource. Table1 has a column called Field1 containing numbers between 1 and 3

All I need to do is add the following code into the module of the Form1 and the moment I click on Text0 Form2 will be openend filtered down to rows with Field1 = 2

Private Sub Text0_Click()
    DoCmd.OpenForm "Form2", acFormDS, , "Field1 = " & Nz(Me!Text0, 0)
End Sub
Roland
  • 946
  • 8
  • 20
  • i did the onchange but it wouldn't trigger for some reason. i did "Tv_rubrieknaam.Value = Me.Tekst14.Value" at OpenArgs but it did nothing and it didn't change the value of Tv_rubrieknaam. Tv_rubrieknaam was empty when it opened the form. – Appernicus May 29 '14 at 13:20
  • 1
    Try passing ´" rubrieknaaam = " & Me!Text14.Value` as the whereCondition as I proposed. You can also write `DoCmd.ApplyFilter` into the On_Load event - if it doesn't trigger automatically. Btw: may I ask why you first add " AND " and then remove it? – Roland May 29 '14 at 18:20
  • If you want to add another filter you can do that by just copying the line and changing the names. And i copied it from my other filter so i just left it there. – Appernicus May 29 '14 at 19:02
  • Ah, alright. Just didn't make sense on it's own here. – Roland May 29 '14 at 19:06
  • it asks for a value for the value in tekst14 – Appernicus Jun 01 '14 at 19:06
  • i fixed it already. but thanks anyways. see my other question for my next problem with this form. http://stackoverflow.com/questions/23992226/how-to-set-a-result-of-a-sql-query-in-a-variable-in-vba – Appernicus Jun 02 '14 at 10:19