Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2011
    Posts
    57

    Unanswered: Problems trying to add "ALL" option to combo box

    Hello,

    I have a form with a combo box whose values are based on a table (RotationTbl) using the following:

    That works fine, but when I add an "ALL" option I get the following error:

    "The number of columns in the two selected tables or queries of a union query don't match."

    I then went back and tried basing the combo box on the entire table (ID field and Rotation field):


    SELECT DISTINCTROW [RotationTbl].[RotationID], [RotationTbl].[Rotation] FROM [RotationTbl]

    I want to add an "ALL" option to the combo box, so I used a union query and came up with:

    SELECT DISTINCTROW [RotationTbl].[RotationID], [RotationTbl].[Rotation] FROM [RotationTbl] UNION SELECT "*" as RotationID, "(All)" as Rotation from RotationTbl.

    However, when I select the All option and try to run the query, I get an error saying that there's a "Datatype mismatch in criteria expression."

    I think the issue is that the ID field is an AutoNumber, while the Rotation field is Text therefore there's two different types of data. However, when I base my table only on the Rotation field, it tells me the Union query won't work because of the different number of columns...

    Can someone please tell me what I need to do to try to fix this problem? I've tried researching it online, but nothing I've found has seemed to fix it. Thanks in advance!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    SELECT DISTINCTROW [RotationTbl].[RotationID], [RotationTbl].[Rotation] FROM [RotationTbl] UNION SELECT 0 as RotationID, "(All)" as Rotation from RotationTbl.
    Depending on how you use the combo, you'll have to test for the special zero value.
    Have a nice day!

  3. #3
    Join Date
    Nov 2011
    Posts
    57
    I tried your suggestion and that makes it so that no results are returned. I'm trying to get it so that when a user selects the ALL, all the rows in the Rotation field are returned.
    I tried switching the 0 to a * to accomplish this, however this makes it so that the combo box has no values listed under it...

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by nic311 View Post
    I tried your suggestion and that makes it so that no results are returned.
    That's why I wrote that depending on how you use the combo, you'll have to test for the special zero value. You never explained what you intend to do whith the value selected in the combo, though I can guess that it will be used to operate a selection in the form data set. If I'm right, you now have to test the selected value for the zero (special value meaning "All") and handle it accordingly.
    Have a nice day!

  5. #5
    Join Date
    Nov 2011
    Posts
    57
    Sorry I'm completely new to Access and I'm not really sure what you mean by testing the value for the zero...

    The combo box is used as the criteria of a query that runs when the user clicks a command button on the form.
    The table Rotation has two records: Standard or Reverse. Those two are currently shown via the first select statement that I have in my original post. I want the "ALL" option to bring up results that have either Standard or Reverse as value for that field. That's why I originally tried changing the 0 to a *, so that it would bring up both of the options. However, this did not seem to work...

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You need to use another query (one that will return all records) when the value of the combo is zero. Post the code you use now and the SQL of the query.
    Have a nice day!

  7. #7
    Join Date
    Nov 2011
    Posts
    57
    The sql i currently have in place is what I mentioned above

    SELECT DISTINCTROW [RotationTbl].[RotationID], [RotationTbl].[Rotation] FROM [RotationTbl] UNION SELECT "*" as RotationID, "(All)" as Rotation from RotationTbl

    Which still leaves the combo box without any value options.

    My rotation table is made up of two fields: Rotation ID and Rotation. There is currently only two records in there: Standard and Reverse, each with an auto generated ID number.

    I also have another table, Product which contains rotation as one of the fields. It also has a product ID, product name, and a few other fields (size and material) in there. I currently have a query which links the ProductTbl with the Rotation Tbl. In the form, there is combo boxes for ProductName, Rotation, Size and Material.

    I'm a bit confused regarding the query. What would I be linking this query to, and how would I get it so that the value of 0 is identified with either the standard or reverse rotation?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    personally I wouldn't use a bound column
    I would populate the combo box manually
    I'd handle the selection of the value from the combo in vba
    If needed I'd need to set a value for the combo based on whatever is the relevant column in the underlying table

    when the form loads I'd place some code in the on load event
    so Id want to look at how to open a recordset (using wither DAO or ADO.. doesn't matter which)
    then I'd stuff those values into the combobox (having made certaint he combo box type was list not query

    havign selected a value I'd want to place some code in the controls on click event that either took the appropriate action or set a value.

    however given that the current source has two values is the combo box an appropriate control, a list box would be better, if there are only ever going to be 3 values then a radio button control may make more sense Im struggling trying to think of different forms of rotations (its got to be clockwise, anti clockwise or both).
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2011
    Posts
    57
    I did as you suggested and changed the box to a list box. I changed the property of multi select to simple and found the following code which allows the user to select either or both rotation options :


    Private Sub cmd_test_Click()
    On Error GoTo Err_cmd_test_Click

    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    Dim stDocName As String

    Set db = CurrentDb()
    Set qdf = db.QueryDefs("qry_multi_select")

    For Each varItem In Me!lst_rotation.ItemsSelected
    strCriteria = strCriteria & ",'" & Me!lst_rotation.ItemData(varItem) & "'"

    Next varItem
    If Len(strCriteria) = 0 Then
    MsgBox "You did not select anything from the list" _
    , vbExclamation, "Nothing to find!"
    Exit Sub
    End If

    strCriteria = Right(strCriteria, Len(strCriteria) - 1)
    strSQL = "SELECT * FROM AttributesTbl " & _
    "WHERE AttributesTbl.Rotation IN (" & strCriteria & ");"

    qdf.SQL = strSQL
    DoCmd.OpenQuery "qry_multi_select"
    Set db = Nothing
    Set qdf = Nothing


    Exit_cmd_test_Click:
    Exit Sub

    Err_cmd_test_Click:
    MsgBox Err.Description
    Resume Exit_cmd_test_Click

    End Sub

    This works fine now. However, I still have my other 2 combo boxes that I want to include as part of the query criteria when it searches. I am completely new to VBA, but is there a way to add the values chosen for the other combo boxes in this code as well, or as criteria under the query?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes
    build your sql on the fly
    eg

    Code:
    strSQL = "select my, column, list from mytable where 1=1 "
    'check the relevant controls to see if there are terms to add to the where clause
    if len(mylistbox.text) > 0 then
      strSQL = strSQL & " AND acolumn = avalue"
    endif
    if len(mylistbox.text) > 0 AND isnumeric(mylistbox.text) then
      strSQL = strSQL & " AND anumericcolumn = " & mylistbox.text
    endif
    if len(mynextlistbox.text) > 0 then
      strSQL = strSQL & " AND astringcolumn = '" & mynextlistbox.text & "'"
    endif
    the where 1=1 is a nifty little trick when building SQL so you odn't need to worry about inserting where or and verbs.
    string values need quote ' or double quote marks either side so the SQL engine understands where the text values starts and stops.
    one thing to be wary of when building SQL is to make certain that there is a space between each 'chunk' of the where clause so that the SQL engine knows where each clause starts and stops
    some put it at the end of the sentence, some at the beginning. doesn't matter where providing its there

    eg
    strSQL = strSQL & " AND ProductID = " & anumericvalue
    or
    strSQL = strSQL & " AND ProductID = " & anumericvalue & " "

    personally I prefer the former, as it easy to spot iof the spacing is or inst there. but each to their own.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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