How to set default value for a comboBox in MS Access 2000

  • Thread starter Thread starter James P.
  • Start date Start date
J

James P.

Hello there,

I need to create a comboBox named cboStatus as follow:

1) If the user is a manager, display only one status in the comboBox
as "CLOSED" and the default value shown is "CLOSED".

2) If the user is employee, display status with two values "OPEN" and
"PENDING", and the default value shown is "OPEN"

In my design, I set the Row Source Type as "Value List." In the code
in my Form_Open event, I tried different combinations:

If Manager Then
Me.cboStatus.AddItem ("CLOSED")
cboStatus.ItemData(0) = "CLOSED"
Else
cboStatus.RowSource = "OPEN;PENDING"
cboStatus.DefaultValue = "OPEN"
End If

I also tried to set default value with: cboStatus.value =
cboStatus.column(0,0).

So far, the best I got was be able to get the list of values into the
comboBox, but the comboBox shown empty because the default value for
it does not work yet. Either it said type mismatch or can't assign
value to it.

I've been working on this problem for two days and haven't been able
to figure it out. Please help.

Thanks a lot,
Jim
 
James,

Your combobox is unbound, right?

If so, to position the combobox to display a value in the itemdata, you must
set the value of the combobox. You don't set the .DefaultValue. That's
only if it is bound...

Here's a utility function I use that demonstrates the call in a useful way:

Public Function ACComboBoxSelectFirstItemData(cbo As Access.ComboBox) As
Boolean
'PURPOSE: select the first row of data in the comboboxes, if any, being
sensitive to where it has column heads.
'RETURN: TRUE only if there was data to set it to.
If cbo.ListRows > Abs(cbo.ColumnHeads) Then
cbo = cbo.ItemData(Abs(cbo.ColumnHeads))
End If
End Function

Cheers,

Malcolm Cook
 
One other simple sollution might be to design 2 combo boxes with the
properties and defaults set. Then on the form on open event use code to see
which one to display depending on if its a manager or not.

If Manager Then
Me.cboManager.Visible = True
Me.cboOther.Visible = False
Else
Me.cboManager.Visible = False
Me.cboOther.Visible = True
End If
 
Reggie said:
One other simple sollution might be to design 2 combo boxes with the
properties and defaults set. Then on the form on open event use code to see
which one to display depending on if its a manager or not.

If Manager Then
Me.cboManager.Visible = True
Me.cboOther.Visible = False
Else
Me.cboManager.Visible = False
Me.cboOther.Visible = True
End If
Click to expand...

Malcolm,
Thanks a lot for responding. I tried your solution but still got the
"Type mismatch" message. Maybe I did not put it right. This is what
I have:
If Manager Then
Me.cboStatus.AddItem ("CLOSED")
cboStatus = cboStatus.ItemData(Abs(cboStatus.ColumnHeads))
End if
Any suggestion?

Reggie,

I like your solution a lot because it's simple. The only thing is in
my program, I use Status field in a lot of places. Use two status
means I have to double it every where. But if I can't find a better
solution, I know what I have to do.
Thanks a lot for responding too. If you can think of any thing else,
please let me know.
Jim
Jim
 
James, One other thing you could do. Since it appears from your description
that if it's a manager then it will always be Closed and they can't change
it. If on the other hand it's an employee they can choose either Open or
Pending. You could design the combo box as you've described, but hard-code
the value list to Open;Pending as the rowsource. Then run something like
this on opening the form


Private Sub Form_Open(Cancel As Integer)
If Manager Then
Me.cboStatus = "Closed"
Me.cboStatus.Locked = True
Me.cboStatus.Enabled = False
Else
Me.cboStatus = "Open"
End If
End Sub
 
James:

If Manager Then
cboStatus.RowSource = "CLOSED"
Else
cboStatus.RowSource = "OPEN;PENDING"
End If
cboStatus.value = cboStatus.ItemData(Abs(cboStatus.ColumnHeads))

??

-Malcolm
 
Malcolm Cook said:
James:

If Manager Then
cboStatus.RowSource = "CLOSED"
Else
cboStatus.RowSource = "OPEN;PENDING"
End If
cboStatus.value = cboStatus.ItemData(Abs(cboStatus.ColumnHeads))

??

-Malcolm
Click to expand...

Reggie and Malcolm,

Again, thanks a lot for helping me. Malcolm came up with a good
question in the beginning: is it a bound column. Indeed, it is.
Therefore, I can't set value for a bound column's value. Instead, I
had to set value for the column's text. This is what I got to make it
work, finally :

cboStatus.RowSource = "OPEN;PENDING;SUSPEND;"
cboStatus.SetFocus
cboStatus.Text = "OPEN"

What a relieve! I'm thrilled with the response from you guys, helpers
out there. Appreciate it.

James
 
Back
Top