MS Access 2016 How top populate a list box from a combobox selection using command button

Hello,

I have a form with a combo box that gets populated on form load.  I want the user to be able to select a value in the combo box, click on a command button with sql that should populate the rowsource of the list box with the results.  Not getting any results in the list box.  Code:


Private Sub Form_Load()
    Me.cboTrackName.RowSourceType = "Table/Query"
    Me.cboTrackName.RowSource = "SELECT DISTINCT dbo_wagers.track" & _
    " FROM dbo_wagers WHERE dbo_wagers.track Is Not Null ORDER BY dbo_wagers.track;"
   
    Me.cboTrackName = Me.cboTrackName.ItemData(0)
End Sub

Private Sub cmdFilteredResults_Click()
     Dim strSQL As String
    strSQL = "select dbo_wagers.wager from dbo_wagers where dbo_wagers.track = ' " & Me.cboTrackName.Value & "'"
    MsgBox "Current track selected is " & Me.cboTrackName.Value & ""
    MsgBox "Query is " & strSQL & ""
    Me.lstResultsByTrack.RowSourceType = "Table/Query"    
    Me.lstResultsByTrack.RowSource = strSQL
    Me.lstResultsByTrack.Requery
       
End Sub

Any suggestions appreciated.
Paul SchulerSystems Production Support AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Máté FarkasDatabase Developer and AdministratorCommented:
Hi,
Did you try to copy and run query from MsgBox displayed?
You have and extra space at the beginning of the criteria (at "dbo_wagers.track = "):
strSQL = "select dbo_wagers.wager from dbo_wagers where dbo_wagers.track = '" & Me.cboTrackName.Value & "'"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
agree with Mate, no points please

It looks like you have a space between the ' and the " before the first & in you SQL Statement.
0
Paul SchulerSystems Production Support AnalystAuthor Commented:
That was the issue thank you both very much for the assist!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Paul SchulerSystems Production Support AnalystAuthor Commented:
That was the issue thanks for the assist!
0
Dale FyeCommented:
Paul,

Mate provide the answer first, I was just endorsing his comment and requested no points.  Recommend you request assistance on your original question and ask the moderators to reopen the question, so that you can reallocate points.

Dale
0
Paul SchulerSystems Production Support AnalystAuthor Commented:
Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.