Open form to a specific record from a query

[chris.williams]

Hi - I am attempting to provide a list of records via sub-form and query - with the ability to click on one record, and open another form filtered to the record selected.



I am using the OpenForm in a macro associated with an action, and the OpenForm Parameters are as follows:

Form Name: frm_FTSO
Filter Name:
Where Condition= : = "[TaskSR=" & Nz([TaskSR],0)
Data Mode: Edit
Window Mode: Dialog

The same settings on another form/query pair work fine. the problem with this one is that when the action is taken, I get an Enter Parameter Value message box requesting that I enter the search criteria for the value contained in the [TaskSR] field referenced above.

If I enter the proper data in the message box, the new form is open to the record being requested.

How do I fix or what do I look for to pass the value of [TaskSR] for the record I selected, to the where condition so that the parameter value box does not appear?

Thanks...

[boblarson]

You are missing a bracket:

Where Condition= : = "[TaskSR]=" & Nz([TaskSR],0)

And I'm not sure you really need the equals sign before it:

Where Condition: "[TaskSR]=" & Nz([TaskSR],0)

[chris.williams]

I still get the EPV popup. Now instead of the data contained in [TaskSR], the EPV states "TaskSR=*

[chris.williams]

Oh - of note, the same syntax that I originally posted works on another form/query set. I've looked through the Db looking for a difference and it's all looks good from my end.

[chris.williams]

OK - I removed the = sign and now it passes the TaskSR. I've encountered another problem, where the form reports the filter is on, but it is not.

[boblarson]

OK - I removed the = sign and now it passes the TaskSR. I've encountered another problem, where the form reports the filter is on, but it is not.

Did you fix the rest of it too? The missing bracket was supposed to be there in the correct spot (and originally I put it in the wrong place).

[chris.williams]

I am using this : "[TaskSR]=" & Nz([TaskSR],0)

It opens the form to the proper SR, but all records are navigable with the filter on.

[boblarson]

Not sure what would be causing that but there are a few form properties in 2010 that are different from my 2003 stuff I have here at work. I have 2010 at home but I'm not able to get to it at the moment.

I would try using VBA instead of the macro -


DoCmd.OpenForm "frm_FTSO", acNormal, DataMode:=acFormEdit, WhereCondition:="[TaskSR]=" & Nz(Me![TaskSR],0), WindowMode:=acDialog

[chris.williams]

This didn't work either. I'd be interested in knowing how to verify if a selection is being passed through to a where statement - it seems that is a possible place to look.

[boblarson]

Well, using VBA you can just put this in the event which the open form command is on:

Debug.Print Me![TaskSR]

to see what the value is in the Immediate Window.

[chris.williams]

Hi - thank you for all of your help, your quick response is unparalleled.

Through your suggestions and continued digging, I resolved the problem by creating a temp var based on Nz and created a query which filtered on the temp var.

I then used OpenForm with the query specified in the Filter field.

Again, thank you for your quick responses and helpful suggestions.

[boblarson]

Glad you came up with a solution.