Populate listbox with sql query

[shabbaranks]

Hi guys,

Im currently pulling my hair out trying to overcome this one. I would like to populate a listbox based on an sql query which gets its record from a combo box...

Ok so the way Ive done it so far, my combo box is populated from a column in the table as per

Private Sub form_load()
Dim strSQL As String
strSQL = "SELECT [CompanyDetails].Company FROM CompanyDetails ORDER BY [CompanyDetails].Company; "
With Me![CompanyName_Combo]
    .RowSource = strSQL
    .Requery
End With
Me.CompanyName_Combo.SetFocus
End Sub

And then "in thoery" my list box would requery based on the selection in the combo box as per

Private Sub CompanyName_Combo_AfterUpdate()
Me.Results_listbox.RowSource = "SELECT CompanyDetails.Address1, CompanyDetails.Address2, CompanyDetails.Address3, CompanyDetails.Address4, " & _
"FROM [CompanyDetails] WHERE (CompanyDetails.Company) Like '*" & Me.CompanyName_Combo & "*' """
Me.Results_listbox.RowSourceType = "Table/Query"
Me.Results_listbox.Requery
End Sub

But the listbox isnt populating and if I press f5 I get the SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrct.

Can anyone assist please? Thanks

 

[pr2-eugin]

You first have to set the type as "Table/Query" first, after which you can change the RowSource.. Also your SELECT statement has an extra comma (,).. Try this..

Private Sub CompanyName_Combo_AfterUpdate()
    [B]Me.Results_listbox.RowSourceType = "Table/Query"[/B]
    Me.Results_listbox.RowSource = "SELECT CompanyDetails.Address1, CompanyDetails.Address2, CompanyDetails.Address3, CompanyDetails.Address4 " & _
                                   "FROM [CompanyDetails] WHERE (CompanyDetails.Company) Like '*" & Me.CompanyName_Combo & "*' """
    Me.Results_listbox.Requery
End Sub

 

[shabbaranks]

And bingo was his name-o... Thanks works fine

 

[pr2-eugin]

Glad to have helped..

 

[shabbaranks]

You wouldnt know how to clear down a list box upon exiting the form? If I use

me.listbox = ""

Nothing happens, no error but the listbox is still populated.

Fixed - there was content within the property as well as the VBA Code.

 

[Kamissar]

Thank you, I looked for ages for this. I was using 3 combo boxes as query parameters to locate a book title within a query that was drawn from a table of books.

putting this in in the afterupdate code for each combobox -

Me.lstTitle.RowSourceType = "Table/Query"
Me.lstTitle.RowSource = "SELECT Books.Title FROM Books WHERE (((Benchmarking.Genre)=Books.Genre) And ((Benchmarking.Box)=Books.Box) And ((Benchmarking.Level)=Books.Level)); "
Me.lstTitle.Requery

plus this in the Formload

Dim strSQL As String
strSQL = "SELECT Books.Title FROM Books WHERE (((Benchmarking.Genre)=Books.Genre) And ((Benchmarking.Box)=Books.Box) And ((Benchmarking.Level)=Books.Level)); "

With Me![lstTitle]
.RowSource = strSQL
.Requery
End With

worked the magic that I needed! Thanks again!

 

[Kamissar]

After I closed Access and opened it again, it repeatedly asks me for each of the query parameters, which wasn't happening before. Any ideas?

Fixed: Restarted again and it is working everytime. Weird. But good weird.