Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problems with combobox and datatype (2002/SR1)

    I'm trying to provide filtering capabilities for my reports. I have a form that lists all the available reports in a listbox,
    below that I have a section for filtering the selected report. See picture below.

    I have 3 combo boxes. The first gets filled by available fields from the selected report. The second combo is the operator.
    The third gets filled with distinct records from the query that the report uses and just the field that was selected in the first combo.
    The code below generates the rowsource for the 3rd combo.

    <pre>Private Sub cboFilterBy_AfterUpdate()
    On Error GoTo ErrorHandler

    cboFilterAnswer.RowSource = ""

    If Not IsNull(cboFilterBy) Then
    cboComparisonOperator.Enabled = True
    cboComparisonOperator.SetFocus
    cboFilterAnswer.RowSource = CreatecboFilterAnswerSQL
    Else
    cboComparisonOperator.Enabled = False
    cboComparisonOperator = ""
    cboFilterAnswer.Enabled = False
    cboFilterAnswer = ""
    cboFilterAnswer.RowSource = ""
    End If

    cboFilterAnswer = ""

    Exit_ErrorHandler:
    Exit Sub
    ErrorHandler:
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_ErrorHandler
    End Sub</pre>


    <pre>Public Function CreatecboFilterAnswerSQL() As String
    On Error GoTo ErrorHandler

    Dim strFilterField As String
    Dim strFilterQuery As String
    Dim strSQL As String

    CreatecboFilterAnswerSQL = ""

    strFilterField = Me.cboFilterBy
    strFilterQuery = Me.lstReports.Column(2)

    strSQL = "SELECT DISTINCT [" & strFilterField & "] FROM " & strFilterQuery & ";"

    CreatecboFilterAnswerSQL = strSQL

    Exit_ErrorHandler:
    Exit Function
    ErrorHandler:
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_ErrorHandler
    End Function
    </pre>


    The first combo contains fields like: Defendant Name, Docket Number, Date Due, and Amount Due.
    If you select Defendant Name then select an operator then goto the 3rd combo the cursor blinks at the beginning.
    If you select Date Due, when you enter the 3rd combo the cursor blinks at the far right.
    Here is my problem, if you first select Date Due, then go back and select Defendant Name from the first combo,
    the cursor blinks at the far right. Then when I make a selection from the 3rd combo I get this error message below:

    "The value you entered isn't valid for this field"

    The combo is filled by looping through a recordset based on the query that is used in the report.
    It pulls just the fields of the query in. It is set to Value List and Limit to List = yes.

    The 3rd combo's RowSourceType is set to Table/Query and Limit to List = No

    If I close and reopen the form and select Defendant Name from the 1st combo, when I get to the
    3rd combo the cursor blinks at the far left. Is this a field datatype issue? I'm not sure how to handle
    this and I'm also having a darn time explaining it. That is why provided some code and a pic.

    Any help would be greatly appreciated.

    Thanks,
    Don
    Attached Images Attached Images

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problems with combobox and datatype (2002/SR1)

    Why don'y you just use a requery on the 3rd combo box after entering data in the 1st combo box?
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with combobox and datatype (2002/SR1)

    Thanks for the reply Pat!

    Yes I tried that but that only works if the 3rd combo's Limit to List =yes. Why is this??
    I need to allow them to type anything in that combobox at times.

    Any other suggestions?
    Thanks,
    Don

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problems with combobox and datatype (2002/SR1)

    Why don't you set the Limit to List property to No and ensure that AutoExpand is Yes.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with combobox and datatype (2002/SR1)

    I think I might not have explained my problem well enough. I'll give it another shot.
    If I select "Defendant Name" from the 1st combo, when I get to the 3rd combo the cursor is blinking
    far left which tells me that it sees Defendant Name as being of text datatype which it is. I can then select
    any other field from the 1st combo without having any problems in the 3rd.
    Now I close the form and reopen it. If I select Date Due first, then I select Defendant Name, when I go to
    the third combo and make a selection, I get the error msg: "The value you entered isn't valid for this field"
    I'm only having a problem if a date, number, or currency field is selected first. If a text field like "Defendant Name"
    is selected first everything works fine.

    I hope I have made it more understandable.
    Thanks for any help.
    Don
    Attached Images Attached Images

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problems with combobox and datatype (2002/SR1)

    What SQL code have you got for the Combo boxes "Filter By" and "Answer".
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Portland, Maine, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Problems with combobox and datatype (2002/SR1)

    The code below fills the "SortBy" and "FilterBy" combos:
    <pre>Private Sub lstReports_AfterUpdate()
    On Error GoTo ErrorHandler

    Dim strQuery As String
    Dim strSortByDefault As String
    Dim rst As New ADODB.Recordset
    Dim fnum As Integer

    strQuery = lstReports.Column(2)
    strSortByDefault = lstReports.Column(3)

    If lstReports.Column(4) = True Then
    fraSort.Enabled = True
    cboSort.Enabled = True
    cboSortBy.Enabled = True
    Else
    fraSort.Enabled = False
    cboSort.Enabled = False
    cboSortBy.Enabled = False
    Exit Sub
    End If

    If lstReports.Column(5) = True Then
    fraFilter.Enabled = True
    cboFilterBy.Enabled = True
    Else
    fraFilter.Enabled = False
    cboFilterBy.Enabled = False
    End If

    cboFilterBy = ""
    cboComparisonOperator = ""
    cboComparisonOperator.Enabled = False
    cboFilterAnswer = ""
    cboFilterAnswer.Enabled = False

    rst.Open strQuery, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    cboSortBy.RowSource = ""
    cboFilterBy.RowSource = ""
    cboSortBy = strSortByDefault
    cboSort = "Asc"

    For fnum = 0 To rst.Fields.Count - 1
    cboSortBy.AddItem rst.Fields(fnum).Name
    cboFilterBy.AddItem rst.Fields(fnum).Name
    Next fnum

    rst.Close
    Set rst = Nothing

    Exit_ErrorHandler:
    Exit Sub
    ErrorHandler:
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_ErrorHandler
    End Sub
    </pre>


    This is the code that fills the "Answer" combo:

    <pre>Public Function CreatecboFilterAnswerSQL() As String
    On Error GoTo ErrorHandler

    Dim strFilterField As String
    Dim strFilterQuery As String
    Dim strSQL As String

    CreatecboFilterAnswerSQL = ""

    strFilterField = Me.cboFilterBy
    strFilterQuery = Me.lstReports.Column(2)

    strSQL = "SELECT DISTINCT [" & strFilterField & "] FROM " & strFilterQuery & ";"

    CreatecboFilterAnswerSQL = strSQL

    Exit_ErrorHandler:
    Exit Function
    ErrorHandler:
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_ErrorHandler
    End Function
    </pre>

    <pre> Private Sub cboFilterBy_AfterUpdate()
    On Error GoTo ErrorHandler

    cboFilterAnswer.RowSource = ""

    If Not IsNull(cboFilterBy) Then
    cboComparisonOperator.Enabled = True
    cboComparisonOperator.SetFocus
    cboFilterAnswer.RowSource = CreatecboFilterAnswerSQL
    Else
    cboComparisonOperator.Enabled = False
    cboComparisonOperator = ""
    cboFilterAnswer.Enabled = False
    cboFilterAnswer = ""
    cboFilterAnswer.RowSource = ""
    End If


    'cboFilterAnswer = ""
    cboFilterAnswer.Requery
    Exit_ErrorHandler:
    Exit Sub
    ErrorHandler:
    MsgBox Err.Description & " " & Err.Number
    Resume Exit_ErrorHandler
    End Sub
    </pre>


    Thanks for your help.
    Don

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Problems with combobox and datatype (2002/SR1)

    Do you know what command it's failing on? Try setting a breakpoint and stepping thru the code until it fails.
    Failing this, I have run out of ideas.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •