Dependent Listboxes with criteria

[Delta729]

Access Novice here, so please bare with me. (I hope my title makes sense as well.)

I have a form that has two listboxes. The first list box is named "lstType", which pulls in information from a table tblType. The second or dependent listbox, named lstSName, is then filtered and only shows the SName that match the type. This is pulled in from a table "tblSName." Using the form filtering on the query "[Forms]![frmqryTask]![lstType]"

This portion actually works perfectly and filter my form as I wanted it too (at least originally). However, I realized a few days ago that the when clients or the SName does not have any active projects my company will be working on for them, we don't want don't need to see their name on the forms listbox called "lstSNameand", so we want change a status from "Active" to "Inactive". I've already added this status in as well.

What I'd like to have happen is that when using the dependent listbox, the lstSName will only show those with the "Active" status. I've tried adding the status = active onto the query, however I get a error message saying the query is too complex to run, which I'm guessing has something to do with the "[Forms]![frmqryTask]![lstType]" on the query. (Just a guess). Any suggestions? (If I didn't explain portions of the correctly, let me know or ask. I am, after all, definitely a novice here.)

Any help would be greatly appreciative.

[June7]

Is the Status field a Yes/No data type?

status = True

Or is it text?

status = "active"

What table is status field in?

Post lstSNameand RowSource full SQL statement. I expect this will have to be a query that joins tblSName with tblProjects.

[Delta729]

The status is a text field from the table tblNameStatus and is linked to the table tblName as a combo box. Here is the sql for that.
SELECT tblNameStatus.ID, tblNameStatus.[Name Status]
FROM tblNameStatus;

Here is the sql for the query used on the form:

SELECT tblObjective.SourceName, tblObjective.Objective, tblObjective.[Relationship Lead], tblObjective.[Management Lead], tblObjective.[Contract Start Date], tblObjective.[Contract End Date], tblObjective.Status, tblTask.[Task/Deliverable], tblTask.Staff, tblTask.[Start Date], tblTask.[Due Date], tblTask.[Completed Date], tblTask.Notes, DateDiff("m",[tblObjective]![Contract Start Date],[tblObjective]![Contract End Date]) & " Months" AS [Contract Duration]
FROM tblObjective LEFT JOIN tblTask ON tblObjective.ObjectiveID = tblTask.ObjectiveID
WHERE (((tblObjective.SourceName)=[Forms]![frmqryTask]![lstSName]));

I took out the status = active earlier due to the error I was getting.

[June7]

You have tblName with all company info and also a tblNameStatus with company name and status? Why two tables? Why tblNameStatus table at all? Isn't it projects that are active/inactive?

The first query does not have a JOIN clause or WHERE clause. This results in a Cartesian relationship - every record of each table will associate with every record of other table.

[Delta729]

the tblNameStatus only has a "Name Status" & ID field which is used as a combobox for the user to select either "Active" or "Inactive". I originally had this as a listbox option instead of a lookup wizard, but made a separate table to use the combobox drop down options for the user and in case it's needed on a form filter somewhere. I pulled this sql from the field properties for tblName.Status. This is what Access inserted when I set it up using the lookup wizard. (Sorry, maybe I shouldn't have included that).

Only the tblName would be "Active" or "Inactive. The tblname is active while we have current "Objectives" to work on for the client "tblName" and each "objective" will have multiple task to complete the objective. Once all objectives and task are complete, we'd change it to "Inactive. Objective & task are either "Open" or "Closed".

(Like I said earlier, I'm very much a novice and I might not be getting you what you need to help me. Please bare with me.)

[June7]

As I understand, whether or not a Name is active/inactive can be determined by whether or not there are any open objective/task records. Objective/task is associated with project and project is associated with name?

Maintaining another field for the Name status is not necessary. The name status can be calculated when needed.

tblNameStatus has only two records? You save the ID into tblName field? I probably wouldn't bother with a table for only 2 records and would make the combobox RowSource a ValueList or use a yes/no field for active/inactive.

[Delta729]

Yes, you have the premise here. (Sorry, I'm really poor at explaining some of this.)

So, if I changed the tblNameStatus to a ValueList, how would I have the form only load the "Active" names on the dependent listboxes on the form?

[June7]

Not saying you have to change the combobox RowSource. Didn't really answer questions about the status field in tblNames. Is it Yes/No or text or number type? Exactly what value is saved?

Since I don't really know your data structure, am guessing about specifics. Options:

1. a field in tblNames to indicate active/inactive - simple query: SELECT [names] WHERE Status = "Active";

2. calculate the status based on objectives - possibly a query that joins tblNames, tblProjects, tblObjectives and selects only the names where objective is open.

If you want to provide db for analysis, follow instructions at bottom of my post.

I never build lookups in table: http://access.mvps.org/access/lookupfields.htm

[Delta729]

Thanks June7, I have it working now.

I changed the row source sql for the list box "lstSName" to as follows: (the top set of sql from my 1st reply back to you)
SELECT tblName.SourceName, tblName.SNType, tblName.SNStatus
FROM tblName
WHERE (((tblName.SNType)=[Forms]![frmqryTask]![lstType]) AND ((tblName.SNStatus)=1));

I also change type to SNType and status to SNStaus. Type being a reserved word I guess may of had something to do with it. The 1 is PK for the "Active".

Thanks again for your help. Sorry if I made this more confusing than it should have been, I'm still learning and hoping to take a class soon; especially on sql and VBA.

[June7]

Access query builder can be a big help to learn SQL syntax. Also, http://www.w3schools.com/SQl/default.asp

Lots of self-teaching sources, web and print. This one sounds interesting: Access™ 2007 Programming by Example with VBA, XML, and ASP by Julitta Korol, Wordware Publishing, Inc. I expect there is a later edition.