Passing a Clicked Field value as a parameter to a Query

[bcournoyer]

I have a tablular form with one of the fields being value I want to use to constrain the query for the new form opened. In the Macro in the OpenForm action I can call for the value of a form field via [Forms]![formname]![fieldname]. I tried this, but doesn't seem to use the clicked field's value. What am I missing?

Here's what I am trying to do. I have a tablular form that shows a number of sales orders. I want to click on the desired sales order number and display that info in a new form using the following macro.






ORDERNO exists as a field in both tables.

[Bob Fitz]

Perhaps you need to save the record before referencing a control/field that is dirty

[Micron]

You mention "macro" but show what looks like vba and they are not the same. I can find nothing in the entire list of macro command that would allow you do do this.
So if in fact this is about code, then my guess is that the field is a lookup field in your table. You should explain what is meant by not using the clicked field value. What value does it use and what value do you expect it to use.

[bcournoyer]

Micron, I modified the post with more info.

Thx

[Micron]

Thanks, but still don't know if we're dealing with a table lookup field or what value is being returned. While I'm at it, I might as well add to the list: is the control on a subform or a form on a navigation form?

[bcournoyer]

Thanks for the questions Micron. My novice tag is showing. The form I'm selecting the field from is a form called by a button that constrains the data to a specific customer name. The form was created as a tabular form through the form wizard. Do you want to see the OpenForm macro associated with populating this form?

Clicking on the OrderNo field will open a new form referencing the same table but displaying info constrained by that order number. It instead returns data for all the order numbers.

[Welshgasman]

In 2007, there is no need for the first = ?
Is the form control called OrderNo?

Honestly start learning VBA, so much easier, neater and easier to debug.

[bcournoyer]

Yeah, I'm think VBA will be the way to go.

[Welshgasman]

Well the OpenForm macro is pretty much the same as the VBA version, but so much easier to debug.
I tried your method on a form and used a hardcoded value and a form control reference and they both worked.?

[Micron]

Maybe it would help to see the macro steps that open the form. Or you could copy, compact/repair it, zip it and post the file here. Not many db's will be under the 500kb limit for uploading files. See How to Attach files at the top of the page if you need help.