On Current Event on a Continous form

[Javawag]

For a report, build the report based on your Jobs table. Use the Wizard. At the step that asks you if you want to add any grouping levels, choose StaffID to group on. Finish the Wizard. This should give you a report with sub headings of Staff (you can use a combo box bound to StaffID with the Staff table behind the RowSource to show the staff name instead of the ID). Under each staff subheading will be a list of jobs for that staff member.

Alternatively, on your form I can write a function that will allow you to show your list of jobs in a text box:

Public Function JobList(Staff As Long) As String
    Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM JobsTable WHERE StaffID = " & Staff & ";")
    JobList= ""
    If rs.RecordCount <> 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            If JobList<> "" Then JobList= JobList& Chr(13) & Chr(10)
            JobList= JobList & rs!JobName
            rs.MoveNext
        Loop
    End If
    rs.Close
End Function

You then put a text box on your form with a control source:
=JobList([StaffID])

By the way, there will be slight adjustments if StaffID is not an auotnumber.

Wow, thanks!

I actually ended up half doing that and half recycling my SQL from the form, it seems to work fine though! The problem now is, how do I pass the start and end date values to the report? I know I can set fromDate and toDate as global variables and have a function (i.e. getFromDate()) that returns them for use in the SQL statement, but is there an easier and/or better way to do this?

Thanks for all your help,
-Javawag

 

[gemma-the-husky]

the thing is, what is the listbox/combobox trying to do

if its not bound, it will show the same data, but more importantly it wont DO anything.

it will also be wasting your screen space - i find continuous forms with more than at most 2 lines of screen space really irritating - i try to restrict them to single lines only.