Combo Box Filter

[Chuck]

I have created a service form. The user is requested to chose the client's
account number for the service requested. The service information is in the
"Service Details" table and the account information is in the "Accounts"
table. The Row Source of the Combo Box that I created in the service form
cannot be filterd via the same type of query that I have used in previous
versions of Access. (I am using 2007 now). Is there a new property in Access
2007 that I need to deal with? If not what is the reason I can't limit the
records in the Combo Box using a query to filter the records?

Thank you in advance,
Chuck

 

[Arvin Meyer [MVP]]

You should be able to use either a saved query or a SQL statement as a Row
Source. You may be using a reserved word which 2007 won't allow, or a
function which needs permission to run, Is your database in a Trusted
Location, or have you set the security level to low?

 

[Chuck]

I used the following SQL Statement:

SELECT [Services Offered].ID, [Services Offered].Service, [Services
Offered].Classification, [Services Offered].Service, [Service Pricing].Client
FROM [Services Offered] INNER JOIN [Service Pricing] ON [Services
Offered].ID=[Service Pricing].Service WHERE ((([Service
Pricing].Client)=[Forms]![Service Details]![Client])) ORDER BY [Services
Offered].Classification, [Services Offered].Service;

I have used this same technique in previous versions and never had a
problem. I did go in and add the location of this database to a trusted
location, but there is still no change in the behavior of the database.
Another oddity is that the filter works for the first record, but shows the
same results for all other records. In other words, the combo box filters
out the options displayed by the combo box, but the options continue to be
the same even on other records where the client is different which should
cause the combo list to be different.