Dlookup with DoCmd.OpenForm (1 Viewer)

I am trying to open up my frmMain with only active clients. I do have a control [Class} which has among several options, "Active". However when I use a macro to open the form with the critera [Class] = "Active, sometimes I get non-active clients. This is because [Class} is updated on the form's OnCurrent event based on another calculated field. But, of course, I have to have a particular record open for the [Class] control to be updated. So I decided to use the DLookup function based on a query that only gives me "Active" clients. The query is:
SELECT tblSales.ClientID, Min(IIf([ContractsEnds]<Date() Or [DateCancelled]<Date(),"Inactive","Active")) AS CustomerStatus
FROM tblSales
GROUP BY tblSales.ClientID;

But, when I used
DoCmd.OpenForm "frmMain", , DLookup("[ClientID]", "qryOnAirActive", '[CustomerStatus] = 'Active'")
I get only one record and it is not active. I've tried all kinds of variations for the criteria with no good results, usually an error message.

The query works fine, I just can't get it to work with the Docmd.OpenForm

Any suggestions?

Try

DoCmd.OpenForm "frmMain", ,, "[CustomerStatus] = 'Active'"

Thanks for the quick reply. [CustomerStatus] is only in the query, qryOnAirActive, not the frmMain or tblMain. [CustomerStatus} comes from the query based on a related table (ClientID = ClientId), tblSales. The field and control [Class] is from tblMain.
The query gives me the desired results, only one entry for each client that is "Active", but it won't work in the DLookup criteria.

I think it's a bit more complicated than that.

I suspect that the Form is based on a Client table where a calculated field is updated from the Sales table.

I think you'll have to come up with a query based on the Client and Sales tables combined.


I don't thin you could produce a form which was updatable using a combination of tblMain and query qryOnAirActive.

But what you might able to do is create a temporary table based on the values returned by qryOnAirActive.

I've changed the query to get the ClientID from tblMain instead of the tblSales.
SELECT tblMain.ClientID, Min(IIf([ContractsEnds]<Date() Or [DateCancelled]<Date(),"Inactive","Active")) AS CustomerStatus
FROM tblSales INNER JOIN tblMain ON tblSales.ClientID = tblMain.ClientID
GROUP BY tblMain.ClientID;

But I still can't get the Dlookup to work. Am I just trying to do something that can't ge done?

DoCmd.OpenForm "frmMain", , DLookup("[ClientID]", "qryOnAirActive", "[CustomerStatus] = 'Active'")

Sorry about that, my train of thought got interrupted, anyway ...

(The next post below might be more useful)

Did you still want to be able to update the records of tblMain through this form?

If so there may be a solution without adding a marker to the record itself, which would ultimately be easier.


A query with the SQL below would produce a table called tmpOnAirActive which would hold the IDs of all the clients who were Active.


Combining this table with tblSales would make an updatable query which could be used to give only Active clients.


The only trouble is you would need to keep the temporary table up to date.

Oh, I've just tried something else that I've never tried before ..


I've only got two records to try it with but it seems to work.

You are SO GOOD! Works like a charm. Could you possibly explain how this is different from a DLookup statement?

What I've done is use the result of your query (qryOnAirActive) as a subquery in the Where clause of the DoCmd.OpenForm statement.



What has happened is the form is now based upon the sql statement using the WhereCondition supplied in the DoCmd.OpenForm statement above.

Thanks again. Very easy to understand your explanation.