Limit Combobox list

[gw2013]

Hi,


I'd really appreciate some help with the attached sample.


I want to use the database to record the number of hours employees spend on projects.


Each project will have various stages (all specified in the Projects and Project Stages tables). Then in the ProjectHours table, I want to record the number of hours spent on the Project Stages for a specified date.


So in the form frmHOURSINPUTmain, the employee will select their name and proceed to select the Project, then the Project Stage, (e.g. Project 1, stage 1b), then enter the workdate and the number of hours.


My question is, when I select the Project, can the stages be filtered to only show the stages for that project. it currently shows all possible stages, regardless of which project has been selected.


Maybe my tables need to be set up differently? Anyway, I'd appreciate any help.


Thanks.

[Micron]

Your design is not correct IMHO. Move the combo box for project to the form header and have the subform reflect the choice that is made from it. You don't usually put combo boxes in a data sheet like this. Research cascading combo boxes and when you find a source you like, use the info to have the project combo populate based on the chosen name, then the subform records populate based on the chosen project. You will need the AfterUpdate event for each combo to trigger a requery on the project combo and datasheet.

[RuralGuy]

Before I get too far along, I'd like you to review this link: http://access.mvps.org/access/lookupfields.htm

[gw2013]

Thank you both for your replies.

Yes, I knew I was on the wrong path

I will have a rethink and will probably back with some questions...

[RuralGuy]

You're very welcome and we'll be around.

[orange]

I agree with the others. I am attaching a preliminary relationships diagram for consideration.


Good luck with your project.

[gw2013]

Hi Folks,


I've had a rethink.... but haven't got too far.


Maybe I'll describe in more detail what I'd like to do and then get your feedback?


So the idea is this will be a form filled out at the end of a day.


1. User opens form,
2. Selects their name,
3. In the subform(?), they need to record the hours spent on various projects so they need to select the Project, which then gives them the list of stages of that project which they then select from and then enter their hours. Each entry will default to today's date.
4. Ideally, as they fill out their hours, there would be a field at the bottom updating to show total hours entered for the day.


So I guess it's like a timesheet of sorts?

In one of the earlier posts, it mentioned moving the Project Name to the header and selecting it from there, but if there are many different projects for hours to be recorded, how might this work?


I think I've just hit a wall with it and need a bit of guidance.


Any help greatly appreciated...

[RuralGuy]

You're on the right track with the Project Name selected in the header. The user simply *must* go back up to the header and select another project.

[Micron]

In one of the earlier posts, it mentioned moving the Project Name to the header and selecting it from there,

I think that was me and the explanation was posted. To clarify, user picks their name from name combo and this action (AfterUpdate) populates the project combo with only their projects. If you wish, make project status part of the deal (e.g. if hours cannot be charged to a completed project, prevent that status from appearing in the resulting list). User selects a project and the AfterUpdate of the project combo triggers a requery of the subform to show only the specified project belonging to the user. User enters hours on the subform. You decide if the subform is presented as a datasheet or single form. I would opt for datasheet rather than deal with subform record navigation buttons, plus it will show more than one record at a time if user wants to see a more expanded list at once. Totals can be shown by using an unbound textbox in the form footer or header that has an expression that sums the hours field.

[gw2013]

I think that was me and the explanation was posted. To clarify, user picks their name from name combo and this action (AfterUpdate) populates the project combo with only their projects. If you wish, make project status part of the deal (e.g. if hours cannot be charged to a completed project, prevent that status from appearing in the resulting list). User selects a project and the AfterUpdate of the project combo triggers a requery of the subform to show only the specified project belonging to the user. User enters hours on the subform. You decide if the subform is presented as a datasheet or single form. I would opt for datasheet rather than deal with subform record navigation buttons, plus it will show more than one record at a time if user wants to see a more expanded list at once. Totals can be shown by using an unbound textbox in the form footer or header that has an expression that sums the hours field.

Thank you very much.

Going to have a go at this later and will hopefully report back with some success!

[gw2013]

Hi All,


I've had a go at the cascading combo boxes and they appear to be working ok.


How do I enforce the choices made in the combo boxes reflect into the subform?


I've set a filter for the subform so it only displays records for the current date as this formis to be filled out on a daily basis so no need to see past entries.


How I'd like this to work is that the user selects the criteria in the header, then fills out the relevant hours in the subform. So they may have hours to put on various projects/stages. Will it be possible to keep everything they've entered for a day visible, or will the subform reset when they select a different project/stage?


Thanks again for any help...

[Micron]

Your table normalization is not correct IMHO. Think of ProjectStages as a list of stages a project could be in. ProjectID should not be in stages - stageID should be linked to projects via stageID.
You don't need stageId in hours either. You get that by linking to projects after you take care of the previous.
This should probably be fixed before proceeding because it will mess up your queries/forms.

[gw2013]

Your table normalization is not correct IMHO. Think of ProjectStages as a list of stages a project could be in. ProjectID should not be in stages - stageID should be linked to projects via stageID.
You don't need stageId in hours either. You get that by linking to projects after you take care of the previous.
This should probably be fixed before proceeding because it will mess up your queries/forms.

Hi Micron,

Thanks for your reply.

Not sure I get you.


Each project will have it's own set of unique stages. Wouldn't the ProjectID have to be in ProjectStages to relate those stages to the correct Project?

[Micron]

reverse that thinking. One of the details on the project is the stage, and you get that from stages. You don't put the project id in stages.

Try this if you need more clarity:
table about cars: Make, Model, Colour
table about colour options: red, green, blue. Would you put Ford in this colour table?

[gw2013]

Micron,


The car example makes sense but i don't think it's the same context?


In reality, the Stages table will contain the Stage Name along with other pieces of information. Maybe my example was over-simplified?
The Stages table won't just contain the Stage Name, it will have other stage-specific information as well such as Start Date, Estd Finish Date, Cost Information etc which are specific to only one Project in the Projects table. So one Project will have many stages which are completely specific to that Project.
If the Stages table has no reference to the ProjectID, how will the stages be correctly related to their specific Project?


Are you ready to throw something at the screen yet :-0