Listbox recordset problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Listbox recordset problem

Author

Topic

chobo
Starting Member

11 Posts

Posted - 2004-03-31 : 16:48:05

Hi, I am using access 2000 to make a form that has a listbox with textboxes for each field that will be displayed in the listbox.

The idea is on the Form_Load Event set the recordsource for the listbox to open all fields in table.

Private Sub Form_Load()
Dim sql As String
lstPackageInfo.RowSource = ""
txtNonFlightPackageID.Enabled = False
sql = "select NonFlightPackageID from tblNonFlightPackageInfo"

With lstPackageInfo
.ColumnCount = 1
.ColumnWidths = "0.2764"""
.RowSource = sql
End With

End Sub

Which works and displays all the records in the listbox.

Now, when you click on a record in the listbox the values for each field get assigned to a textbox.

For example the ID field would be displayed in the txtID textbox.

The code I use for the listbox_click() method is this.

Private Sub lstPackageInfo_Click()
Dim con as ADODB.Connection
Dim rstinfo As New ADODB.Recordset
Dim sql As String
Dim selectionIndex As Integer
Dim text As String

txtNonFlightPackageID = ""

Set con = CurrentProject.Connection
sql = "select * from tblNonFlightPackageInfo"
rstinfo.Open sql, con

selectionIndex = lstPackageInfo.ListIndex
rstinfo.Move (selectionIndex)

txtNonFlightPackageID = rstinfo!NonFlightPackageID

Set rstinfo = Nothing

End sub

The problem I have is that the field values in the textboxes are different from the ones displayed in the listbox.
For example I click on an item in the listbox with an id of 358, which has a listbox.listindex of 1 (so it is the second record in the table) in the textboxes it will display a different record such as id 555 ( there is usually a difference of 4+ records separating the one I selected and one that is displayed.

Any help is much appreciated.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-03-31 : 19:12:25

I've never used this sort of technique before, but one option to try is to add an ORDER BY clause to both SELECT statements to ensure that the records are returned in EXACTLY the same order. Without the ORDER BY they might be coming back in different order.

Typically when I do things in Access I work with disconnected recordsets so I would build the listbox and store the Primary Key with each item and then use that to retrieve the one record I want into the text boxes rather than using techniques like rst.Move.

--------------------------------------------------------------
Find more words of wisdom at [url]http://weblogs.sqlteam.com/markc[/url]

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-31 : 20:58:11

You are waaaaaay over-complicating things.

On the listbox click event, all you have to do is say:


txtNonFlightPackageID.value = lstPackageInfo.value

That's it ! the value of lstPackageInfo is what is selected, unless you have the BoundCOlumn set to something other than 1 or more than 1 column in it.

Iis there something I'm missing? do you just want to the take the value from this listbox and copy it into another textbox? that's all you need to do.

your listbox can have multiple columns, too, if you need more than 1 value from the list to put into multiple textboxes. you can even hide the other columns as needed by setting the widths of those columns to 0. Or you can display a "description" column but have the value of your listbox be the internal primary key and keep it hidden. Let me know if you need to do this, I can help you out.

- Jeff

chobo
Starting Member

11 Posts

Posted - 2004-04-01 : 17:23:30

thx a lot! It works perfectly now. I completely forgot about the value method.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources