Binding RecordSets

[Deleted member 73419]

Hi all,

I have a form with a listbox on and want to bind it to a recordset. So far I have this:

Private Sub Form_Load()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strCustomersSQL As String
 
    strCustomersSQL = "SELECT a,b,c FROM d;"
 
    Set db = CurrentDb
 
    Set rs = db.OpenRecordset(strCustomersSQL, , dbReadOnly)
 
    rs.MoveFirst
 
    Set lstCustomer.Recordset = rs
 
    lstCustomer.Requery
 
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

The idea being when the form is opened, the recordset fills the listbox however it doesn't. The problem is the recordset and database are closed at the and of the sub. If I comment out the last four lines everything works and the listbox is populated.

I want to do a proper job at this so if I leave these lines out, how to I clean up the variables since I can only use them within that particular sub? It's not like I can close the database and set it to nothing from another routine as I won't be able to access them.

Where am I going wrong?

Thanks

 

[wiklendt]

i'm not sure WHY you want to do this (i may be too novice), but why not just use a Record Source SQL for your listbox? i use listboxes on forms all the time and their purpose is to "go to the selected record" on the form when i select it from the listbox...

 

[SOS]

A list box doesn't have a Recordset property. It has a ROW SOURCE (where the list items can be a query, table, or value list) property and a CONTROL SOURCE property (where that is a single field bound to a query, table, or it can be bound to an ADO recordset).

 

[Deleted member 73419]

i'm not sure WHY you want to do this (i may be too novice), but why not just use a Record Source SQL for your listbox? i use listboxes on forms all the time and their purpose is to "go to the selected record" on the form when i select it from the listbox...

I am not sure why I started out that way in the first place must have seemed good at the time.

Thanks

 

[SOS]

but why not just use a Record Source SQL for your listbox?

That would be ROW SOURCE (there is no Record Source for a list box)

 

[Deleted member 73419]

That would be ROW SOURCE (there is no Record Source for a list box)

That it would indeed be

The trouble is now that it does not display the correct data. Although bound column is set to 2, it displays data from column 1 aarrrrghhhhh

Any ideas?

 

[SOS]

That it would indeed be

The trouble is now that it does not display the correct data. Although bound column is set to 2, it displays data from column 1 aarrrrghhhhh

Any ideas?

You need to also set the

ColumnCount
ColumnWidths


so for two columns in the query you set

ColumnCount property to 2

and the
ColumnWidths property to

0";2"

to show the second column but have the first column hidden.

 

[Deleted member 73419]

You need to also set the

ColumnCount
ColumnWidths


so for two columns in the query you set

ColumnCount property to 2

and the
ColumnWidths property to

0";2"

to show the second column but have the first column hidden.

I really need to show both columns in the drop down but once i've selected something I only want the value from the second column to display.

Is this possible?

 

[SOS]

Is this a combo box or a list box?

 

[Deleted member 73419]

Is this a combo box or a list box?

Sorry!! I've changed the list box to a combo box as it's more appropriate to what i'm doing and forgot to say.

It would be really nice if there was a way to display both columns of data when something is selected but i'd settle for getting the second column even if this is possible!!

 

[SOS]

Sorry!! I've changed the list box to a combo box as it's more appropriate to what i'm doing and forgot to say.

It would be really nice if there was a way to display both columns of data when something is selected but i'd settle for getting the second column even if this is possible!!

Just switch the two columns around in the query. It will show the first column then after selecting.

 

[wiklendt]

A list box doesn't have a Recordset property. It has a ROW SOURCE (where the list items can be a query, table, or value list) property and a CONTROL SOURCE property (where that is a single field bound to a query, table, or it can be bound to an ADO recordset).

sorry, that's what i meant - i was typing too fast for my brain.

thanks for bringing it up multiple times in one thread.

cosmarchy - the BOUND column ought to be your primary key for your ROW SOURCE SQL (read: query for combobox). this is in the DATA tab, but what you need to be looking at is the FORMAT tab, where you tell access which columns to display - that is, you tell access that there are two columns in your ROW SOURCE and that the first column (which presumably holds you bound PK column) you want hidden (0cm) while the second is the data you want to display, so you give that a greater than zero value... like this

Column Widths: 0cm; 2cm (or whatever units you are using).

if you want to display both columns, change the above to something like:

Column Widths: 0.5cm; 2cm (because PK often doesn't take up too much space).

and just make sure that this all fits both the data and the size of your combo box (you can also increase or decrease the size of your combo box to match the data/form aesthetics).

however, i would urge against displaying the PK in a form control - a PK is there for the use of Access, while the human-read data (the values in column two) should be what users see - users may become confused by seeing the PK.

 

[SOS]

Column Widths: 0cm; 2cm (or whatever units you are using).

if you want to display both columns, change the above to something like:

Column Widths: 0.5cm; 2cm (because PK often doesn't take up too much space).

That will just display both columns when you click to select a value. Combo boxes after selected will only show the first visible column so if the OP wants the second column to show they will need to switch the second column with the first in the underlying query and have that be first.

 

[wiklendt]

That will just display both columns when you click to select a value. Combo boxes after selected will only show the first visible column so if the OP wants the second column to show they will need to switch the second column with the first in the underlying query and have that be first.

or cosmarchy can elect to switch back to listbox (the selected item is highlighted) - which can display as many columns as he wants. i've explained to him in my previous post which column should be what.

what i do in my forms, other than use a listbox, is have the bound form display the selected item in big, friendly, bright letters - just to remind the user what they are actually looking at without having to scan the listbox. here's a snapshot of one of my forms...