Radio buttons to change query defs (1 Viewer)

[Kayleigh]

Hi,
I am designing a continuous form to display all current students. The data source is a query based on student name list query - who are currently enrolled.
I would like the radio buttons to offer to display the students who are no longer enrolled or ALL students past/current.
My idea was to change the query def of the base query to include those students required however it is not changing on the form display. Can anyone assist me with this?
(By the way - I notice the pop-out form disappears after I have done any changes and requires C+R so it is visible.)

 

[MajP]

That is a lot of work for not much payoff. Simply make three queries. qryCurrent, qryAll, qryLeft and save them.

Private Sub Frame20_AfterUpdate()
    Select Case Me.Frame20.Value
    Case 1 'Current
      me.recordsource = "qryCurrent"
    Case 2 'Left
      me.recordsource = "qryLeft"
    Case 3 'All
      me.recordsource = "qryAll"
    End Select
End Sub

 

[Kayleigh]

I did that at the start but I found that whenever there was a small change to the data recordsource, I had to change the SQL three times so trying to prevent that.

 

[plog]

SHould be able to use Form.Filter:

Form.Filter property (Access)

Office VBA reference topic

docs.microsoft.com

Whenever a button is clicked turn the filter off, set it correctly, turn it on.

 

[MajP]

I did that at the start but I found that whenever there was a small change to the data recordsource, I had to change the SQL three times so trying to prevent that.

So let me see if I understand. You prefer to instead update this?. Unfortunately there is not sacrasm emoji.

Case 1 'Current
    strSQL = "SELECT tblStudents.fldStudentID, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]) AS Student, ([fldHousenumber] & "" "" & [fldstreet]) AS Address, tblParents.fldParentID, [fldMotherTitle] & "" "" & [tblParents].[fldPLastName] AS Parent, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname] & ""  -  "" & [fldhousenumber] & "" "" & [fldstreet]) AS Student1, qrylkpClassName.fldClassID, tblStudents.fldDateLeft " & vbCrLf & _
    "FROM (tblParents RIGHT JOIN tblStudents ON tblParents.fldParentID = tblStudents.fldParentID) LEFT JOIN qrylkpClassName ON tblStudents.fldClassID = qrylkpClassName.fldClassID " & vbCrLf & _
    "WHERE (((tblStudents.fldDateLeft) Is Null Or (tblStudents.fldDateLeft)>Date())) " & vbCrLf & _
    "ORDER BY IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]);"
    Case 2 'Left
    strSQL = "SELECT tblStudents.fldStudentID, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]) AS Student, ([fldHousenumber] & "" "" & [fldstreet]) AS Address, tblParents.fldParentID, [fldMotherTitle] & "" "" & [tblParents].[fldPLastName] AS Parent, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname] & ""  -  "" & [fldhousenumber] & "" "" & [fldstreet]) AS Student1, qrylkpClassName.fldClassID, tblStudents.fldDateLeft " & vbCrLf & _
    "FROM (tblParents RIGHT JOIN tblStudents ON tblParents.fldParentID = tblStudents.fldParentID) LEFT JOIN qrylkpClassName ON tblStudents.fldClassID = qrylkpClassName.fldClassID " & vbCrLf & _
    "WHERE (((tblStudents.fldDateLeft)<Date())) " & vbCrLf & _
    "ORDER BY IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]);"
    Case 3 'All
    strSQL = "SELECT tblStudents.fldStudentID, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]) AS Student, ([fldHousenumber] & "" "" & [fldstreet]) AS Address, tblParents.fldParentID, [fldMotherTitle] & "" "" & [tblParents].[fldPLastName] AS Parent, IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname] & ""  -  "" & [fldhousenumber] & "" "" & [fldstreet]) AS Student1, qrylkpClassName.fldClassID " & vbCrLf & _
    "FROM (tblParents RIGHT JOIN tblStudents ON tblParents.fldParentID = tblStudents.fldParentID) LEFT JOIN qrylkpClassName ON tblStudents.fldClassID = qrylkpClassName.fldClassID " & vbCrLf & _
    "ORDER BY IIf(([fldKnownAs] Is Null),[tblstudents].[fldSLastName] & "" "" & [tblstudents].[fldFirstname],[tblstudents].[fldSLastName] & "" "" & [tblStudents].[fldKnownAs]);"

However the qryAll gets changed. The other two just add a criteria to qryall. So their sql is
Select * from qryAll where WHERE fldDateLeft)<Date()
and
Select * from qryAll where fldDateLeft Is Null Or fldDateLeft>Date()

 

[Pat Hartman]

Changing the RecordSource should requery the form but if it isn't, you can force it with:

Me.Requery.