• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1115
  • Last Modified:

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!
  • 2
  • 2
2 Solutions
The way continuous forms work is that any changes that aren't bound -- i.e. a manual change of the rowsource based off a record -- will take effect on every Detail section.  Thus, you're only grabbing the textForemanName from one Detail section, and the list box is showing it throughout all of them.

The best workaround is to instead use a combo box (and thus no display), and set the GotFocus event of that combo box to reset the RowSource.  Thus, when you click to type or select someone, it'll change the rowsource properly.

Unfortunately, it requires some tinkering to behave.  For example, you might need to have the combo box's value get stored in a bound textbox control, and just use the combo box unbound, otherwise it might think the value isn't in the list (not sure about this).

Otherwise, I'd consider another route, such as breaking this into multiple forms.  It seems quite complex for a rudimentary DB, and don't take that as anything but a compliment -- I just think the UI might be overcomplicated.
As nexusnation has stated you can't use continous forms to show ongoing related data because events occur on a per record basis and if mircosoft allowed multirecord events in Access the potential for database overload doesn't bear thinking about.

I suppose one  possibility is to use a query which retrieves all workers associated with all formen and then base a form on that, but of course you will then encounter the problem of each forman/project etc being displayed multiple times

You might consider using a report which is really the way to go in this situation because at least these are static continuous entities which alow sub reports to be displayed per section, as well as giving you the opportunity to manipulate and display data on a per-section basis

CThomp2005Author Commented:
Well, as there doesn't seem to be too much action on this topic, I think I'll sign off the points on it.  Thanks both, nexusnation and Natchiket for your reponses.  Reading them over, I would be confident that any of your suggestions would work, so I'm splitting the points.  They both answer the initial question "can this be done?".

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.
CThomp2005Author Commented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now