Creating a button to navigate from one form to another, restricting records displayed

[emrodge]

I'm using Access 2007 on Windows Vista and am having some problems trying to configure a simple button. There are two tables called Clients and Contacts which are linked on clients.id and contacts.client_id. There are two forms associated with each table and I am trying to create a button to navigate from the Clients form to the Contacts form (which is a split form) but only to display the contacts where the current clients.id = contacts.client_id (eventually I would like the entry in the top of the form to be the contact with the earliest letter in the alphabet as their surname).
I turned on the Control Wizard and created the button specifying Form Operations ->Open form ->Contacts->Open the form and find specific data to display. At this stage there are no fields listed under the Clients table so I can't use the <-> button to link the fields. I have therefore tried to set up an On Click events procedure using DoCmd.OpenForm. I am not a programmer so have tried copying code from the Help and from forums such as this. My current code reads as follows (although I have tried many variations):

Private Sub Display_Contacts_for_Client_Click()
DoCmd.OpenForm "contacts", , , _
"id = forms![contacts]![client_id]"
End Sub

Upon clicking the button the Contacts form is opened but with no records displaying. Other variations on the syntax either result in run time errors, or the Contacts form being opened with all of the records displaying at the bottom of the screen.
Any help much appreciated. I have set the properties for the MSACCESS.exe to run the program as administrator as I've read that this should be set for Access 2007.
Thanks, Emma

[Stewart Ross]

Hi. You will need to refer to the value of the field client_id, not its name, within the Where string. If the client_id field is a number use the first version below, and if a string use the second version:

"id = " & forms![contacts]![client_id]

or

"id = '" & forms![contacts]![client_id] & "'"

Welcome to Bytes!

-Stewart

[emrodge]

Hi Stewart, thanks for your welcome & your help...I'm tearing my hair out with this - a seemingly straightforward database configuration project is fast turning into a time consuming, frustrating nightmare!
The client id field is numeric so I tried your first example of code but when I click on the button I get run time error 2450 and a message saying the Contacts form can't be found - I promise you it does exist!
Any further suggestions gratefully received!

[Stewart Ross]

Ah, I should have spotted this earlier - you appear to be trying to open form Contacts with a WHERE clause based on a control value on the same form!! That simply cannot work, and indeed explains the error message you last received, as the control concerned cannot be referenced until AFTER the form is opened - which OpenForm cannot do because of the reference to the form's ClientID control which is not yet available (until the form is open). Do you see the circular reference?

I presume you have a client ID field somewhere on the form from which you are trying to open form Contacts. If you do, you can refer to that instead, substituting the actual name of your client ID control in the expression below:

"[ID] = " & me![your client ID control name]

-Stewart

[emrodge]

Ah, I see what you mean. I was presuming that because I had created a relationship between the two tables (linked on clients.id and contacts.client_id) that the condition would work. I have now added an invisible field for contacts.client_id to the Clients form and amended the code to read:

DoCmd.OpenForm! "Contacts", , , " ID = " & Me![Client_id]

When I click on the button now it does open the Contacts form but it is blank.
Maybe I am going about this in totally the wrong way!
Thanks for your patience.
Emma

[Stewart Ross]

It appears to me that you are thinking of the relationship the wrong way round. If you are trying to open the Contacts form then it is the ID field underlying the Contacts table that you need to include in the WHERE clause, and the Clients ID field value that you need to supply to it to match from one to the other. This is the reverse of what you appear to be doing.

If I can try to clarify, the idea is that you

OPEN the contact form WHERE
the primary key field from the contact form's recordsource table 
is equal in value to
the primary key field from the client form's recordsource table

This is more likely to be something like

DoCmd.OpenForm "Contacts" ,,, "[Client_ID] = " & Me![ID]

if I am correctly understanding which key is which...

-Stewart

[emrodge]

Stewart - you have saved the day! Thank you so much, I really appreciate your help & patience. Now I'll have a go at configuring a button to do the reverse from the Contacts form back to Clients.
Emma