Adding criteria on the field being filled. (1 Viewer)

[jtoole]

I am working on a scheduling form that uses 3 combo boxes to select the people being scheduled. The form is rowsourced to a table of training sessions with a field for each of three crew positions being trained. One combo box is used for each of those fields. The row source for each combo box is a query that returns a list of people due for training, not on vacation that date, correct crew position, etc.

The problem is this: I want to eliminate the people already sceduled in prior sessions from the list. I've tried to put a "not" criteria on the rowsource query but that did not work.

I've been away from Access for a few years but I vaugely remember problems of putting criteria on the field you are filling. Anyone have any ideas?

 

[RuralGuy]

What do you mean the "not" criteria did not work? How about posting the SQL you tried and tell us how it failed? AFAIK the "not" criteria should work just fine.

 

[jtoole]

Here is the SQL for the rowsource on the combo box:

SELECT DISTINCT [TRAINING COMPLETION DATES].REC, [LAST NAME]+", "+[FIRST NAME] AS CAFULLNAME, [TRAINING COMPLETION DATES].[TNG NO], PEOPLE.POS, PEOPLE.AC, [RECURRENT TRAINING SESSIONS].CREW, [RECURRENT TRAINING SESSIONS].RECNO, PEOPLE.[PC BASE], DateAdd("m",11,DateSerial(Year(Now())-1,DatePart("m",DateValue("1 "+[PC BASE]+" 1967")),1)) AS [REC DUE START], DateSerial(Year(Now()),DatePart("m",DateValue("1 "+[PC BASE]+" 1967"))+2,1)-1 AS [REC DUE END]
FROM ((PEOPLE LEFT JOIN [TRAINING COMPLETION DATES] ON PEOPLE.[TNG NO] = [TRAINING COMPLETION DATES].[TNG NO]) LEFT JOIN [RECURRENT TRAINING SESSIONS] ON PEOPLE.[TNG NO] = [RECURRENT TRAINING SESSIONS].CA) LEFT JOIN VACATIONS1 ON PEOPLE.[TNG NO] = VACATIONS1.[TNG NO]
WHERE ((([TRAINING COMPLETION DATES].REC)>=[Forms]![TRAINING SESSION SHCEDULER]![STARTDATECOMBO]-60 And ([TRAINING COMPLETION DATES].REC)<=[Forms]![TRAINING SESSION SHCEDULER]![STARTDATECOMBO]+60) AND ((PEOPLE.POS)="CA") AND ((PEOPLE.AC)=[Forms]![TRAINING SESSION SHCEDULER]![ACCombo]) AND ((DateAdd("m",11,DateSerial(Year(Now())-1,DatePart("m",DateValue("1 "+[PC BASE]+" 1967")),1)))<[Forms]![TRAINING SESSION SHCEDULER]![STARTDATECOMBO]) AND ((DateSerial(Year(Now()),DatePart("m",DateValue("1 "+[PC BASE]+" 1967"))+2,1)-1)>[Forms]![TRAINING SESSION SHCEDULER]![STARTDATECOMBO]) AND ((VACATIONS1.VAC)>[Forms]![TRAINING SESSION SHCEDULER]![STARTDATECOMBO]+7 And (VACATIONS1.VAC)>=[Forms]![TRAINING SESSION SHCEDULER]![STARTDATECOMBO]-7) AND (Not ([RECURRENT TRAINING SESSIONS].CA)=[CA]));

Thbe "not" statement I'm having trouble with is at the end. Note that [CA] is the field being filled by this combo box. The trouble is I am still getting the whole list when entering subsequent records.

I know the answer is right in front of me; I'm just not seeing it! Any / all help is greatly appreciaited.

 

[RuralGuy]

This has probably been mentioned to you before so forgive me if I'm being redundant. Spaces in *any* name in Access will cause you unexpected grief some day; better to use CamelFontNames or Under_Score_Names. All UPPERCASE is definately hard to read. The "+" is a math operator in Access; the string concantenation character is "&". They often work the same but not always. Now() returns the current DateTime while Date() returns just the date. Have you considered using the SQL Between clause? What value is in [PC BASE]? Is it the name of a month? Have you tested to see what STARTDATECOMBO returns? A text TextBox next to the cbo named txtTest1 and code like this in the AfterUpdate event of the cbo would test it:
Me.txtTest1 = Me.STARTDATECOMBO
Those are just a few of the questions I have. I have to put this in a module so I can examine it further.

 

[jtoole]

You're right about the naming conventions. I'm working off some old tables (lots of flats, no relationships) and I guess I needed a good excuse to change it. Here is the new SQL from the query:

SELECT DISTINCT Training_Completion_Dates.REC, [Last_Name] & ", " & [First_ Name] AS CAFULLNAME, Training_Completion_Dates.TNG_NO, PEOPLE.POS, PEOPLE.AC, REC_TNG_SESSIONS.Crew, REC_TNG_SESSIONS.RECNO, PEOPLE.PC_Base, DateAdd("m",11,DateSerial(Year(Date())-1,DatePart("m",DateValue("1 "+[PC_Base]+" 1967")),1)) AS REC_DUE_START, DateSerial(Year(Date()),DatePart("m",DateValue("1 "+[PC_Base]+" 1967"))+2,1)-1 AS REC_DUE_END, REC_TNG_SESSIONS.CA
FROM ((PEOPLE LEFT JOIN Training_Completion_Dates ON PEOPLE.TNG_No = Training_Completion_Dates.TNG_NO) LEFT JOIN REC_TNG_SESSIONS ON PEOPLE.TNG_No = REC_TNG_SESSIONS.CA) LEFT JOIN Vacations ON PEOPLE.TNG_No = Vacations.[TNG NO]
WHERE (((Training_Completion_Dates.REC)>=[Forms]![Training_Session_Scheduler]![STARTDATECOMBO]-60 And (Training_Completion_Dates.REC)<=[Forms]![Training_Session_Scheduler]![STARTDATECOMBO]+60) AND ((PEOPLE.POS)="CA") AND ((PEOPLE.AC)=[Forms]![Training_Session_Scheduler]![ACCombo]) AND ((DateAdd("m",11,DateSerial(Year(Date())-1,DatePart("m",DateValue("1 "+[PC_Base]+" 1967")),1)))<[Forms]![Training_Session_Scheduler]![STARTDATECOMBO]) AND ((DateSerial(Year(Date()),DatePart("m",DateValue("1 "+[PC_Base]+" 1967"))+2,1)-1)>[Forms]![Training_Session_Scheduler]![STARTDATECOMBO]) AND ((Vacations.VAC)>[Forms]![Training_Session_Scheduler]![STARTDATECOMBO]+7 And (Vacations.VAC)>=[Forms]![Training_Session_Scheduler]![STARTDATECOMBO]-7));

I would normally use Between() but in this case I need both dates for other things, so it is just as well...

PC_Base is the first three letters of the month. STARTDATECOMBO from the above code (with the Not statement removed) returns a list of names and their corresponding Training number. All attempts at a not criteria returns an empty list.

Thanks for your help, RG!

 

[RuralGuy]

When I said:

Have you tested to see what STARTDATECOMBO returns? A text TextBox next to the cbo named txtTest1 and code like this in the AfterUpdate event of the cbo would test it:
Me.txtTest1 = Me.STARTDATECOMBO

What I meant was your SQL references [Forms]![Training_Session_Scheduler]![STARTDATECOMBO]. That particular reference to the ComboBox may not return what you think. Go ahead and create txtTest1 and see what returns. You still have some "+" as concantenation characters but it certainly is getting easier to read.