unable to set a default value to multi-column combo box

[Frank]

Hi

I have a 2-column combo box "cboCourseID", it has two columns: "course
id"
and "Course title". The bound column is "course id" and it is hidden
(its width is set to 0). So the user will see only the course title.

I was unable to set the default value to this combo box. The code
section is shown below. When I run the code it stops at the line
"Me.cboCourseID.Value = Me.cboCourseID.ItemData(0)"
and showed the error message "You can't assign a value to this object".


The sql works fine. It returns all courses run by a department, and
accDept is
the department code. If the line
"Me.cboCourseID.Value = Me.cboCourseID.ItemData(0)"
is commented out., the section of code works fine except that the
default value was shown as the value left by previous run (which I do
not want).

The similar code works for 1 column combo box. I would be very grateful
if someone can help me to show me where is wrong. Thanks.


Private Sub Form_Open(Cancel As Integer)

Dim sql As String

'Loading a list of courses offered by dept
Me.cboCourseID.RowSourceType = "Table/Query"

sql = "SELECT [Course Dept Table].[course id], [Course Info
Table].Title "
sql = sql & "FROM [Course Dept Table] INNER JOIN [Course Info
Table] ON "
sql = sql & "[Course Dept Table].[course id] = [Course Info
Table].[course id] "
sql = sql & "WHERE ((([Course Dept Table].Dept) = "
sql = sql & "'" & accDept & "')) ORDER BY [Course Dept
Table].[course id];"

Me.cboCourseID.RowSource = sql
Me.cboCourseID.Value = Me.cboCourseID.ItemData(0)


End Sub

 

[John Vinson]

"Me.cboCourseID.Value = Me.cboCourseID.ItemData(0)"

Make it

Me.cboCourseID.DefaultValue

instead and you should be fine.

John W. Vinson[MVP]

 

[Frank]

John

Thank you for your help. I tried to use

Me.cboCourseID.DefaultValue = Me.cboCourseID.ItemData(0)

no error message was shown up (it is good!), however, the default value
is set properly. It is always the value left over by the previous run,
it is not the first value in the current list (the drop down list is
fine). I must do something wrong...

Frank

 

[John Vinson]

John

Thank you for your help. I tried to use

Me.cboCourseID.DefaultValue = Me.cboCourseID.ItemData(0)

no error message was shown up (it is good!), however, the default value
is set properly. It is always the value left over by the previous run,
it is not the first value in the current list (the drop down list is
fine). I must do something wrong...

Frank

I thought that's what you were TRYING to do. Could you explain what
you want as the default value? If it's always the same value you need
no code at all - just set the DefaultValue property in form design
view!

John W. Vinson[MVP]

 

[Frank]

John

Excuse me for being not able to explain my problem clearly. What I want
to do is to extract a list of course IDs from the database. Those
courses are offered by a department. Since the user can be from
different departments so the list is changing. I need to set the first
course id in the list as the defaul value for the combo box. For
example if the department is Mathematics, the list of its courses can
be ALG 20101, NUM 10102, DIF 30101..., then the first course id "ALG
20101" should be shown at the combo box. The reason that I am using 2
column combo box is that the user will see both the course id and
course title such as "ALG 20101, Modern Algebra", "NUM 10102, Intro to
Numerical Analysis", etc.

Perhaps my code is correct (since it works in other forms) while I made
some mistakes elsewhere. I am checking the code and form design again.

You mentioned that I can just set the DefaultValue property in form
design, I do not know how to do that since the default value is
changing.

Thank you so much for your help!

Frank