Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Unanswered: Driving me crazy!

    I would like to have a form where the user can select which fields they would like to query and those fields would show up when they hit a command button. I was thinking of using checkboxes to allow the user to chose what they wanted to see, but I cannot for the life of me figure out how to do. Any help is much appreciated.

  2. #2
    Join Date
    Sep 2004
    Location
    Kuala Lumpur
    Posts
    60
    Hi. I can't really help; someone smarter than I might have an answer... My two cents-worth is a question: "How can you have queries *prepared* to run when you don't know what fields you'll need?" Sounds ominous to me.

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi,

    Whereas I don't know of the proper way to do this you can create Parameters in your query for the fields likely to hold data needed to retrieve later or check on the site for what someone did with CascadingComboBoxes which works on the DrillDown principle. Each ComboBox you select from takes you to the Next ComboBox to dig deeper and get you closer to your results.
    Someone should be coming along soon to give you a real clearcut solution.

    have a nice one,
    Bud

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    I can't give you a detailed code I never tried this but:
    You could list all fields in a combobox (you can make combobox/listboxes to list fields in table), and with an add button user could add field names to a listbox. this section provides choosing fields option to user.

    then you could create a SQL string based on the field listbox in VBA and assign it to form's control source property.
    ghozy.

  5. #5
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    ... or better you could list fieldnames in listbox and make listbox multiselectable then use selected fieldnames for creating sql string.
    ghozy.

  6. #6
    Join Date
    Sep 2004
    Posts
    5
    ah, thats my problems, I've only been thinking one way, thanks a lot ghozy, that makes it easier.

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    ... And then of course there are those who've done something similar ... This is one of my filters that utilzes the technique you're looking for:

    Code:
    Function ConstructFilter() As String
        Dim HaveStatusSEL As Boolean, HaveTrackingSEL As Boolean, HaveRequestorSEL As Boolean
        Dim HaveUsingSEL As Boolean, HaveDatesSEL As Boolean
        Dim FilterString As String
        
        HaveStatusSEL = False
        HaveTrackingSEL = False
        HaveRequestorSEL = False
        HaveUsingSEL = False
        HaveDatesSEL = False
        
        ' Validations
        If BeginTrackTxt.Value & "" <> "" And EndTrackTxt.Value & "" <> "" Then
            If CLng(BeginTrackTxt.Value) > CLng(EndTrackTxt.Value) Then
                MsgBox "The starting tracking # cannot exceed the ending tracking #.", vbExclamation, "System Monitor"
                ConstructFilter = ""
                BeginTrackTxt.SetFocus
                Exit Function
            End If
        End If
        If BeginDtTxt.Value & "" <> "" And EndDtTxt.Value & "" <> "" Then
            If CDate(BeginDtTxt.Value) > CDate(EndDtTxt.Value) Then
                MsgBox "The starting date cannot exceed the ending date.", vbExclamation, "System Monitor"
                ConstructFilter = ""
                BeginDtTxt.SetFocus
                Exit Function
            End If
        End If
        
        FilterString = " WHERE ("
        If SelectionGroup.Value <> 0 Then
            FilterString = FilterString & "( [Purchase Orders].Status="
            Select Case SelectionGroup.Value
                Case 1  ' Purchase Requests
                    FilterString = FilterString & PO_Created_STAT
                Case 2  ' In Process Orders
                    FilterString = FilterString & PO_InProcess_STAT
                Case 3  ' Validated Orders
                    FilterString = FilterString & PO_Validated_STAT
                Case 4  ' Outstanding Orders
                    FilterString = FilterString & PO_Outstanding_STAT
                Case 5  ' Fulfilled Orders
                    FilterString = FilterString & PO_Fulfilled_STAT
                Case 6  ' Voided Orders
                    FilterString = FilterString & PO_VOIDED_STAT
            End Select
            FilterString = FilterString & " )"
            HaveStatusSEL = True
        End If
        If BeginTrackTxt.Value & "" <> "" Then
            If HaveStatusSEL Then FilterString = FilterString & " AND "
            FilterString = FilterString & "( [Purchase Orders].[Tracking #]"
            If EndTrackTxt.Value & "" <> "" Then
                FilterString = FilterString & " BETWEEN " & CLng(BeginTrackTxt.Value) & " AND "
            Else
                FilterString = FilterString & "=" & CLng(BeginTrackTxt.Value) & " )"
            End If
            HaveTrackingSEL = True
        End If
        If EndTrackTxt.Value & "" <> "" Then
            If HaveStatusSEL And Not HaveTrackingSEL Then
                FilterString = FilterString & " AND "
            End If
            If Not HaveTrackingSEL Then
                FilterString = FilterString & "( [Purchase Orders].[Tracking #]="
            End If
            FilterString = FilterString & CLng(EndTrackTxt.Value) & " )"
            HaveTrackingSEL = True
        End If
        If RequestedByComboBox.Value & "" <> "" Then
            If HaveStatusSEL Or HaveTrackingSEL Then FilterString = FilterString & " AND "
            FilterString = FilterString & "( [Purchase Orders].RequestedByID='" & RequestedByComboBox.Value & "' )"
            HaveRequestorSEL = True
        End If
        If DateGroup.Value <> 0 Then
            If HaveStatusSEL Or HaveTrackingSEL Or HaveRequestorSEL Then FilterString = FilterString & " AND "
            Select Case DateGroup.Value
                Case 1  ' Required Date
                    FilterString = FilterString & "( [Purchase Orders].RequiredDate"
                Case 2  ' Order Date
                    FilterString = FilterString & "( [Purchase Orders].OrderDate"
                Case 3  ' Last Modified Date
                    FilterString = FilterString & "( [Purchase Orders].LastModified"
            End Select
            If BeginDtTxt.Value & "" <> "" And EndDtTxt.Value & "" <> "" Then
                FilterString = FilterString & " BETWEEN #" & BeginDtTxt.Value & "# AND #" & EndDtTxt.Value & "# )"
            ElseIf BeginDtTxt.Value & "" <> "" Or EndDtTxt.Value & "" <> "" Then
                If BeginDtTxt.Value & "" <> "" Then
                    FilterString = FilterString & "=#" & BeginDtTxt.Value & "# )"
                ElseIf EndDtTxt.Value & "" <> "" Then
                    FilterString = FilterString & "=#" & EndDtTxt.Value & "# )"
                End If
            End If
            HaveDatesSEL = True
        End If
        If HaveStatusSEL Or HaveTrackingSEL Or HaveRequestorSEL Or HaveDatesSEL Then FilterString = FilterString & ")"
        
        If FilterString = " WHERE ()" Then FilterString = ""
        
        ConstructFilter = FilterString
    End Function
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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