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.OpenReco rdset("MyTable" , dbOpenDynaset)

If Not rsMyRS.EOF Then rsMyRS.MoveFirs t
Do While Not rsMyRS.EOF
lstRecords.AddI tem rsMyRS!Name
lstRecords.Item Data(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.