How do I make a listbox display the results of an SQL query in VBA? 1

[JimmyL]

I have a table of data linked to a text file, the text file being in a rather unhelpful format. The linked table in Access looks something like this:-

==================tblSecurityAccess=======================
Directory SecurityGroup Access
XData UsersGrp1 read
XData UsersGrp2 read/write
XData UsersGrp3 full

YData UsersGrp1 read/write
YData UsersGrp3 full
YData UsersGrp4 read/write
YData UsersGrp5 read

ZData UsersGrp1 full
ZData UsersGrp2 read/write
ZData UsersGrp6 read
====================================================

I have a simple form with a combobox and a listbox. The combobox allows the user to choose which directory he/she wishes to look at. This combobox uses a simple SQL query in the combobox's Row Source property.

Using the After Update event of the combobox, I wish to populate the listbox with all the security groups for the chosen directory. (Ideally the listbox would also display the access for each security group).

I am using Access 2000, and am trying to use VBA code of the form:-

Dim db As Database, rst As Recordset, strSQL As String
Set db = CurrentDB
Set rst = db.OpenRecordset
strSQL = "SELECT SecurityGroup FROM tblSecurityAccess WHERE Directory = " _
& cboDirSelect.Value
.
.
.


Unfortunately Access 2000 does not seem to recognise the Database or Recordset keywords!

Any ideas?

 

[tomschafer]

Set the list box's Row Source property equal to...
SELECT SecurityGroup FROM tblSecurityAccess WHERE Directory = [Forms]![Form Name]![Combo Box Name].
Which is pretty much the same thing as what you did for the combo box.
And on "Unfortunately Access 2000 does not seem to recognise the Database or Recordset keywords!" Try this instead...
dim db as adodb

 

[JimmyL]

Pezamystik,

thanks for your help!

I went the simple route and set the listbox Row source as an SQL query as you said. Then I requeried the listbox on the combobox After Update event using:-

Private Sub cboDirSelect_AfterUpdate()

Dim ctlLstBox As Control
Set ctlLstBox = Forms!frmMain!lstSecurityRules
ctlLstBox.Requery

End Sub

Cheers, Jimmy B-)

 

[tomschafer]

You could actually shorten your code down to

Private Sub cboDirSelect_AfterUpdate()

Me.Requery

End Sub

 

[Guest_imported]

Jimmy,
This may also be useful to you
Access 2000 uses ADODB instead of terms like Currentdb or Database.

You can use these terms in Access 2000 by loading the Microsoft DAO 2.5/3.51 Compatibility Library.

You do this in the VBA Code window and select TOOLS>REFERENCES
Put a tick in the appropiate box and then click OK

 

[Ed2020]

ADO is designed for accessing external data sources, such as SQL Server. It is actually slower than DAO when accessing native Jet tables. I'd stick with DAO.

 

[JimmyL]

Thanks for all your input!

I must admit, moving from Access 97 to Access 2000 has been far from straighforward. Unfortunately the Help in Access 2000 is far from helpful and didn't make much of a reference to any of the above changes.

Jimmy