Obtaining combobox RowSource via VBA?

  • Thread starter Thread starter WSF
  • Start date Start date
W

WSF

Access97

I am trying to set a ComboBox Row Source using VBA and cannot work out
the correct syntax.

I wish to list two columns in the combo box thus:
The source is a table called tblStock which holds among it the
appropriate fields
PartNo [these are unique part numbers]
Description [descriptive text of the PartNo]
Group [these determine the Brand of the PartNo e.g. Honda, Ford, Nissan
etc]
Type [these categorise the PartNo's e.g. Accessory, Panel, Electrical etc]

The Group and Type are determined by Variables taken from option groups
selected on the form prior to entering the combo box

I wish the list all PartNo and Description sorted ascending by PartNo
where the Group and Type meet criteria determined by the aforementioned
selected options.

I have tried:

Dim StockGroup As String, StockType As String

Select Case Me!optGroup
Case 1
StockGroup = "Honda"
Case 2
StockGroup = "Ford"
End Select
Select Case Me!optType
Case 1
StockType = "Accessory"
Case 2
StockType = "Panel"

End Select

Me!lstPartNo.RowSource = "SELECT STOCK.PartNo, STOCK.Description From
Stock WHERE ((Stock.Group) = [StockGroup]) And ((Stock.Type) =
[StockType])) ORDER BY STOCK.PartNo;"
Me!lstPartNo.Requery

Nothing appears in the ComboBox

Any help gratefully appreciated

WSF
 
Replace what you have with this...

Me!lstPartNo.RowSource = "SELECT STOCK.PartNo, STOCK.Description" _
& " FROM Stock" _
& " WHERE ((Stock.Group) = '" StockGroup & "')" _
& " AND ((Stock.Type) = '" & StockType & "))" _
& " ORDER BY STOCK.PartNo;"

The requery is unnecessary...it happens automatically when you assign a new
rowsource.
 
Hello Paul,
Fixed a couple of typos

Me!lstPartNo.RowSource = "SELECT STOCK.PartNo" _
& " FROM Stock" _
& " WHERE ((Stock.Group) = '" & StockGroup & "')" _ [missing &]
& " AND ((Stock.Type) = '" & StockType & "')" _ [chgd "))" to "')"
& " ORDER BY STOCK.PartNo;"

works great!

Thanks for your help.
Regards,
Bill
 
Back
Top