Cannot evaluate/set option button state

[wolfie78uk]

Hi,

I have an access form that consists of two "parts". The lower part of the form displays a datasheet and the upper part of the form provides the user with a bespoke interface for filtering the data. It is the filter that I am concerned with.

The filter consists of a number of controls and one in particular is causing me an enourmous headache even though it should be a very straigtforward piece of code.

I have a frame (MileageFrame) which contains :

1 check box (chkMileage) which allows the user to enable this part of the filter

3 option buttons (optMileageGT, optMileageLT, optMileageB) which define the type of filter - is the milage less than, greater than or between certain values?

A number of text boxes for the user to enter filter info into. I don't think they are relevant to the question though.

The problem I am having is with the three option buttons. All I want to do is check whether they are selected but I recieve the error "Run time error 2427 : You entered an expression that has no value" when the function is called. My function is shown below (with the offending line higlighted) :

Public Function ApplyFilter()
    
    Dim sourceQuery As String
    Dim whereClause As String
    Dim accountCodeFilter  As Boolean
    
    'If a filter exists on Account Code then add the relevant where clause
    If Application.Forms("ViewAll").Controls("chkAccount").Value = False Then
        whereClause = ""
    Else
        Application.Forms("ViewAll").Controls("cmbAccountCode").SetFocus
        whereClause = "WHERE [RouteDetails].[Account Code] = '" & Application.Forms("ViewAll").Controls("cmbAccountCode").Text & "'"
        whereFlag = True
    End If
    
    'If a filter exists on Journey then add the appropriate where clause
    If Application.Forms("ViewAll").Controls("chkJourney").Value = True Then
            
        ' If this is the second clause then it must be preceded by AND
        'otherwise it is the first and must be proceeeded by WHERE
        Application.Forms("ViewAll").Controls("txtJourney").SetFocus
        If whereFlag Then
            whereClause = whereClause & " AND "
        Else
            whereClause = whereClause & " WHERE "
            whereFlag = True
        End If
        
        whereClause = whereClause & "[RouteDetails].Journey LIKE '*" & Application.Forms("ViewAll").Controls("txtJourney").Text & "*'"
    End If
    
    If Application.Forms("ViewAll").Controls("chkMileage").Value = True Then
    
        ' If this is the second clause then it must be preceded by AND
        'otherwise it is the first and must be proceeeded by WHERE
        Application.Forms("ViewAll").Controls("chkMileage").SetFocus
        If whereFlag Then
            whereClause = whereClause & " AND "
        Else
            whereClause = whereClause & " WHERE "
            whereFlag = True
        End If
        
        'Determine which option the user has selected (<, > or =)

     
[COLOR=#ff0000]If Application.Forms("ViewAll").Controls("optMileageGT").Value = True Then[/color]

            
            If Not IsNumeric(Application.Forms("ViewAll").Controls("txtMileageGT").Text) Then
                MsgBox "Mileage must be a number", vbOKOnly + vbExclamation, "Validation Error"
                Exit Function
            Else
                whereClause = whereClause & "Mileage > " & Application.Forms("ViewAll").Controls("txtMileageGT").Text
            End If
             
        ElseIf Application.Forms("ViewAll").Controls("MileageFrame").Value = 2 Then
            
            If Not IsNumeric(Application.Forms("ViewAll").Controls("txtMileageLT").Text) Then
                MsgBox "Mileage must be a number", vbOKOnly + vbExclamation, "Validation Error"
                Exit Function
            Else
                whereClause = whereClause & "Mileage < " & Application.Forms("ViewAll").Controls("txtMileageLT").Text
            End If
            
        ElseIf Application.Forms("ViewAll").Controls("MileageFrame").Value = 3 Then
        
            If Not IsNumeric(Application.Forms("ViewAll").Controls("txtMileageUpper").Text) _
                Or Not IsNumeric(Application.Forms("ViewAll").Controls("txtMileageLower").Text) Then
                
                MsgBox "Mileage must be a number", vbOKOnly + vbExclamation, "Validation Error"
                Exit Function
            Else
                whereClause = whereClause & "Mileage BETWEEN " & Application.Forms("ViewAll").Controls("txtMileageLower").Text _
                                & Application.Forms("ViewAll").Controls("txtMileageUpper").Text
            End If
        
        End If
        
        
    End If
    
    sourceQuery = "SELECT * FROM [RouteDetails] " & whereClause
    MsgBox sourceQuery

    Application.Forms("ViewAll").Controls("RouteDetails").Form.RecordSource = sourceQuery
    Application.Forms("ViewAll").Controls("RouteDetails").Requery
    
End Function

Also, in my form-load routine I wish to set one of the 3 option buttons to true and the others to false. However, the following line :

Me.optMileageGT.Value = 1

produces the error ("Runtime Error 2448. You can't assign a value to this object").

Any help would be greatly appreciated.

Thanks in advance,

Simon

 

[PHV]

Have you tried to test the value of the OptionGroup control ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244