Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Oct 2002
    Posts
    14

    Unanswered: search by results of combo box

    I use an event procedure to conduct a search in a query based form that works perfectly using a text box. However, it does not work when using a combo or list box which is populated by a field in a table.

    I currently use the following as an "on click" event procedure:

    --------------------------------------------------
    Private Sub Command23_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "supplybydesc"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    stLinkCriteria = "[description]=" & Me![findboxsupply]
    End Sub
    ------------------------------------------------

    Where findbox supply is the combo box.

    If I delete the combo box on the form and add a text box and call it "findboxsupply" then all is well.

    Any Ideas.

    RZ
    Last edited by rickyzicky; 10-13-02 at 17:08.

  2. #2
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Your combo box must have more than one column.

    You probably have a row source of the combo box similar to this:

    SELECT [Employees].[EmployeeID], [Employees].[FirstName] FROM [Employees]

    In this example, only the employee's first name will be shown since the column width property is set to 0. (Column Width= 0";1")

    If the bound column is set to 1 (Bound = 1), then Me!cboEmployee will return the EmployeeID. If the bound column is set to 2, then Me!cboEmployee will return the FirstName. The wizard will automatically set the bound property for you. Don't change this unless the control is unbound. You can use the column property to retreive the value you need.

    try stLinkCriteria = "[description]=" & Me![findboxsupply].Column(1)

    or to test:

    debug.print "Column 0; " & Me![findboxsupply].Column(0)
    debug.print "Column 1; " & Me![findboxsupply].Column(1)

  3. #3
    Join Date
    Oct 2002
    Posts
    14
    I have only 1 column called description.
    The bound column is set to 0 by default.

    You stated "Your combo box must have more than one column."

    Are you saying that more than one colum is required? Or were you assuming?

    I added a second column and played with the "bound" with similar results.


    Thanks

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Sorry - making the assumption.

    What does this give you?

    debug.print Me![findboxsupply].Column(0)

  5. #5
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    The bound column set to 0 with will return 0 for the value Me![findboxsupply].

    I assume that this combo box is unbound?

  6. #6
    Join Date
    Oct 2002
    Posts
    14
    correct, it is unbound.

  7. #7
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    I just noticed that you are setting the value of stLinkCriteria after you have opened the form.

  8. #8
    Join Date
    Oct 2002
    Posts
    14
    It's the same way in other search forms using text boxes.
    Should this matter if it is a combo box?

  9. #9
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    Examples below:

    TEXT BOX
    --------------------------------------------------
    Private Sub Command23_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "supplybydesc"
    stLinkCriteria = "[description]=" & Me![findboxsupply]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    End Sub
    ------------------------------------------------

    or

    COMBO BOX
    --------------------------------------------------
    Private Sub Command23_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "supplybydesc"
    stLinkCriteria = "[description]=" & Me![findboxsupply].Column(0)
    'Assuming there is only one column (column index starts from 0 not 1)
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    End Sub
    ------------------------------------------------

  10. #10
    Join Date
    Oct 2002
    Posts
    14
    Same result either way.

    I did notice that the form seem to open 2 times. One displaying the corrrect form with no results (as if there was no match) and the second with just a blank background the same color as the background of the form.

    Strange

  11. #11
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    sorry a little slow today
    you need the single quote with a string

    stLinkCriteria = "[description]= & "'" & Me![findboxsupply].Column(0) & "'"

  12. #12
    Join Date
    Oct 2002
    Posts
    14
    Still a no go.
    There are too many (or 1 too few) double quotes/ I count 5.

    I changed:
    stLinkCriteria = "[description]= & "'" & Me![findboxsupply].Column(0) & "'"
    stLinkCriteria = "[description]= & ""'" & Me![combo1].Column(0) & "'" to




    with no change in the result.

  13. #13
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    one more time....

    stLinkCriteria = "[description]=" & "'" & Me![findboxsupply].Column(0) & "'"

    or

    stLinkCriteria = "[description]='" & Me![findboxsupply].Column(0) & "'"

  14. #14
    Join Date
    Oct 2002
    Posts
    14
    No go on either...
    Thanks anyway.

  15. #15
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440

Posting Permissions

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