Query result can show in list box, but not combo box (1 Viewer)

[dhlao]

I've a form that let user to input an ID, then click a button to show the result in a report's combo box.

The reason I use combo box but not list box is because I need to limit the number of result show in the report. So I need "ListRows" which only can use with combo box.

Private Sub Form_button_Click()
  Dim strReportName As String
  Dim strCriteria As String
  Dim strSql As String
  Dim listControl As Control

  strReportName = "Target_report"
  strCriteria = "[Id]='" & Me![txtFormId] & "'"
  strSql = "SELECT * FROM Db_table;"

  DoCmd.OpenReport strReportName, acViewReport, , strCriteria
  [Reports]![Target_report]![Combo1].RowSource = strSql

  Set listControl = [Reports]![Target_report]![Combo1]
  With listControl
    If .ListCount < 20 Then
      .ListRows = .ListCount
    Else
      .ListRows = 20
    End If
  End With
End Sub

If I change

[Reports]![Target_report]![Combo1].RowSource = strSql

to

[Reports]![Target_report]![ListBox1].RowSource = strSql

the query result can successfully show in the list

 

[JHB]

Sorry, but what is it exactly you want to do? Or put another way, what is the problem?

 

[BitsOfBytes]

You could use a continuous or "multiple items" subform to display that data. If you want to limit your result quantity just go into your query and change it to select top (20).