ComboBox Setting Value

[JohnBoy2005]

How can I set the text value of a combo box, based on its itemdata value

e.g.

'initial populate of combo box

Dim rstSubject As Recordset
Set rstSubject = dbs.OpenRecordset("SELECT * FROM tblSubjects ORDER BY Subject ASC")
rstSubject.MoveLast
rstSubject.MoveFirst
Do Until rstSubject.EOF
cboSubject.AddItem UCase(rstSubject("Subject"))
cboSubject.ItemData(cboSubject.NewIndex) = rstSubject("Subject_ID")
rstSubject.MoveNext
Loop

'Set data in combobox

cboSubject = the subject_id from record in database

I've tried
cboSubject.ItemData(cboSubject.ListIndex) = rst("Subject_ID") but it returns the wrong value

Cheers

John

 

[Bluejay07]

Perhaps your NewIndex is not functioning properly or you require .fields. You could try

'Load the combo box.
cboSubject.Clear
rs.MoveFirst
i = 0
Do Until rs.EOF
   cboSubject.AddItem UCase(rstSubject.fields("Subject"))
   cboSubject.ItemData(i) = rstSubject.fields("Subject_ID")
   rs.MoveNext
   i = i + 1
Loop

...

'Set the list index of the combobox.
For i = 0 To cboSubject.ListCount - 1
   If cboSubject.ItemData(i) = rstSubject.fields("Subject_ID") Then
      cboSubject.ListIndex = i
      Exit For
   End If
Next i

I hope this helps.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

As I was modifying code, please change rs to rstSubject.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Andrzejek]

You are going to get an error (probably) on teh RED line of code:

[green]'Set the list index of the combobox.[/green]
[blue]
rsSubject.MoveLast[/blue]

For i = 0 To cboSubject.ListCount - 1
   [red]If cboSubject.ItemData(i) = rstSubject.fields("Subject_ID") Then[/red]
      cboSubject.ListIndex = i
      Exit For
   End If
Next i

Since the previous Loop made rsSubject point to EOF and not the last record in rsSubject. That's why you may want to add the BLUE line of code.

Just a guess, code not tested.

Have fun.

---- Andy

 

[JohnBoy2005]

Thanks guys, sorted it with a combination of mine and both your code

Cheers