Two Option Groups on one form

[Khalil Handal]

Hi to all,

I have main form with a subform. The main form has an option group to show books for a certain person based on the selection: (Borrwed, Returned or Both). I have a Public Function to requery the subform in the AfterUpdate event of the option group. Here is the VBA code I am using.

Public Function RequerySubform()
On Error GoTo ProcError

Dim strSQL As String
Dim varReturned As Variant


    If Len(Me.cboSelectedPerson & vbNullString) > 0 Then

        Select Case fraBookStatus
            Case 1 ' Borrowed books
                varReturned = "IS NULL"
            Case 2 ' Returned books
                varReturned = "IS NOT NULL"
            Case 3 ' All books borrowed and returned by a given member.
                varReturned = Null
        End Select
       
    
        ' mstrWHERE = "fkMemberID = " & Me.cboSelectedPerson & (" AND [DateReturned] " + varReturned)
          mstrWHERE = "fkMemberID = " & Me.cboSelectedPerson & (" AND [DateReturned] " + varReturned) & (" AND [DateBorrowed] " + "Is Not Null")
          
    
        strSQL = "Select * from qryBorrowingBooks where " & mstrWHERE
        Me.cntBorrowedBooksSubForm.Form.RecordSource = strSQL
    End If

ExitProc:
    Exit Function
ProcError:
    MsgBox "Error " & Err.Number & ": " & Err.Description, , _
               "Error in RequerySubform event procedure..."
    Resume ExitProc
End Function

I want to add a second Option group (fraDates) to take the selected records based on (fraBookStatus) and sort them by dates: Date borrowed, Date Due or Date Returned selected from the second option group.

Any ideas of how to do it?

Khalil

[Welshgasman]

Just test the option group and add to the mstrWhere, just as you have with the first option group.

I am suprised that even works as the brackets are outside the quotes?

You should always Debug.Print your sql string and see if it is what you need, not what you think you have.

[Khalil Handal]

Hi,
Thank you for the reply
I added the following line to have another Select Case in the same AfterUpdate event:

 Select Case fraDates
            Case 1 ' Date Borrowed
                varDates = "1"
            Case 2 ' Date Due
                varDates = "2"
            Case 3 ' Date Returned
                varDates = "3"
        End Select

I tried to add the Order By but was not successful.

mstrWHERE = "fkMemberID = " & Me.cboSelectedPerson & (" AND [DateReturned] " + varReturned) & (" AND [DateBorrowed] " + "Is Not Null") &  ORDER BY fraDates

I got an "End of statement" error.

Thanks

[Welshgasman]

As I said 'Use Debug.Print' to see your error.
Your concatenation is incorrect.

[Micron]

To add to that, when you eliminate your error message(s) and try to apply sorting, what kind of date sort is a 1 or 2 or 3 (the value of fraDates)?
Also looks to me like you're going to need to use some other event/trigger since
- you'll be applying a form recordsource and then a sort (inefficient) or
- applying a sort then losing it when a recordsource is applied (because the operations were done backwards)
Probably a button click event would be better and apply the sort as part of the recordsource sql.

[moke123]

Using an ampersand "&" and a plus "+" when concatenating do 2 separate things especially when there is a null involved.

I would probably simplify the book status Opt group code

       Select Case fraBookStatus

            Case 1 ' Borrowed books
                varReturned = " AND [DateReturned] is null"

            Case 2 ' Returned books
                varReturned = " AND [DateReturned] is not null"

            Case 3 ' All books borrowed and returned by a given member.
                varReturned = ""

        End Select

       debug.print varReturned

Same with the dates

Select Case fraDates

            Case 1 ' Date Borrowed
                varDates = " Order by DateBorrowed "

            Case 2 ' Date Due
                varDates = " Order by DateDue "

            Case 3 ' Date Returned 
               varDates = " Order by DateReturned "

        End Select

If this applies to a subform, I may be more inclined to use a filter.

[Khalil Handal]

Thank you Moke123
I used the Debug.Print to follow up with the mstrWHERE and strSQL.

One more logical issue is that when Books are not returned yet to the library the date returned value is null and I cannot sort by that option.

In other words: optActive from fraBooksStatus (Borrowed books) is selected, Date returned is null because books are not returned yet.
How will I disable the optReturned Button (Date Returned) in fraDtes?

I tried this code unsuccessfuly.

 If fraBookStatus = 1 Then
            OptReturned.Locked = True
        Else
            ' do nothing
        End If

I put the above code after the Select Case fraBookStatus and before the Select Case fraDates

Any advise?

[Welshgasman]

You could use the NZ() function to put in a spurious date? like 30/12/2099
Increase the year, if you think your app is still going to be running by then.

[Khalil Handal]

Hi to all,
Resolved it with the following:

Private Sub optActive_GotFocus()

        Me.optActive.Enabled = True
        Me.OptReturned.Enabled = False

End Sub

Private Sub optBoth_GotFocus()

        Me.optBoth.Enabled = True
        Me.OptReturned.Enabled = True

End Sub

Private Sub optInactive_GotFocus()

        Me.optInactive.Enabled = True
        Me.OptReturned.Enabled = True

End Sub

Thank you all
Khalil

[moke123]

What happens if they choose the wrong option? They can't choose another if not enabled.

Option groups only allow one option to be selected at a time so no need to disable an option.