Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57

    Unanswered: showing all in a combo box

    I hope you all are doing well this morning.

    I was wondering if there was an easy way to get an option for all on a combo box. I have a dropdown list to pick a person's name, then another one to pick one of several dates that differ on each person. I was wondering how I could put another option of all in the date dropdown that would put all of the selected person's dates together when a report is run off that form.

    Any help would be appreciated.

    Thanks,

    David

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    great question! trouble is i only have a work-around answer.
    see .GIF

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032

    Re: showing all in a combo box

    I'd suggest having a DefaultValue of * for the second combobox. And in the query use Like instead of =.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey decad1,

    If I understand your question you could alter this SQL statement:

    SELECT field FROM Table1 UNION SELECT 'All' FROM Table1

    This is based on a table called 'Table1' and one field called 'field'. It will first list the word 'All' and then all other data from the table. Just apply it to your combo box and your ready to go.

    Hope this helps,
    Kal

  5. #5
    Join Date
    Jan 2003
    Location
    Columbia, MO
    Posts
    57
    Hey, thanks for the reply. I tried using * and I couldn't get it to work yet. I also tried the Union Select option, but I got a "Syntax error in FROM clause." error. Here is what I had originally:

    SELECT DISTINCT dbo_HTCarcass.HarvDate, FullPatronName.FullName
    FROM FullPatronName INNER JOIN ((dbo_EIDNumbers INNER JOIN dbo_HTCarcass ON dbo_EIDNumbers.ElectronicID = dbo_HTCarcass.ElectronicID) INNER JOIN dbo_TagApplication ON dbo_EIDNumbers.ApplicationID = dbo_TagApplication.ApplicationNumber) ON FullPatronName.PatronNumber = dbo_TagApplication.PatronNumber
    WHERE (((FullPatronName.FullName)=[Forms]![frmCarcassReportForm]![CustName]))
    ORDER BY dbo_HTCarcass.HarvDate;

    Here is what I have now and have trouble with:

    SELECT DISTINCT HarvDate FROM dbo_HTCarcass UNION SELECT 'All' FROM dbo_HTCarcass, SELECT FullPatronName.FullName
    FROM FullPatronName INNER JOIN ((dbo_EIDNumbers INNER JOIN dbo_HTCarcass ON dbo_EIDNumbers.ElectronicID = dbo_HTCarcass.ElectronicID) INNER JOIN dbo_TagApplication ON dbo_EIDNumbers.ApplicationID = dbo_TagApplication.ApplicationNumber) ON FullPatronName.PatronNumber = dbo_TagApplication.PatronNumber
    WHERE (((FullPatronName.FullName)=[Forms]![frmCarcassReportForm]![CustName]))
    ORDER BY dbo_HTCarcass.HarvDate;

    Any thoughts in this matter would be appreciated.

    Thanks,

    David

  6. #6
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Originally posted by decad1
    Hey, thanks for the reply. I tried using * and I couldn't get it to work yet. I also tried the Union Select option, but I got a "Syntax error in FROM clause." error. Here is what I had originally:

    SELECT DISTINCT dbo_HTCarcass.HarvDate, FullPatronName.FullName
    FROM FullPatronName INNER JOIN ((dbo_EIDNumbers INNER JOIN dbo_HTCarcass ON dbo_EIDNumbers.ElectronicID = dbo_HTCarcass.ElectronicID) INNER JOIN dbo_TagApplication ON dbo_EIDNumbers.ApplicationID = dbo_TagApplication.ApplicationNumber) ON FullPatronName.PatronNumber = dbo_TagApplication.PatronNumber
    WHERE (((FullPatronName.FullName)=[Forms]![frmCarcassReportForm]![CustName]))
    ORDER BY dbo_HTCarcass.HarvDate;

    Here is what I have now and have trouble with:

    SELECT DISTINCT HarvDate FROM dbo_HTCarcass UNION SELECT 'All' FROM dbo_HTCarcass, SELECT FullPatronName.FullName
    FROM FullPatronName INNER JOIN ((dbo_EIDNumbers INNER JOIN dbo_HTCarcass ON dbo_EIDNumbers.ElectronicID = dbo_HTCarcass.ElectronicID) INNER JOIN dbo_TagApplication ON dbo_EIDNumbers.ApplicationID = dbo_TagApplication.ApplicationNumber) ON FullPatronName.PatronNumber = dbo_TagApplication.PatronNumber
    WHERE (((FullPatronName.FullName)=[Forms]![frmCarcassReportForm]![CustName]))
    ORDER BY dbo_HTCarcass.HarvDate;

    Any thoughts in this matter would be appreciated.

    Thanks,

    David
    Perhaps change this:
    WHERE (((FullPatronName. FullName)=[Forms]![frmCarcassReportForm]![CustName
    ]))

    To use Like instead of =:
    WHERE (((FullPatronName. FullName) Like [Forms]![frmCarcassReportForm]![CustName
    ]))

    Best regards,
    J. Paul Schmidt, Freelance Access and ASP Developer
    http://www.Bullschmidt.com
    ASP design tips, demo database on the Web, free barchart tool...

  7. #7
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hmmmm, how did you come up with that? First of all you have two SELECTS in your statement????? Secondly, if your going to select two fields you need to have the UNION give two values for both fields:

    UNION SELECT 'ALL', 'ALL'

    Try changing the select part of your statement to grab your two fields with all the INNERs and WHEREs then add the UNION then use your ORDER BY. This should get you good to go. Although I can't test it (because you have the database) it should look similiar to this:

    SELECT DISTINCT dbo_HTCarcass.HarvDate, FullPatronName.FullName
    FROM FullPatronName INNER JOIN ((dbo_EIDNumbers INNER JOIN dbo_HTCarcass ON dbo_EIDNumbers.ElectronicID = dbo_HTCarcass.ElectronicID) INNER JOIN dbo_TagApplication ON dbo_EIDNumbers.ApplicationID = dbo_TagApplication.ApplicationNumber) ON FullPatronName.PatronNumber = dbo_TagApplication.PatronNumber
    WHERE (((FullPatronName. FullName)=[Forms]![frmCarcassReportForm]![CustName
    ])) UNION SELECT 'All','All' ORDER BY dbo_HTCarcass.HarvDate;

    Later, Kal

  8. #8
    Join Date
    Mar 2003
    Posts
    46
    I currently use a combo-box to filter records on a form, because it uses the Len statement it accounts for 'Null' (ie. All records) automatically...code follows below, perhaps you can adapt for your needs...?

    CCC

    Public Function SetFilter() 'Dynamically creates an SQL string based on the Search criteria entered by the user
    Dim strsql As String

    strsql = ""

    'Evaluate whether Search criteria has been entered in the combo-boxes
    If Len(Me.cboRulesType) > 0 Then
    strsql = strsql & "[Parameter] = """ & Me![cboRulesType] & """ AND "
    End If
    If Len(Me.cboSecurityType) > 0 Then
    strsql = strsql & "[SecurityTypeID] = " & Me![cboSecurityType] & " AND "
    End If
    If Len(Me.cboQualifier) > 0 Then
    strsql = strsql & "[Qualifier] = """ & Me![cboQualifier] & """ AND "
    End If
    If Len(strsql) > 0 Then
    strsql = Left(strsql, Len(strsql) - 5) 'Truncates the last 5 digits of the SQL string ' AND "'

    Me.Form.Filter = strsql 'Applies the SQL string as the form filter
    Me.Form.FilterOn = True
    Else
    Me.Form.FilterOn = False
    End If
    End Function

  9. #9
    Join Date
    May 2002
    Location
    Atlanta, GA
    Posts
    117
    Hey David,
    ccc brings up another point. If you want to use this method, in putting All in your combo boxes, your going to have to establish (more than likely with code) that the report being run understands you want all data instead of queried data. You can easily use the On Open event on your report to change how the record source will find your data. If your running the report from your form you'll have to do something like the following:

    Private Sub Report_Open(Cancel As Integer)
    Dim mySQL as string
    If forms!yourform!combobox = "All" Then
    mySQL = "SELECT field FROM Table1"
    Else
    mySQL = "SELECT field FROM Table1 WHERE(field = """ & forms!yourform!combobox & """)"
    End If
    Me.RecordSource = mySQL
    Exit Sub
    End Sub

    Let me know if you have any questions about this. Or if you don't understand.

    Kal

Posting Permissions

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