DoCmd.OpenForm WhereCondition question

[VBAjedi]

One more question for the day...

I'm using DoCmd.OpenForm in an event on form "Tasks Overview" to open form "Task Detail", and using WhereCondition to bring up the appropriate record on form "Task Detail".

DoCmd.OpenForm FormName:="Task Detail", WhereCondition:="[TaskID] = " & Me![TaskID]

No problem there, but form "Task Detail" has a subform ("Task Events") and I want to find a good way to set "Task Events" to display a specific record (based on a matching value from another control on form "Tasks Overview") before I turn the focus over to form "Task Detail".

What's the best way to go about this one? Do I need to do this in two steps (open form Task Detail, then find the appropriate record in form "Task Events")?

Thanks!

VBAjedi

 

[Remou]

There are a few possibilities, you could check for .isloaded for Task Overview, you could use OpenArgs, you could Find the record, or you could set a control on the Task Events form the the relevant ID and use that as one of the Link Master, Link Child fields for Task Events. I am generally in favour or OpenArgs and Link Fields, but it does not always suit.

http://lessthandot.com

 

[TheAceMan1]

How are ya VBAjedi . . .

Since you need to pass two values and taking into account that in a form with subform(s), the subform(s) open first ... Id use the last arguement of DoCmd.OpenForm (namely [blue]OpenArgs[/blue]) to pass the values. The values are simply concatenated and delimited with a single character. [green]Its important here to remember that OpenArgs only accepts a string.[/green]

Next we use the [blue]On Load[/blue] event of [blue]Task Detail[/blue] to manipulate the form/subform. In the [blue]On Load[/blue] event the [purple]Split[/purple] function is used to stuff the values into a variant array.

You didn't say how you intended to trigger the code so I'm assuming a button on form [blue]Tasks Overview[/blue]. I'm also assuming (as an example) that the datatype of both values are nemuric. Note ... in any code [blue]you![/blue] substitute/check proper names in [purple]purple[/purple]:

So ... in the [blue]On Click[/blue] event of the button, copy/paste the following line:

[blue]   DoCmd.OpenForm "Task Detail", , , , , , Me.[purple][b]TaskID[/b][/purple] & ";" & Me.[purple][b]2ndControlName[/b][/purple][/blue]

Next ... in the [blue]On Load[/blue] event of [blue]Task Detail[/blue], copy/paste the following:

[blue]   Dim Ary
   
   If Me.OpenArgs <> "" Then
      Ary = Split(Me.OpenArgs, ";")
      Me.Recordset.FindFirst "[[purple][b]TaskID[/b][/purple]] = " & Val(Ary(0))
      [[purple][b]Task Detail[/b][/purple]].Form.Recordset.FindFirst "[[purple][b]DetailID[/b][/purple]] = " & Val(Ary(1))
   End If[/blue]

Note the code doesn't filter ... it simply moves focus (the record pointers) to the proper records. Also ... if no values are passed thru OpenArgs the form simply opens normally.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]