By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

recordset will not assign column values listbox columns

P: 27
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!
Dec 11 '06 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 11 '06 #2

P: 27
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.
Dec 12 '06 #3

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 12 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  Dim dbMyDB As Database
  3.  Dim rsMyRS As Recordset
  4. Dim tempList As String
  5.  
  6.    Set dbMyDB = CurrentDb
  7.    Set rsMyRS = dbMyDB.OpenRecordset("MyTable", dbOpenDynaset)
  8.  
  9.    If Not rsMyRS.EOF Then rsMyRS.MoveFirst
  10.    tempList = Me.lstRecords.RowSource
  11.    Do While Not rsMyRS.EOF
  12.       tempList = tempList & ";" &   rsMyRS!ID & ";" &  rsMyRS!Name
  13.       rsMyRS.MoveNext
  14.    Loop
  15.  
  16.    lstRecords.RowSource = tempList
  17.  
  18. End Sub
  19.  
If List Row Source Type is a Table/Query you don't actually use recordsets:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Load()
  3.  Dim strSQL As String
  4.  
  5.    strSQL = "SELECT [ID], [Name] FROM MyTable"
  6.    Me.lstRecords.RowSource = strSQL
  7.    Me.lstRecords.Requery
  8.  
  9. End Sub
  10.  
Mary
Dec 12 '06 #5

P: 27
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.
Dec 12 '06 #6

NeoPa
Expert Mod 15k+
P: 31,186
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.
Dec 12 '06 #7

Post your reply

Sign in to post your reply or Sign up for a free account.