List box selection into combo box

[h2fcell]

I’ve had no luck locating a prior post for this scenario. I’m using Access
2007 and I have a form with a combo box that populates data in another combo
box and a list box using the following code.

Private Sub cboBookingID_AfterUpdate()
Me.cboServiceIssue.RowSource = "SELECT DISTINCT
tblCRMInvoicedBooking.item_type " & _
"FROM tblCRMInvoicedBooking " & _
"WHERE [booking_id] =
[Forms]![frmComplaintsEntry]![cboBookingID] " & _
"ORDER BY tblCRMInvoicedBooking.item_type;"

Me.lboItems.Requery
End Sub

The lboItems Row Source property has the below query.

SELECT tblCRMInvoicedBooking.item_type, tblCRMInvoicedBooking.item_description
FROM tblCRMInvoicedBooking
WHERE
(((tblCRMInvoicedBooking.booking_id)=[Forms]![frmComplaintsEntry]![cboBookingID]))
ORDER BY tblCRMInvoicedBooking.item_type;

The list box “Bound Column†property = 2 , this I need and can’t change.
After a user selects a row in the list box, I would like combo box
“cboServiceIssue†to equal the first column “item_type†of that selection.

I figure I need to add code to the “After Update†property of the list box
but I don’t know how to refer to the first column in the list box in SQL.

As usual any help is appreciated.
Thanks.

 

[Arvin Meyer [MVP]]

Columns in combo and list boxes are referred to in code like:

ComboName.Column(0) ' column 1
ComboName.Column(1) ' column 2
ComboName.Column(2) ' column 3

and so on. I would not change the bound column property, instead use the
code about in your query.

 

[h2fcell]

Thank you very much Arvin. That's exactly what I needed.
Have a Great New Year!

Columns in combo and list boxes are referred to in code like:

ComboName.Column(0) ' column 1
ComboName.Column(1) ' column 2
ComboName.Column(2) ' column 3

and so on. I would not change the bound column property, instead use the
code about in your query.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

I've had no luck locating a prior post for this scenario. I'm using
Access
2007 and I have a form with a combo box that populates data in another
combo
box and a list box using the following code.

Private Sub cboBookingID_AfterUpdate()
Me.cboServiceIssue.RowSource = "SELECT DISTINCT
tblCRMInvoicedBooking.item_type " & _
"FROM tblCRMInvoicedBooking " & _
"WHERE [booking_id] =
[Forms]![frmComplaintsEntry]![cboBookingID] " & _
"ORDER BY tblCRMInvoicedBooking.item_type;"

Me.lboItems.Requery
End Sub

The lboItems Row Source property has the below query.

SELECT tblCRMInvoicedBooking.item_type,
tblCRMInvoicedBooking.item_description
FROM tblCRMInvoicedBooking
WHERE
(((tblCRMInvoicedBooking.booking_id)=[Forms]![frmComplaintsEntry]![cboBookingID]))
ORDER BY tblCRMInvoicedBooking.item_type;

The list box "Bound Column" property = 2 , this I need and can't change.
After a user selects a row in the list box, I would like combo box
"cboServiceIssue" to equal the first column "item_type" of that selection.

I figure I need to add code to the "After Update" property of the list box
but I don't know how to refer to the first column in the list box in SQL.

As usual any help is appreciated.
Thanks.

.