CThomp2005
asked on
Populating a list box on a continuous forms form
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!
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks again for your time and input! Hope to run into you guys again during my next quandry.
You have a much better grasp on that than I do, that's for sure. I'm not sure I could help too much on the Excel VBA front -- other than experience I have doing some basic automation, I know very little. However, EE's Excel zone is swarming with top experts -- something like 3 or 4 Microsoft MVPs, I think -- so you should have very little problem going that route, if that's what you decide to do.
However, if you have an Access question, I'd always be happy to assist.
However, if you have an Access question, I'd always be happy to assist.
ASKER
As the customer is already, using an Excel spreadsheet for doing this (and since Excel is pretty "freeform"), I'm kinda dropping the "do it Access" idea and moving more towards an Excel solution. Having been doing *a lot* of VBA coding in Excel these days, I'm piecing together a structured sheet with custom user forms, tied via DAO to an mdb that it will read in current data from and write the final results to (this will be reviewed by users of the mdb in question). (BTW - I'm not much good with ADO, but have a good handle on DAO). Preliminary testing looks pretty positive, and I'm hoping that I can keep it "freeform" enough for them to use, while still keeping it structure enough for the VBA to process it. Time will tell. (Either of you do any Excel coding - in case I run into problems/questions?)
Thanks very much, again, for your responses.