On Current Event on a Continous form

[Javawag]

On Current Event on a Continuous form

Hi,

I have a "continuous forms" Form set up which shows a listbox for each person in my database (well, I only put one in, called lstJobs, but as you know, on a continuous form it shows up once for each record instead of just once). I was hoping to populate said listbox with lstJobs.AddItem in the Form_Current event, thinking this event would fire multiple times as the form shows multiple records at once. However! That isn't the case; instead it shows the listbox once for each person (as it should), but each list is the same, and each contains the data for just the first person in the database, not a different listbox for each.

Help!
- Javawag

 

[Kafrin]

You can't have an unbound control that simultaneously shows different things for different records. You'd need the ControlSource for your box to be bound to something - a field, or a function that calls a field as a parameter - in order for it to display different things for each record.

 

[ajetrumpet]

You can't have an unbound control that simultaneously shows different things for different records.

only on single or split forms. not continuous ones.

 

[Javawag]

Right. So, if i set the RowSource (or is it ControlSource? One of them!) to an SQL statement/Query, will that show different values properly?

Thanks for the quick reply!
- Javawag

 

[Kafrin]

Actually Aje, even then the control is not showing different things simultaneously, you just can't see it on the other records to know that it's the same.

 

[Kafrin]

No, setting the RowSource won't solve this. It's the ControlSource that needs to be bound. So if your list values all link to the ID of your record, you would bind it to this (I think, it's difficult to worl out without seeing it).

 

[ajetrumpet]

Actually Aje, even then the control is not showing different things simultaneously, you just can't see it on the other records to know that it's the same.

I've done enough of this stuff to be able to ignore comments like these Kafrin. Thank you for your input. We're here to help, not philosphise. I'm out of this thread now. good luck to the OP!

 

[Javawag]

No, setting the RowSource won't solve this. It's the ControlSource that needs to be bound. So if your list values all link to the ID of your record, you would bind it to this (I think, it's difficult to worl out without seeing it).

Yeah, the continuous form shows a record for each staff member in the database (so it should be bound to StaffID I imagine? It's a primary key/autonumber id field...). So, I've set my ControlSource to that now, how do I get the listbox to run the query using the StaffID as an argument? The problem here is also that the query takes 2 other arguments - start date and end date. I can paste the SQL statement if that helps?

- Javawag

 

[Kafrin]

Your SQL should now be the RowSource. The date parameters should be controls on a form (at a guess).

By the way, I apologise if this doesn't work out right!

 

[Javawag]

Your SQL should now be the RowSource. The date parameters should be controls on a form (at a guess).

By the way, I apologise if this doesn't work out right!

It's ok, I've taken a backup .
Ok, I've thrown my SQL into the RowSource. The date arguments being controls could be done, that sounds sensible. The problem I'm having now is that it's now showing all the records - it's not linking the StaffID in the SQL statement with the StaffID of the current record. I did change the SQL statement to say "WHERE StaffID = StaffID", but quickly realised how ridiculous that was!!

The statement:
SELECT tblJobs.JobID, tblJobs.Title, tblServices.ServiceName, getelementname([AllocElementID]) AS ElementName, Getminutesdone([tblJobs.jobid],[allocelementid],[staffid]) AS MinsDone, tblStaffJobs.StaffID, tblStaffJobs.AllocDate, getjobclosedate([tbljobs].[jobid]) AS CloseDate
FROM (tblJobs INNER JOIN tblStaffJobs ON tblJobs.JobID = tblStaffJobs.JobID) INNER JOIN tblServices ON tblJobs.ServiceID = tblServices.ServiceID
WHERE (((tblStaffJobs.AllocDate)<#6/1/2009#) AND ((getjobclosedate([tbljobs].[jobid]))>#1/1/2009#));

It looks like a lot, but it's quite easy, the select clause just uses a lot of functions so it looks crazy. The from clause selects and links the tables (as you'll know) and the where clause (for now) sets the start and end date values - which are hardcoded for the minute.

Thanks,
- Javawag

 

[Kafrin]

OK, I'm struggling myself now. I want to say that your SQL StaffID should equal your form StaffID, but I'm fairly sure that'll put you right back where you started. Give me a minute, I'll have a think!

 

[Kafrin]

OK, time to go back a few steps and establish exactly what you're trying to do. It may be that a list box isn't the answer. So, questions:

1) What is this list box for?
2) Why does it need to be different for each row?
3) Does it need to show multiple items, or will it only show one item per record?
4) Do the items need to be selectable, or do they just need to be visible?

 

[Javawag]

OK, time to go back a few steps and establish exactly what you're trying to do. It may be that a list box isn't the answer. So, questions:

1) What is this list box for?
2) Why does it need to be different for each row?
3) Does it need to show multiple items, or will it only show one item per record?
4) Do the items need to be selectable, or do they just need to be visible?

1. The list box shows a list of allocated to a staff member (more precisely, it finds jobs that were allocated before the "end date" parameter and which either have not closed or closed after the "start date" parameter - so all current jobs for a given staff member in the timeframe provided)
2. The form is being used kind of like a report - I need it to show all the staff members at once, with each member having their own ListBoxes full of the jobs allocated to them.
3) It'll show one item per record in the tblJobs table - but there will obviously be multiple jobs for one given staff member (hence the ListBox to show them)
4) They don't need to be selectable - as long as they're all visible, thats fine.

Thanks so much!
- Javawag

 

[Kafrin]

Does it need to be a form? A report would be easier - you can either add grouping at the Staff level or have a subreport. Unfortunately you can't put subforms on continuous forms.

 

[Javawag]

Does it need to be a form? A report would be easier - you can either add grouping at the Staff level or have a subreport. Unfortunately you can't put subforms on continuous forms.

I did try a report but I couldn't seem to get the grouping to work properly. Perhaps you could explain how to group records by Staff member? The only real reason for using a form was that I thought I could populate the ListBox using VBA, which I find easier to do than using reports - but if it has to be a report, that's fine too really.

Thanks,
- Javawag

 

[boblarson]

Does it need to be a form? A report would be easier - you can either add grouping at the Staff level or have a subreport. Unfortunately you can't put subforms on continuous forms.

Well, technically you can (as Rich will point out - he did to me a few years ago). It just has to be in the footer and not in the details section.

 

[Kafrin]

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.

 

[Kafrin]

Well, technically you can (as Rich will point out - he did to me a few years ago). It just has to be in the footer and not in the details section.

Didn't know that, handy. Unfortunately not very useful for this problem, but definitely worth knowing.

 

[boblarson]

Didn't know that, handy. Unfortunately not very useful for this problem, but definitely worth knowing.

Well, maybe for trivia I have yet to find an actual use for it.

 

[Rich]

You can't have an unbound control that simultaneously shows different things for different records.

That's not strictly true actually