How to select an item in a listbox?

[Mar]

Hi, I am using access 2000 on XP (with Intuitive's ERP) and have a data entry
form with 3 combo boxes that make up the primary key. When something is
selected in the first one, the second one becomes populated. sometimes with
only one value. When something is selected in the second one, it populates
the third one, again, possibly with one value. If there is only one value in
the listbox I would like to select it programmatically because it doesn't
make sense to force the user to select an item from a list of one. The
problem is I get an error saying:"You've used the ListIndex property
incorrectly." (during the AfterUpdate event). But if i put an On Error
Resume Next in there, it works on the second combobox, then fails on the
third with "Microsoft Access can't move the focus to the control cboLineItem.
" and it doesn't work. I'm at the point where I am going to force them to
live with it, but I think that is a poor approach. Is Access incapable of
selecting an item in a combobox? If not then how? Any assistance is
appreciated, I am a VB programmer with little access experience on the front
end.

STARTS HERE:

Private Sub cboVendorID_AfterUpdate()

Dim strProcName As String

On Error GoTo Err_Handler

strProcName = "cboVendorID_Change"

SelectVendor

Exit_Procedure:
On Error Resume Next
Exit Sub

Err_Handler:
Call mobjEventInfo.NewEvent(mstrObjectName, strProcName, _
Err, Error, mcnxADO)
mobjEventInfo.DisplayMessage
Resume Exit_Procedure

End Sub

Private Sub SelectVendor()

meState = evqRFQID_VendorID
cboLineItem.RowSource = "SELECT DISTINCT LineItem FROM " &
mconRFQVendorTable & " WHERE RFQID = " & cboRFQID
EnableControls
If cboLineItem.ListCount = 1 Then 'Only 1 Line Item, select it
SelectSingleItem cboLineItem
SelectLineItem
End If

End Sub

Private Sub SelectSingleItem(cbo As ComboBox)

With cbo
On Error Resume Next
.SetFocus '<= Error "Microsoft Access can't move the focus
to the control cboLineItem."
.ListIndex = 0 '<= Error "You've used the ListIndex property
incorrectly." occurs here
End With

End Sub

 

[Dirk Goldgar]

Hi, I am using access 2000 on XP (with Intuitive's ERP) and have a data
entry
form with 3 combo boxes that make up the primary key. When something is
selected in the first one, the second one becomes populated. sometimes
with
only one value. When something is selected in the second one, it
populates
the third one, again, possibly with one value. If there is only one value
in
the listbox I would like to select it programmatically because it doesn't
make sense to force the user to select an item from a list of one. The
problem is I get an error saying:"You've used the ListIndex property
incorrectly." (during the AfterUpdate event). But if i put an On Error
Resume Next in there, it works on the second combobox, then fails on the
third with "Microsoft Access can't move the focus to the control
cboLineItem.
" and it doesn't work. I'm at the point where I am going to force them to
live with it, but I think that is a poor approach. Is Access incapable of
selecting an item in a combobox? If not then how? Any assistance is
appreciated, I am a VB programmer with little access experience on the
front
end.

STARTS HERE:

Private Sub cboVendorID_AfterUpdate()

Dim strProcName As String

On Error GoTo Err_Handler

strProcName = "cboVendorID_Change"

SelectVendor

Exit_Procedure:
On Error Resume Next
Exit Sub

Err_Handler:
Call mobjEventInfo.NewEvent(mstrObjectName, strProcName, _
Err, Error, mcnxADO)
mobjEventInfo.DisplayMessage
Resume Exit_Procedure

End Sub

Private Sub SelectVendor()

meState = evqRFQID_VendorID
cboLineItem.RowSource = "SELECT DISTINCT LineItem FROM " &
mconRFQVendorTable & " WHERE RFQID = " & cboRFQID
EnableControls
If cboLineItem.ListCount = 1 Then 'Only 1 Line Item, select it
SelectSingleItem cboLineItem
SelectLineItem
End If

End Sub

Private Sub SelectSingleItem(cbo As ComboBox)

With cbo
On Error Resume Next
.SetFocus '<= Error "Microsoft Access can't move the
focus
to the control cboLineItem."
.ListIndex = 0 '<= Error "You've used the ListIndex property
incorrectly." occurs here
End With

End Sub

Try this:

'------ start of revised code ------
Private Sub SelectSingleItem(cbo As ComboBox)

With cbo
On Error Resume Next
.Value = .ItemData(0)
End With

End Sub

'------ end of revised code ------

 

[Jeanette Cunningham]

You can do it like this-->

meState = evqRFQID_VendorID
cboLineItem.RowSource = "SELECT DISTINCT LineItem FROM " &
mconRFQVendorTable & " WHERE RFQID = " & cboRFQID
EnableControls
If Me.cboLineItem.ListCount = 1 Then 'Only 1 Line Item, select it
Me.cboLineItem = Me.cboLineItem.ItemData(0)
End If



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Mar via AccessMonster.com]

Thanks for your quick response.
Unfortunately it does not work. I fill the combobox with the rowsource
property and apparently Access doesn't populate the itemdata property. Is
there a way to have Access do this? As it is now the Itemdata contains NULL,
so the combobox contains nothing after .Value = .ItemData(0).
But you did give me an idea, so I tried this:

Private Sub SelectSingleItem(cbo As ComboBox)

With cbo
' On Error Resume Next
' .SetFocus
' .ListIndex = 0
.Value = .Column(0, 0)
End With

End Sub

Which works, however the combobox is blank on the screen, but referencing the
combobox (cbo in above example) will return the data. The blank combobox can
confuse people any idea on how to fix it, or any other way to select an item
then the way I used, perhaps my way is a poor choice? Or is there a way to
get Access to populate the ItemData property so I can use the previously
mentioned solution? It must work somehow since it was suggested. Thanks for
looking.