Open a form using a field as filter

[RichS]

I have a form that normally opens to show all clients. I have another form which lists invoices in a subform. I have made the invoices so that they can be selected and at the bottom I have a field that shows the currently selected client reference. I wish to be able to open the clients form with just the client whose reference was selected and have put a button under the list on the subform.

For some unknown reason, when I click on the button I am prompted to enter the client reference! The code behind the button is:

DoCmd.OpenForm "Clients", , , "[Client Ref] = Me.[Selected]"

where 'Selected' is the field containing the Client reference I want to open. Am I getting the syntax wrong or something?

 

[KenHigg]

If [Selected] is a string:

DoCmd.OpenForm "Clients", , , "[Client Ref] = '" & Me.[Selected] & "'"

If [Selected] is a number:

DoCmd.OpenForm "Clients", , , "[Client Ref] = " & Me.[Selected]

???

 

[KenHigg]

My bad:

If [Client Ref] is a string:

DoCmd.OpenForm "Clients", , , "[Client Ref] = '" & Me.[Selected] & "'"

If [Client Ref] is a number:

DoCmd.OpenForm "Clients", , , "[Client Ref] = " & Me.[Selected]

 

[RichS]

I'm afraid that doesn't work... That is now taking Me. [Selected] as being the actual value of the text I am filtering on, rather than the value held in the field called Me.[Selected]. The field is a text entry by the way...

 

[maxmangion]

Hi RichS,

It should be DoCmd.OpenForm "Clients", acNormal, , "[Client Ref] = " & Me.Selected

 

[KenHigg]

Is [Selected] on the the same form that the docmd.openform code is in? It's not on a subform is it?

 

[RichS]

Yes, the code is on the same subform as the [Selected] field.

I think I'm going to change the way I select the client, by using a dropdown box instead.

I'll keep trying various methods, but thank you all for offering advice. More helpful than Microsoft Help!

 

[KenHigg]

I would step through the code and when you get to the docmd line see what the [Selected] field value is.

 

[KenHigg]

Hi RichS,

It should be DoCmd.OpenForm "Clients", acNormal, , "[Client Ref] = " & Me.Selected

You don't have to put quotes around it?

 

[maxmangion]

Hi Ken,

That's how i always use it and i never had any problems.