How to reference a value in a table with a query's criteria

[loopa1]

Hi all
I have a query which includes criteria for a field. Until now this criteria has remained static, there's never been a need for it to be anything else, however, I now what to extend it so that it is based upon a value within a table which is changeable by the user.

The table, called PartnershipSelector, contains one record and field only. The field is called CurrentPartnership and the field value (for the only record in the table) is updated whenever the user selects which partnership they want to report on using a combo box.

So how do I reference this table/field/value in an access query?

I though it would be something like [PartnershipSelector]![CurrentPartnership], but access justs prompts me for this value instead of returning it, so I know I have it wrong.

Thanks.

 

[Trevor G]

On a form create a combo that points to the table and field then set the query criteria to use the combo on the form, remember to name the combo in the properties something like cboSelectName, save the form again something like frmSelectList, then add a command button to open the query.

 

[loopa1]

Thanks Trevor, although I still don't know how to reference the value stored in the combo box.

In the criteria row in my query, I have placed this for the field I want to filter..

[Forms]![frmSelect]![cboSelectName]

Where am I going wrong?

 

[Trevor G]

You don't seem to have gone wrong, have you added the command button to open the query?

So you have opened the form, then selected the combo box and made your choice then clicked the button to open the query !

 

[loopa1]

Ah...I know why it was still prompting me for the value, I didn't have frmSelect open.

How can I use this value without having the form open? I think this is why I first thought I'd need to reference the value stored in the table.

 

[Trevor G]

You can use code (do you know vba?) or point to the table in the criteria, but you would need to have the table open then same as selecting from the form.

 

[loopa1]

I know a little vba.

I've added the table to my query designed, joined the two fields together, removed the criteria and this seems to work.

I suppose this way achieves what I want given that it's only showing those records where the partnership = the value in my joined table.

Don't know why I didn't think of that sooner!

 

[Trevor G]

Pleased to read you have found a solution