Populating a list box on a continuous forms form
Posted on 2008-06-16
Pretty basic setup, and I thought this would be "easy" - relatively... I'll boil out all the fat...
We have Projects, Foremen and Laborers. Each Foreman is assigned to one or more Projects (typically only one Project, but there may be overachievers....). Each Laborer is assigned to a Foreman (and through this, to the Project the Foreman is on.
The goal is to create a rudimentary scheduling system that lists the Projects (and associated Foreman), and the Laborers connected to that Foreman. The request is to see as many Projects at a time, so the user can scroll through the Projects, moving people as necessary.
To this end, I have a "continuous forms" form with the Project info and Foreman name. I am trying to use a list box to display all Laborers associated with the Foreman. To goal will be to allow the user to move Foreman (and thus, associated Laborers) between Projects. The moving part I can handle.
My problem is, of course, the list boxes only display the Laborers for the first(?) / last(?) Project/Foreman (we'll say *one* of the Foreman), instead of the Foreman in each individual Project. I've tried putting the SQL statement to load the list boxes in both the Form Open (no data appears in the lists) and the On Current (Laborers from *one* Foreman appear for all records). I can understand why these results are happening, but not how to fix it - get it to do what I want.
Form pulls Project/Foreman info from a query. The statement I'm trying to use to populate the list boxes for each Foreman in each Project is:
me.lstLaborers.RowSource = "SELECT txtLaborerName FROM tblLaborAssignment WHERE txtForemanName = '" & txtForeman & "'"
(The Foreman field names are correct - in the Project table it's "txtForeman" and in the LaborAssignment table it's "txtForemanName".)
How can I pull this off? If a list box isn't the way to go, I can deal with that, but I need to have the form be "continuous" - if at all possible.
Thanks *very much* for any assistance on this!