Populate a listbox from combobox selection in MS Access 2007

On my form I have a field called Symbol as a combobox. I want to populate a listbox with records where this value exists in a table. This is the code I have so far...

Private Sub Symbol_Change()
    Me!lstHolders.RowSource = "Select tbl_Stock_Postn.StockPosnID, tbl_Customer.AcctName, tbl_Stock_Postn.TOTAL, tbl_Stock_Postn.Holding" _
    & "FROM tbl_Customer INNER JOIN tbl_Stock_Postn ON tbl_Customer.ID = tbl_Stock_Postn.ACCTNAME" _
    & "WHERE tbl_Stock_Postn.Holding) = " & Me.Symbol & "ORDER BY tbl_Customer.AcctName"
    Me!lstHolders.Requery
End Sub

I get no results in the listbox. Can someone please direct me on what I am not doing correctly?
rawilkenAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jeffrey CoachmanConnect With a Mentor MIS LiasonCommented:
I don't see where you are actually "connecting" to the Access database?
Are you trying to load these fields Horizontally of vertically?
How many records will your SQL be returning?

In any event, try something like this as a test:

Dim db As DAO.Database
Dim rst As DAO.Recordset
Set db = OpenDatabase("C:\YourFolder\YourDatabase")
Set rst = db.OpenRecordset("Select SomeStuff FROM YourTable")

rst.MoveFirst
 Do Until rst.EOF
    yourlistbox.AddItem rst!Somestuff
    rst.MoveNext
 Loop

rst.Close
Set rst = Nothing
db.Close
Set db = Nothing


This code should load a listbox (Vertically) with the values in the table Field.

;-)

JeffCoachman
0
 
EmenizerCommented:
First of all: are you sure the query returns results?
Try it by puting it in a variant and then run trough the code By Step.

If your are sure it returns results, you have to populate the listbox (e.g.: http://www.exceltip.com/st/Fill_a_ListBox-control_with_values_from_another_workbook_using_VBA_in_Microsoft_Excel/410.html )
0
 
rawilkenAuthor Commented:
Only partial answer but headed in right direction
0
 
Jeffrey CoachmanMIS LiasonCommented:
Thanks,

But what would have been a "Full" answer?

AFAICT my post does what you requested

;-)

JeffCoachman
0
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.

All Courses

From novice to tech pro — start learning today.