Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 701
  • Last Modified:

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?
0
rawilken
Asked:
rawilken
  • 2
1 Solution
 
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
 
Jeffrey CoachmanCommented:
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
 
rawilkenAuthor Commented:
Only partial answer but headed in right direction
0
 
Jeffrey CoachmanCommented:
Thanks,

But what would have been a "Full" answer?

AFAICT my post does what you requested

;-)

JeffCoachman
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now