Capture Filter Property value for record selection

[mreniff]

This cmdUpdate code uses the TOP predicate value entered in TEXTBOX1 to select “scattered” records throughout a filtered list (see list Select statement below).

Private Sub cmdUpdate_Click()
Dim strSQL As String
 
strSQL = "UPDATE Students SET Students.UpdateGroupAdvisingApt = -1 "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " 
strSQL = strSQL & "WHERE ((S.GroupAdvisingSessionID) is null) AND (S.Flightpath) = [Forms]![SCHEDULERFlightpath]![cboFlightPathList] " '
strSQL = strSQL & "AND (S.Session = " & Val([Forms]![SelectSessionFlightpath]![SessionCombo] & "") & "));"
 
fExecuteQuery strSQL, dbFailOnError
   

Me.Textbox1 = Null
DoCmd.Requery
 
End Sub

Form Select statement:

SELECT Students.Major, Students.UpdateGroupAdvisingApt, [Last] & ", " & [First] AS StudentName, Students.ID, Students.Session, Students.FlightPath, Students.GroupAdvisingSessionID, Students.SSN
FROM [Group Advising] RIGHT JOIN Students ON [Group Advising].GroupAdvisingRecordID = Students.GroupAdvisingSessionID
WHERE (((Students.Session)=[Forms]![SelectSessionFlightpath]![SessionCombo]) AND ((Students.FlightPath)=[Forms]![SCHEDULERFlightpath]![cboFlightPathList]) AND ((Students.GroupAdvisingSessionID) Is Null))
ORDER BY Students.Major, Students.UpdateGroupAdvisingApt; 

Usually an additional filter is manually applied using a list attribute (e.g. [Major]) changing form’s Order By property to (e.g Forms!(StudentsByFlightPath].[Major]=”BI”).

Is there a way to only select from the additional filtered list?

Will an Order BY clause select from additional filtered list?

[June7]

[Forms]![SCHEDULERFlightpath]![cboFlightPathList] is not concatenated - is query working?

If filter criteria is not included in the SQL statement, how would you expect it to be applied?

Can reference form Filter property to include that criteria in SQL statement.

A TOP N query usually does have ORDER BY clause. The form's sorting has no bearing on the constructed SQL statement.

[mreniff]

[Forms]![SCHEDULERFlightpath]![cboFlightPathList] is not concatenated - is query working? YES

If filter criteria is not included in the SQL statement, how would you expect it to be applied? Access Sort & Filter menu commands select and "toggle" a filter I usually right click on value and select "EQUALS = [attribute value].

Can reference form Filter property to include that criteria in SQL statement. This is what i am trying to figure out. Once an "adhoc" filter is applied, the "dynamic syntax" is visible in the form's filter property.

A TOP N query usually does have ORDER BY clause. Order by clauses are usually the last clause in a SQL statement. Do I insert it at the end?

The form's sorting has no bearing on the constructed SQL statement. I understand this.

I am not sure what I am doing wrong. I keep getting the message my reply is short by ten charactoers

[June7]

Exactly what is issue? How to reference form Filter property?

[mreniff]

Exactly what is issue? How to reference form Filter property?

[quote]
I apologize for not making myself clear and confusing two different form properties, Filter and Orderby.

Upon applying a filter to a form, the filter value of the form changes to reflect the "adhoc or dynamic" filtering value. For lack of better wording, due to lack of programming skills, I want to capture the "dynamic" value that is in the Filter property to use in the record selection process.

[June7]

Something like:

strF = Me.Filter

[mreniff]

Something like:

strF = Me.Filter

Duh! I am ashamed I did not think of that


Next question.... Do I add me.filter with this clause strSQL = strSQL & "ORDER BY [me.filter]);?

[mreniff]

quote]

there is a semicolon at the end of the SQL statement. What am I missing?
[/quote]

Private Sub cmdUpdate_Click()
Dim strSQL As String


strSQL = "UPDATE Students SET Students.UpdateGroupAdvisingApt = -1 "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE ((S.GroupAdvisingSessionID) is null) AND (S.Flightpath) = [Forms]![SCHEDULERFlightpath]![cboFlightPathList] " '
strSQL = strSQL & "AND (S.Session = " & Val([Forms]![SelectSessionFlightpath]![SessionCombo] & "") & "))"
strSQL = strSQL & "ORDER BY me.filter;"
 
 fExecuteQuery strSQL, dbFailOnError
   
' reset textbox to blank
Me.Textbox1 = Null
DoCmd.Requery
End Sub

[

[mreniff]

HOt damn.... got this to work

rivate Sub cmdUpdate_Click() 'scheduler flightpath advising
Dim strSQL As String


strSQL = "UPDATE Students SET Students.UpdateGroupAdvisingApt = -1 "
strSQL = strSQL & "WHERE Students.[ID] IN "
strSQL = strSQL & "(SELECT TOP " & CLng(Me.Textbox1) & " [ID] FROM Students As S " ' select # or records based textbox1 value
strSQL = strSQL & "WHERE ((S.GroupAdvisingSessionID) is null) AND (S.Flightpath) = [Forms]![SCHEDULERFlightpath]![cboFlightPathList] " '
strSQL = strSQL & "AND (S.Session = " & Val([Forms]![SelectSessionFlightpath]![SessionCombo] & "") & ")"
strSQL = strSQL & "ORDER BY (S.major));"
 
 fExecuteQuery strSQL, dbFailOnError
   
' reset textbox to blank
Me.Textbox1 = Null
DoCmd.Requery
End Sub