Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3

    Question Unanswered: SELECT DISTINCT problems

    I am trying to set the RowSource of a Combo Box to a list of values from a SQL statement triggered by the on change event of a different Combo Box. I am using the SELECT DISTINCT operator, but only one value is being returned. I know there are more. When I past the SQL statement into the SQL view of a query, it works. But the code only returns one value. I'm obviously missing something. Can anyone help. I am a begginer, so go easy on me. Here's the code.

    Private Sub cbo2ndCriteria_Change()

    If IsNull(Me!cbo2ndCriteriaValue.Value) Then

    Else
    Me!cbo2ndCriteriaValue.Value = ""
    End If

    Dim var2ndCriteria As Variant
    Dim sqlIssueDate As String
    Dim sqlIssuedFor As String
    Dim sqlDiscipline As String
    Dim sqlRevision As String
    Dim sqlDrawingType As String
    Dim sqlPercentComplete As String

    sqlIssueDate = "SELECT DISTINCT IssueDate FROM tblRevisionSub;"
    sqlIssuedFor = "SELECT DISTINCT IssuedFor FROM tblRevisionSub;"
    sqlDiscipline = "SELECT DISTINCT Discipline FROM tblDrawingList;"
    sqlRevision = "SELECT DISTINCT Revision FROM tblRevisionSub;"
    sqlDrawingType = "SELECT DISTINCT DrawingType FROM tblDrawingList;"
    sqlPercentComplete = "SELECT DISTINCT PercentComplete FROM tblDrawingList;"

    var2ndCriteria = Me!cbo2ndCriteria.Value
    Dim varTmp As Variant

    Select Case var2ndCriteria
    Case Is = "Issue Date"
    var2ndCriteria = sqlIssueDate
    varTmp = "IssueDate"
    Case Is = "Issued For"
    var2ndCriteria = sqlIssuedFor
    varTmp = "IssuedFor"
    Case Is = "Discipline"
    var2ndCriteria = sqlDiscipline
    varTmp = "Discipline"
    Case Is = "Revision"
    var2ndCriteria = sqlRevision
    varTmp = "Revision"
    Case Is = "Drawing Type"
    var2ndCriteria = sqlDrawingType
    varTmp = "DrawingType"
    Case Is = "Percent Complete"
    var2ndCriteria = sqlPercentComplete
    varTmp = "PercentComplete"
    End Select

    Dim varFieldValue As Variant
    Dim cnMyRecordset As ADODB.Connection
    Dim rsMyRecordset As ADODB.Recordset
    Set cnMyRecordset = CurrentProject.Connection
    Set rsMyRecordset = New ADODB.Recordset
    rsMyRecordset.Open var2ndCriteria, cnMyRecordset, adOpenDynamic, adLockPessimistic
    varFieldValue = rsMyRecordset.Fields(varTmp).Value

    If IsNull(varFieldValue) Then
    Exit Sub
    Else
    Me!cbo2ndCriteriaValue.RowSource = varFieldValue
    End If

    End Sub

    Thanks.
    Pat

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Hi padraig,

    It looks like you're setting the RowSource property to a value, rather than a SQL statement.

    Instead of:
    Me!cbo2ndCriteriaValue.RowSource = varFieldValue

    Try:
    Me!cbo2ndCriteriaValue.RowSource = var2ndCriteria

    JT

  3. #3
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    If I do that, all I get is the SQL statement returned. The literal text statement. I'm not looking for that, I am looking for values. The SQL statement is supposed to get those values. Then I want those values as the RowSource for the combo box. Right now it's only returning one value, when I know there are more.

  4. #4
    Join Date
    May 2002
    Posts
    395

    Re: SELECT DISTINCT problems

    Originally posted by padraig
    I am trying to set the RowSource of a Combo Box to a list of values from a SQL statement triggered by the on change event of a different Combo Box. I am using the SELECT DISTINCT operator, but only one value is being returned. I know there are more. When I past the SQL statement into the SQL view of a query, it works. But the code only returns one value. I'm obviously missing something. Can anyone help. I am a begginer, so go easy on me. Here's the code.

    Private Sub cbo2ndCriteria_Change()

    If IsNull(Me!cbo2ndCriteriaValue.Value) Then

    Else
    Me!cbo2ndCriteriaValue.Value = ""
    End If

    Dim var2ndCriteria As Variant
    Dim sqlIssueDate As String
    Dim sqlIssuedFor As String
    Dim sqlDiscipline As String
    Dim sqlRevision As String
    Dim sqlDrawingType As String
    Dim sqlPercentComplete As String

    sqlIssueDate = "SELECT DISTINCT IssueDate FROM tblRevisionSub;"
    sqlIssuedFor = "SELECT DISTINCT IssuedFor FROM tblRevisionSub;"
    sqlDiscipline = "SELECT DISTINCT Discipline FROM tblDrawingList;"
    sqlRevision = "SELECT DISTINCT Revision FROM tblRevisionSub;"
    sqlDrawingType = "SELECT DISTINCT DrawingType FROM tblDrawingList;"
    sqlPercentComplete = "SELECT DISTINCT PercentComplete FROM tblDrawingList;"

    var2ndCriteria = Me!cbo2ndCriteria.Value
    Dim varTmp As Variant

    Select Case var2ndCriteria
    Case Is = "Issue Date"
    var2ndCriteria = sqlIssueDate
    varTmp = "IssueDate"
    Case Is = "Issued For"
    var2ndCriteria = sqlIssuedFor
    varTmp = "IssuedFor"
    Case Is = "Discipline"
    var2ndCriteria = sqlDiscipline
    varTmp = "Discipline"
    Case Is = "Revision"
    var2ndCriteria = sqlRevision
    varTmp = "Revision"
    Case Is = "Drawing Type"
    var2ndCriteria = sqlDrawingType
    varTmp = "DrawingType"
    Case Is = "Percent Complete"
    var2ndCriteria = sqlPercentComplete
    varTmp = "PercentComplete"
    End Select

    Dim varFieldValue As Variant
    Dim cnMyRecordset As ADODB.Connection
    Dim rsMyRecordset As ADODB.Recordset
    Set cnMyRecordset = CurrentProject.Connection
    Set rsMyRecordset = New ADODB.Recordset
    rsMyRecordset.Open var2ndCriteria, cnMyRecordset, adOpenDynamic, adLockPessimistic
    varFieldValue = rsMyRecordset.Fields(varTmp).Value

    If IsNull(varFieldValue) Then
    Exit Sub
    Else
    Me!cbo2ndCriteriaValue.RowSource = varFieldValue
    End If

    End Sub

    Thanks.
    Pat
    It looks to me the var2ndCriteria variable is storing the value that met the Select Case statement. I believe once the condition is found it exists out of the Select Case statement therefore only one value is being stored in the var2ndCriteria.

    Wish you luck!

  5. #5
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Originally posted by padraig
    If I do that, all I get is the SQL statement returned. The literal text statement. I'm not looking for that, I am looking for values. The SQL statement is supposed to get those values. Then I want those values as the RowSource for the combo box. Right now it's only returning one value, when I know there are more.
    If your combo box is displaying the SQL statement, then I'm guessing you have the "Row Source Type" for the combo box "cbo2ndCriteriaValue" (on the "Data" property sheet) set to "Value List". You can cycle through your recordset and build a value list, but it would be much easier to change the "Row Source Type" to "Table/Query" and populate the Row Source with a SQL statement. You'll save yourself a lot of grief.
    Code:
    Private Sub cbo2ndCriteria_Change()
    
    Dim bInvalidChoice As Boolean
    
    ' Clear the current value
    Me!cbo2ndCriteriaValue.Value = ""
    
    ' Populate the RowSource based on cbo2ndCriteria
    Select Case Nz(Me!cbo2ndCriteria.Value,"")
    Case Is = "Issue Date"
       Me!cbo2ndCriteriaValue.RowSource = "SELECT DISTINCT IssueDate FROM tblRevisionSub;"
    Case Is = "Issued For"
       Me!cbo2ndCriteriaValue.RowSource = "SELECT DISTINCT IssuedFor FROM tblRevisionSub;"
    Case Is = "Discipline"
       Me!cbo2ndCriteriaValue.RowSource = "SELECT DISTINCT Discipline FROM tblDrawingList;"
    Case Is = "Revision"
       Me!cbo2ndCriteriaValue.RowSource = "SELECT DISTINCT Revision FROM tblRevisionSub;"
    Case Is = "Drawing Type"
       Me!cbo2ndCriteriaValue.RowSource = "SELECT DISTINCT DrawingType FROM tblDrawingList;"
    Case Is = "Percent Complete"
       Me!cbo2ndCriteriaValue.RowSource = "SELECT DISTINCT PercentComplete FROM tblDrawingList;"
    Case Else
       bInvalidChoice = True
    End Select
    
    ' Initialize cbo2ndCriteriaValue to be the first of the available choices
    If (Not bInvalidChoice) Then
       Me!cbo2ndCriteriaValue.Value = Me!cbo2ndCriteriaValue.ItemData(0)
    End If
    
    End Sub

  6. #6
    Join Date
    Oct 2002
    Location
    Maine
    Posts
    49
    Provided Answers: 3
    Yes, you are correct. Someone pointed that out to me. It works the way I want it to now. Thanks for all the help everyone!!! I'm still learning.

Posting Permissions

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