recordset will not assign column values listbox columns

[psuaudi]

I am trying to run through a tutorial on recordsets, so I am following the tutorial here:

http://www.devdos.com/vb/lesson4.shtml



I have made the table as described, and tried to run this code:



Option Compare Database
Dim dbMyDB As Database
Dim rsMyRS As Recordset



Private Sub Form_Load()


Set dbMyDB = CurrentDb
Set rsMyRS = dbMyDB.OpenRecordset("MyTable", dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirst
Do While Not rsMyRS.EOF
lstRecords.AddItem rsMyRS!Name
lstRecords.ItemData(lstRecords.NewIndex) = rsMyRS!ID
rsMyRS.MoveNext
Loop

End Sub


However, I am getting an error with the .newindex command. I assume what this line of code is trying to do is assign the ID number of the Name field to the corresponding listbox entry. Is there another way to do it?

Thank you!

[MMcCarthy]

Are you designing in VB or VBA. The following code appears to be Visual Basic. If that's the case then you will have to post this question in the Visual basic forum.

If however, you are using Access forms and VBA let me know more details of the form, listbox and the table you mentioned.

Mary

[psuaudi]

I am using VBA. All I have is a simple form that has the listbox that is tied to a table with 3 fields: ID(autonumber), Name(text), and Phone(text).

I'm simply doing this as an exercise to try to learn more about recordsets.

[NeoPa]

I am using VBA. All I have is a simple form that has the listbox that is tied to a table with 3 fields: ID(autonumber), Name(text), and Phone(text).

I'm simply doing this as an exercise to try to learn more about recordsets.

I'm sorry.
I tried to look at the tutorial and found the code.
Unfortunately I couldn't find anything in Help that dealt with .AddItem or the .NewIndex value.
Maybe ask at the site that has the tutorial.
Good for you to be doing what you're doing though. If you find you can't get on with that, we do have links in the Access section to tutorials and helpful explanations for various items including RecordSet processing.

[MMcCarthy]

I am using VBA. All I have is a simple form that has the listbox that is tied to a table with 3 fields: ID(autonumber), Name(text), and Phone(text).

I'm simply doing this as an exercise to try to learn more about recordsets.

The code you are using, .AddItem and .NewIndex are VB functions. It is done differently in VBA.

See below ...

If List Row Source Type is a value list:

Private Sub Form_Load()
 Dim dbMyDB As Database
 Dim rsMyRS As Recordset
Dim tempList As String
 
   Set dbMyDB = CurrentDb
   Set rsMyRS = dbMyDB.OpenRecordset("MyTable", dbOpenDynaset)
 
   If Not rsMyRS.EOF Then rsMyRS.MoveFirst
   tempList = Me.lstRecords.RowSource
   Do While Not rsMyRS.EOF
      tempList = tempList & ";" &   rsMyRS!ID & ";" &  rsMyRS!Name
      rsMyRS.MoveNext
   Loop
 
   lstRecords.RowSource = tempList
 
End Sub
 

If List Row Source Type is a Table/Query you don't actually use recordsets:

 
Private Sub Form_Load()
 Dim strSQL As String
 
   strSQL = "SELECT [ID], [Name] FROM MyTable"
   Me.lstRecords.RowSource = strSQL
   Me.lstRecords.Requery
 
End Sub
 

Mary

[psuaudi]

I guess I am still struggling with using recordsets and perhaps some of the more intimate details of using combolists. I'm not sure what direction I should head in next.

[NeoPa]

I guess I am still struggling with using recordsets and perhaps some of the more intimate details of using combolists. I'm not sure what direction I should head in next.

Links to useful sites is a good place to start.