Updating a list box within a subform

[dafyddg]

Hi,

I have a main form called AddEditClients with a subform called AddEditClients. The subform has its row source set to a table called clients. The Main form has a list box that shows a list of all the clients and when a user clicks on a client the sub for is updated to show the details of that client.

My problem lies in the subform itself. It has a list box that contains all of the contracts associated with this client. Well thats what i hoped it would do. The problem i'm having is in the WHERE clause. I can't get it to compare the ClientID for the contract record against the current ClientID in the subform.

So

SELECT Contracts.ContractID, Contracts.Description, Providers.[Provider Name], Contracts.ClientID FROM Providers INNER JOIN Contracts ON Providers.ProviderID=Contracts.ProviderID WHERE (((Contracts.ClientID)=Forms![ClientDetails]![ClientID]));

doesn't work

neither does

SELECT Contracts.ContractID, Contracts.Description, Providers.[Provider Name], Contracts.ClientID FROM Providers INNER JOIN Contracts ON Providers.ProviderID=Contracts.ProviderID WHERE (((Contracts.ClientID)=Forms![AddEditClients]![ClientDetails]![ClientID]));

How do i reference a field within a subform in SQL

Help!!!

 

[SeeThru]

Watch your quotes!

"SELECT Contracts.ContractID, Contracts.Description, Providers.[Provider Name], Contracts.ClientID FROM Providers INNER JOIN Contracts ON Providers.ProviderID=Contracts.ProviderID WHERE (((Contracts.ClientID)= " & Forms![AddEditClients]![ClientDetails]![ClientID] & &quot);"

This ensures that you get the current data from the form as it shows.

Use a pause and the debug box to get a view of exctly what the sql string is, and copy that into a blank query to view result. Keep on trying!

 

[dafyddg]

That isn't is mate.

You don't need to use " when writing querys in the query builder that what all the Forms![form name]![field] thing is for.

If i open up the sub form by itself and set the where clause to:

WHERE (((Contracts.ClientID)=Forms![ClientDetails]![ClientID]));

It works fine with no quotation marks at all.

However you did give me an idea. Instead of buildingthe query in the SQL builder i'm going to set it from code on the afterupdate of th listbox pick list.

I'll let you know how it goes

 

[dafyddg]

Well it works now although i had to have the list refresh on every OnCurrent event rather than every AfterUpdate.

Not as elegant as i'd like but until a proper way of doing it comes to light it will have to do for now.

Cheers for your help in getting me this far.