I'm trying to run a query based on a list box (called lstSearch). The list box has 3 columns, RevNum, Analyst and FormNum. After the user selects a review number (RevNum) from the list box, I want a query to pull all records from tblMain where list box RevNum = intReviewNumber from tblMain. Any suggestions?

hmmm. I've never used a list box w/ multiple columns that I recall.
You could put a piece of code in the event that happens when the value is selected from the listbox. I think ValueChanged maybe the event. Or you could create a submit button for the user to click after they choose from the listbox and put code in the click event of that button. The code would be something like DoCmd.OpenQuery, "QueryName"
I think you could put a variable in your query for the listbox value. You can write it something like:
"[FORMS]![FORMNAME].lstSearch"
I'm not sure how to tell it which column to look at right off. You might try adding RevNum after lstSearch with a period or bang seperating the two.

You could also code the query into your forms vba. Use DoCmd.RunSQL and then write your SQL and use the listbox value for the criteria.

Hope this helps you out

-Dave

Thanks! I'll give this a try.

Private Sub Command2_Click()
Dim ctlSource As Control
Dim intCurrentRow As Integer
Dim sLink As String
Dim bCheck As Boolean

sLink = ""
bCheck = False

Set ctlSource = Me.List0

For intCurrentRow = 0 To ctlSource.ListCount - 1

    If ctlSource.Selected(intCurrentRow) Then
        If bCheck = False Then
            sLink = "[MyField] = '" & ctlSource.Column(0, intCurrentRow) & "'"
            bCheck = True
        Else
            sLink = sLink & " AND [MyField] = '" & ctlSource.Column(0, intCurrentRow) & "'"
        End If
    End If
Next intCurrentRow

DoCmd.OpenReport "rptMyReport", , , sLink

End Sub


Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

Sorry, I think with your column names and assuming you are passing integers, it would be:

Dim ctlSource As Control
Dim intCurrentRow As Integer
Dim sLink As String
Dim bCheck As Boolean

sLink = ""
bCheck = False

Set ctlSource = Me.lstSearch

For intCurrentRow = 0 To ctlSource.ListCount - 1

    If ctlSource.Selected(intCurrentRow) Then
        If bCheck = False Then
            sLink = "[RevNum] = " & ctlSource.Column(0, intCurrentRow)
            bCheck = True
        Else
            sLink = sLink & " AND [RevNum] = " & ctlSource.Column(0, intCurrentRow)
        End If
    End If
Next intCurrentRow

DoCmd.OpenReport "rptMyReport", , , sLink

If this is not pulling a report based on a query, which it should, then there are other options to display the results. What are you doing with the results? Displaying them as a report, or giving those results to the user to do more data entry? In the latter case, then you could give them a form instead of the report using the same basic DoCmd... etc.

Did that help?




mmcdonal

Look it up at: http://wrox.books24x7.com