Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Morning Loungers

    Quick question and probably something really easy I'm just missing. I'm putting together a quick form to keep track of time spent on tasks so I have 3 combo boxes for area, project and allocation and these can be repeated up to 10 times on the form but I'm looking for these only to be active/visible if the preceding ones are filled in so pretty much the form would be

    Area Project Allocation

    and if these were filled in then

    Area2 Project2 Allocation2

    would appear. I've been messing about after update for Allocation, Allocation2 etc with

    If Not IsNull(me.allocation) then
    me.area2.enabled=true
    me.project2.enabled=true
    me.Allocation2.enabled=true
    me.area3.enabled=false
    me.area3=null


    etc, but without too much success and just seems a long way for a short cut, so if anyone could point me in a better direction this would be much appreciated.

    Cheers

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you are repeating a set of controls 10 times, that usually means that your data is not properly normalised. You probably should have the data for area, project and allocation in a separate table, displayed as a subform.
    Whenever you create a record another blank line will appear for the next. If you make all 3 fields required, you could not move to a new record without completing the current one.
    Regards
    John



  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    You haven't given us any information about your table design that the data is going to be stored in, but I suspect it isn't normalized. Are all of these being stored in one record?

    My approach would be to use a subform with just three combo boxes, and add a new record for each allocation - the subform would link on date (and person if more that one person uses the form).
    Wendell

  4. #4
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah sorry about that, thought I had given more info on that table than I had, just now it is a pretty large table with everything apart from what will populate the combo boxes on it, though I'm still pretty much in my 'messing around' phase. I think I want to keep as much of this information as one record as possible and was planning on just using tabs in the form to seperate the avaliable time from the allocated time if that makes any sense. In the first tab I have Team/CommandName, Period/Month, TotalGrossFTE(just total hours available), Holidays, Sick etc and finally ProductiveFTE so that I get the actual amount of hours available for that month, i.e.

    ProductiveFTE=TotalGrossFTE-Holidays-Sick and so on

    then in the next tab I was looking to have how those hours were used and this could be on just one area/project or up to 10 or more per month(this is why I had the 10 controls, not too sure how else to keep them all together) and also the total of the Allocated hours should also equal the ProductiveFTE field. Hope this makes some sort of sense.

    Sorry for the rambling explanation and thanks for your patience with my terrible Access skils.

    Cheers

  5. #5
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, so I've changed this all over to separate tables and created a main form with a few subforms pasted on top of each other that are visable depending on what is selected on a Type combo box and that's all good and very much better than what I had but now I have 1 combo box that doesn't work anymore. I had a Project combo that was populated depending on what was in the area combo box and that's stopped working now. In the row source I had

    SELECT AreaProjectTbl.ID, AreaProjectTbl.Area, AreaProjectTbl.Project FROM AreaProjectTbl WHERE (((AreaProjectTbl.Area)=Forms!DataSheetsDeployment Frm!Area));

    With AreaProjectTbl holding a list of Projects and what area's they fell under and Area the name of the combo box that ran off a simple table with a list of area's, I've tried changing the DataSheetsDeploymentFrm to DataSheetsDeploymentSub_Frm as it is on the main form but still no luck, I get an empty drop down or a message box asking for the area that then never changes.

    Any help with this would be much appreciated

    Cheers

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you want to refer to a control on a subform the syntax is:

    Code:
    =Forms!MainFormName!subFormControlName.Form!fieldname
    In your case I think this would be (but it depends on what things are called) :

    =Forms!DataSheetsDeploymentFrm!DataSheetsDeploymen tSub_Frm.Form!Area

    NB The name of the subform control is usually the same as the actual subform contained within it, but may not be.
    In the Main form (design view) click once on the subform, and look at the Properties sheet. On the Data tab, the Source Object tells you the name of the actual subform, but the Name on the Other tab tells you its name.
    Regards
    John



  7. #7
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for that, thought I'd missed something. Still having some problems though, when the Area combo is blank I get a message box asking for a value for Area and if I enter this it fills correctly with just the projects for that area but if I select an area the projects combo always has nothing to choose from. I've tried redoing this a few times, both using the combo wizard with [Forms]![DataSheetsDeploymentFrm]![DataSheetsDeploymentSub_Frm]![Tax] added in the query builder and by typing out. I assume that with the value box that appears working properly that I've messed up somewhere but the values in the tables match properly so I'm kind of lost.

    Any ideas?

    Cheers

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    As I have discussed in this thread it is very hard to debug linked combos where the row source is an sql string, because you can only access the string from Design Mode, and in that mode you can't test it.
    Use a saved Query instead. By all means revert back to an SQL string once you fixed the problem if you want.

    I can't tell what the problem is from here. Can you post a sanitised version of the db, if you can't fix it via a saved query.
    Regards
    John



  9. #9
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok, stupid question but how do I upload a sanitized version?

  10. #10
    Lounger
    Join Date
    May 2010
    Location
    Glasgow, Scotland
    Posts
    38
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sorry ignore that last question, I don't have a copy of the DB on this computer so I will attach one later on.

    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •