Select Rows in Listbox

[Hodges]

Heya, I've got a listbox set to simple multi-select that I want to select rows on form-load based on values in a table (tblStaffBooking).

The table (tblStaffBooking) has BookingID and StaffID.

The listbox's rowsource is this statement:

SELECT [FirstName], [LastName], [ID] FROM tblStaff WHERE ([NotEmployed]=False) ORDER BY [LastName];

I want something like: if a row in (tblStaffBooking) has [BookingID] = " & Me.txtBookingID.Value & " then select the corresponding row on the listbox that has an identical StaffID (column 2 on listbox).

Reckon you could help me?? Need this for A-level coursework :/ There's this, then designing a weekly calendar in a report

Thanks!

 

[Hodges]

bump? - sorry I'm desparate

 

[whatever]

I can think of a couple of ways to do this both involve stepping through the rows in the list box and selecting if it matches either:

1. A record in another recordset i.e a query of all those records with a booking, or
2. Change the source of the list box to have an indicator if the Id has any bookings either way you need to do something like this


Private Sub Form_Open(Cancel As Integer)

Dim ctlSource As Control
Dim intcurrentrow As Integer

'dim myrs as recordset

'open myrs that contains the records you want to match
set myrs = ????

'move to the first record in myrs

'myrs.movefirst

'do while not myrs.eof 'loop round each record and then go through each item in the list box and select it if they match

Set ctlSource = Me.LstExample

'open recordset of those records to match

For intcurrentrow = 0 To ctlSource.ListCount - 1

If ctlSource.Column(2, intcurrentrow) = myrs!matchfield Then ' if the list matches the record then select.

ctlSource.Selected(intcurrentrow) = True

End If

Next intcurrentrow

'loop ' through the records


End Sub

Hope this gives you the right steer without doing your coursework for you


HTH

 

[Hodges]

Ok I've got this so far but I'm struggling to get it working, there's still something wrong...(I believe all object references are correct)

Private Sub Form_Open()

    Dim lst As ListBox
    Dim i As Integer
    Dim conDatabase As ADODB.Connection
    Dim strSQL As String

Set conDatabase = CurrentProject.Connection
    Set lst = Me!lstStaff
    For i = 0 To lst.ListCount - 1
        strSQL = "SELECT [StaffID] FROM tblStaffBooking WHERE ([BookingID] = " & Me.txtBookingID.Value & ")"
        conDatabase.Execute strSQL
        If strSQL = lst.Column(2, i) Then
        lst.Selected(i) = True
        End If
    Next i
    
    conDatabase.Close
    Set conDatabase = Nothing
    
End Sub

 

[whatever]

Nearly there, but you need to open the SQL as a recordset then step through each row in the recordset then compare against all the rows in the list box. If you get stuck PM me.

 

[Hodges]

Ok this is what I've got so far:

Private Sub Command39_Click()

Dim lst As ListBox
Dim i As Integer
Dim rstPeriod As DAO.Recordset
Dim strSQL As String
Dim db As Database

Set lst = Me!lstStaff
Set db = CurrentDb()
strSQL = "SELECT StaffID FROM tblStaffBooking WHERE (((tblStaffBooking.BookingID)=" & Me!ID & "))"

Set rstPeriod = db.OpenRecordset(strSQL)

With rstPeriod
rstPeriod.MoveFirst
Do Until rstPeriod.EOF()
For i = 0 To lst.ListCount - 1
If lst.Column(2, i) = rstPeriod![StaffID] Then
lst.Selected(i) = True
End If
Next i
rstPeriod.MoveNext
Loop
rstPeriod.Close
End With

Set rstPeriod = Nothing
MsgBox "Done!"

End Sub

Can anyone help me further?

 

[whatever]

Make sure you have a reference set to DAO and try this:

Private Sub cmdSelectList_Click()

On Error GoTo Proc_Err

Dim mydb As DAO.Database
Dim rstPeriod As DAO.Recordset

Dim lst As ListBox
Dim intPlaceHolder As Integer
Dim strSQL As String

Set lst = Me.lstStaff
Set mydb = CurrentDb()
strSQL = "SELECT StaffID FROM tblStaffBooking WHERE (((tblStaffBooking.BookingID)=" & Me.txtBookingID & "))"

'View the SQL in the immediate window
Debug.Print strSQL

Set rstPeriod = mydb.OpenRecordset(strSQL)

'With rstPeriod if you want to use "with" keyword then you dont need to reference the object again
'so the next line would become .MoveFirst

rstPeriod.MoveFirst

Do Until rstPeriod.EOF()

For intPlaceHolder = 0 To lst.ListCount - 1
'if you dither over the value of lst.column in the next row you will see it is a string so we convert the criteria.
'NB note change in column of ID
If lst.Column(0, intPlaceHolder) = CStr(rstPeriod![StaffID]) Then

lst.Selected(intPlaceHolder) = True

End If

Next intPlaceHolder

rstPeriod.MoveNext

Loop

rstPeriod.Close

Proc_Exit:

Set rstPeriod = Nothing
Set mydb = Nothing

Exit Sub

Proc_Err:

MsgBox Err.Number & ", " & Err.Description
Resume Proc_Exit

End Sub

I have this working in a small test db if you want it.


HTH


Kevin

 

[Hodges]

works a treat, thank you so much!