combo box in a subform - how to refresh a query onclick

I need a query to refersh onclick(?) of a combobox.

I'm using a combo box to list contract numbers that are specific to clients
(client to contract is one-to-many) in a subform. The mainform and subform
our related with a field called clientid. The subform lists info that is
specific to certain contacts so I want the user to see only the contract
numbers that are specific to that client in the combobox.

To do this I set up a query with active contracts and included the clientID
field where it is equal to [forms].[mainform],[clientid]. The problem is the
query isn't refreshing when I change clients (e.g. I still see the list of
the previous clients contracts). I assume I need something in the onclick of
the combobox that will force the query to refresh. Can someone tell me what
it is?

Ian,

Normally this would be done by setting the subform's LinkMasterFields and
LinkChildFields properties to the ClientID. Access will then handle the
requerying behind the scenes when you move to a new main form record.

If your combo box is simply an unbound lookup control, you can either:

1. Remove the criteria from your subform's query, and use the combo box'
AfterUpdate event to move to the selected main form record. One way is to
use the Where clause of the OpenForm method:

DoCmd.OpenForm "YourForm", , , "[ClientID] = " & Me![YourComboBox]

Access will requery the subform as stated above.

2. Leave the criteria in the subform query and requery the subform:

Me![YourSubform].Requery

Hope that helps.
Sprinks
"Ian" wrote:

I need a query to refersh onclick(?) of a combobox.

I'm using a combo box to list contract numbers that are specific to clients
(client to contract is one-to-many) in a subform. The mainform and subform
our related with a field called clientid. The subform lists info that is
specific to certain contacts so I want the user to see only the contract
numbers that are specific to that client in the combobox.

To do this I set up a query with active contracts and included the clientID
field where it is equal to [forms].[mainform],[clientid]. The problem is the
query isn't refreshing when I change clients (e.g. I still see the list of
the previous clients contracts). I assume I need something in the onclick of
the combobox that will force the query to refresh. Can someone tell me what
it is?

Sprinks,
I created the form then linked it using the subform tool so I think the
parent and child clientID fields are appropriately linked but it's actually
another subform so I don't think it's requerying the query I need (the form
isn't based on it -- just the combobox).

Anyway -- re method #2 -- where do I put the command
Me![frm_activeservices].requery. (the main form is called frm_main). I put
it in the On open line of the subform but got an error message saying the
macro hadn't been created. Should it be attached to the combobox?

"Sprinks" wrote:

Ian,

Normally this would be done by setting the subform's LinkMasterFields and
LinkChildFields properties to the ClientID. Access will then handle the
requerying behind the scenes when you move to a new main form record.

If your combo box is simply an unbound lookup control, you can either:

1. Remove the criteria from your subform's query, and use the combo box'
AfterUpdate event to move to the selected main form record. One way is to
use the Where clause of the OpenForm method:

DoCmd.OpenForm "YourForm", , , "[ClientID] = " & Me![YourComboBox]

Access will requery the subform as stated above.

2. Leave the criteria in the subform query and requery the subform:

Me![YourSubform].Requery

Hope that helps.
Sprinks
"Ian" wrote:

I need a query to refersh onclick(?) of a combobox.

I'm using a combo box to list contract numbers that are specific to clients
(client to contract is one-to-many) in a subform. The mainform and subform
our related with a field called clientid. The subform lists info that is
specific to certain contacts so I want the user to see only the contract
numbers that are specific to that client in the combobox.

To do this I set up a query with active contracts and included the clientID
field where it is equal to [forms].[mainform],[clientid]. The problem is the
query isn't refreshing when I change clients (e.g. I still see the list of
the previous clients contracts). I assume I need something in the onclick of
the combobox that will force the query to refresh. Can someone tell me what
it is?