Solved

Populating a list box on a continuous forms form

Posted on 2008-06-16
5
1,101 Views
Last Modified: 2010-04-21
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!
0
Comment
Question by:CThomp2005
  • 2
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
nexusnation earned 250 total points
ID: 21800172
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.
0
 
LVL 17

Assisted Solution

by:Natchiket
Natchiket earned 250 total points
ID: 21800760
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

0
 
LVL 3

Author Comment

by:CThomp2005
ID: 21827995
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.
0
 
LVL 3

Author Closing Comment

by:CThomp2005
ID: 31467827
Thanks again for your time and input!  Hope to run into you guys again during my next quandry.
0
 
LVL 12

Expert Comment

by:nexusnation
ID: 21829056
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.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now