Using Option group to sort

[Opal]

I would like to use an Option group to sort records in a bound form,
but
am having difficulty getting it to work. I have used the function
before without an issue, but this time I cannot get it to work
as desired.

Using Access 2003, I have a form bound to my Concern table.
Within this form, I have a subform bound to my Countermeasure
table. Each record in the Concern table is related to a record
in the Countermeasure table. I want to be able to sort the
records by their Status as it appears in the Countermeasure table -
Specifically, Open, Closed or All records.

When I used this before, the sort option was in a bound
form with no subform. This time, however, I want to sort
by the subform and cannot get the records to sort. I get a
parameter pop up looking for the value of "Status". Below is the
code that I am using:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
If ApplyType = acShowAllRecords Then
Me.fraStatus.Value = 3 'Show all records
Me.fraStatus.Enabled = True
Else
Me.fraStatus.Value = Null 'No option button selected
Me.fraStatus.Enabled = False
End If
End Sub

Private Sub fraStatus_AfterUpdate()
If Me.fraStatus.Value = 2 Then

Me.Filter = "Status = 'Closed'"
Me.FilterOn = True

ElseIf Me.fraStatus.Value = 1 Then

Me.Filter = "Status = 'Open'"
Me.FilterOn = True

Else

Me.FilterOn = False

End If
End Sub

Is it possible to achieve the desired results?

 

[Jeanette Cunningham]

Opal,
Is the option group on the main form?
Do you want the subform to show all records that have status of Open, when
Open is chosen from the option group on the main form?
Similar thing for closed or all records?
If the answer to the above is yes, you can use the value of the option group
in its after update event to set the subform's recordsource.

in the after update of the option group, something along this idea:
Dim strSQL as String
Dim strWhere as String

Select case Me.[OptionGroupName]
Case "Open"
strWhere = "WHERE tblCountermeasure.Status = 'Open'"
Case "Closed"
strWhere = "WHERE tblCountermeasure.Status = 'Closed'"
Case Else
strWhere = ""
End Select

strSQL = "SELECT tblCountermeasure.Field1, tlbCountermeasure.Field2 FROM
tblCountermeasure & strWhere
Debug.Print strSQL
Me.[Subform Control Name].Form.Recordsource = strSQL

You could add an Order By clause to the strSQL to sort the subform records.

Jeanette Cunningham

 

[Jeanette Cunningham]

<addition> there should be a space followed by a single double quote after
the e at the end of tblCountermeasuer in the line strSQL = "SELECT . . .

strSQL = "SELECT tblCountermeasure.Field1, tlbCountermeasure.Field2 FROM
tblCountermeasure " & strWhere

Jeanette Cunningham

Opal,
Is the option group on the main form?
Do you want the subform to show all records that have status of Open, when
Open is chosen from the option group on the main form?
Similar thing for closed or all records?
If the answer to the above is yes, you can use the value of the option
group in its after update event to set the subform's recordsource.

in the after update of the option group, something along this idea:
Dim strSQL as String
Dim strWhere as String

Select case Me.[OptionGroupName]
Case "Open"
strWhere = "WHERE tblCountermeasure.Status = 'Open'"
Case "Closed"
strWhere = "WHERE tblCountermeasure.Status = 'Closed'"
Case Else
strWhere = ""
End Select

strSQL = "SELECT tblCountermeasure.Field1, tlbCountermeasure.Field2 FROM
tblCountermeasure & strWhere
Debug.Print strSQL
Me.[Subform Control Name].Form.Recordsource = strSQL

You could add an Order By clause to the strSQL to sort the subform
records.

Jeanette Cunningham

I would like to use an Option group to sort records in a bound form,
but
am having difficulty getting it to work. I have used the function
before without an issue, but this time I cannot get it to work
as desired.

Using Access 2003, I have a form bound to my Concern table.
Within this form, I have a subform bound to my Countermeasure
table. Each record in the Concern table is related to a record
in the Countermeasure table. I want to be able to sort the
records by their Status as it appears in the Countermeasure table -
Specifically, Open, Closed or All records.

When I used this before, the sort option was in a bound
form with no subform. This time, however, I want to sort
by the subform and cannot get the records to sort. I get a
parameter pop up looking for the value of "Status". Below is the
code that I am using:

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)
If ApplyType = acShowAllRecords Then
Me.fraStatus.Value = 3 'Show all records
Me.fraStatus.Enabled = True
Else
Me.fraStatus.Value = Null 'No option button selected
Me.fraStatus.Enabled = False
End If
End Sub

Private Sub fraStatus_AfterUpdate()
If Me.fraStatus.Value = 2 Then

Me.Filter = "Status = 'Closed'"
Me.FilterOn = True

ElseIf Me.fraStatus.Value = 1 Then

Me.Filter = "Status = 'Open'"
Me.FilterOn = True

Else

Me.FilterOn = False

End If
End Sub

Is it possible to achieve the desired results?

 

[Opal]

<addition> there should be a space followed by a single double quote after
the e at the end of tblCountermeasuer in the line strSQL = "SELECT . . .

strSQL = "SELECT tblCountermeasure.Field1, tlbCountermeasure.Field2 FROM
 tblCountermeasure " & strWhere

Jeanette Cunningham

Opal,
Is the option group on the main form?
Do you want the subform to show all records that have status of Open, when
Open is chosen from the option group on the main form?
Similar thing for closed or all records?
If the answer to the above is yes, you can use the value of the option
group in its after update event to set the subform's recordsource.

in the after update of the option group, something along this idea:
Dim strSQL as String
Dim strWhere as String

Select case Me.[OptionGroupName]
   Case "Open"
   strWhere = "WHERE tblCountermeasure.Status = 'Open'"
   Case "Closed"
   strWhere = "WHERE tblCountermeasure.Status = 'Closed'"
   Case Else
   strWhere = ""
End Select

strSQL = "SELECT tblCountermeasure.Field1, tlbCountermeasure.Field2 FROM
tblCountermeasure & strWhere
Debug.Print strSQL
Me.[Subform Control Name].Form.Recordsource = strSQL

You could add an Order By clause to the strSQL to sort the subform
records.

Jeanette Cunningham

- Show quoted text -

Jeanette,

I am having problems with your code...

First of all:

strSQL = "SELECT tblCountermeasure.Field1, tlbCountermeasure.Field2
FROM
tblCountermeasure " & strWhere

What fields are you referring to?

Also: Me.[Subform Control Name].Form.Recordsource = strSQL

"Subform Control Name" - I take it to be the name of the field on the
subform, i.e. cboStatus?

"Recordsource" - is this the table or the subform name?

 

[Opal]