Adding rows to Multi select list box (1 Viewer)

[Keith]

I am trying to populate a multi select list with options available depending on room type. I can get either the Package or Rate on the list but not both, this indicates my strSQL is ok.
I have spent all day on this forum and the web but still have not succeeded so here I am, cap in hand, asking you wonderful people for help.

    Select Case Frame71()
    Case 1  'Single Room Selected
        Me.ctrListPackage.RowSource = ""
        Me.ctrListPackage.ColumnCount = 2
        y = "S"

        strSQL = "SELECT tblTariff.TariffRate, tblTariff.TariffPackage FROM tblTariff WHERE (((tblTariff.TariffRoomType)=" & Chr(34) & y & Chr(34) & "))"
        Set rs = CurrentDb.OpenRecordset(strSQL)
        With rs

            If Not .BOF And Not .EOF Then
                .MoveLast
                .MoveFirst
                While (Not .EOF)
                    Me.ctrListPackage.AddItem (rs.Fields("TariffPackage"))
                    'Me.ctrListPackage.AddItem rst!TariffPackage & ";" & rst!TariffRate
                    .MoveNext
                Wend
            End If
        End With
        Me.txtRoomType.Value = y
        Me.txtRoom.Value = DMax("RoomNumber", "tblRooms") + 1
        Me.txtOcc.Value = 1

 

[June7]

Instead of declaring, opening, looping recordset and building list with AddItem, just set the RowSource property and Requery the listbox.

Me.ctrListPackage.RowSource = "SELECT TariffRate, TariffPackage FROM tblTariff WHERE TariffRoomType ='S'";
Me.ctrListPackage.Requery

 

[Keith]

June7 I get a compile error with SELECT highlighted

 

[Keith]

Sorted thanks. The line I had commented out worked but I hadn't set the column width correctly

 

[June7]

Make sure the RowSourceType property is set to Table/Query.

Okay, you have it working but seems less efficient and unnecessary code.

 

[Keith]

Make sure the RowSourceType property is set to Table/Query.

Okay, you have it working but seems less efficient and unnecessary code.

I have taken your advice and used code to set the row source. As you said make sure the rowSourceProperty is set to Table/Query. I had it set to valuelist