Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form question (2003)

    I am sure I should know this, but I don't. Sorry for the basic question
    here.

    I am trying to design a simple database for a small construction company to
    track the walkthrough lists for the homes they build. They have a list of
    items called a punch list that they check for each building (unit). There
    are 10 fields for things like subcontractor, item # and description where the information will be the same for each building, so the data would be "static" in the fields and the data entry person does not need to enter data. Then there are some
    fields that we do want the data entry person to enter -like yes/no if the item is ok or not, and a comments field for each item.

    What I want to do is have a data entry form that has 3 pages to it: one is
    the building information (unit #, type of building, square footage, etc);
    the other is the buyer's info (name and contact information). The third page
    I want to have the full check list of all 300 items with the static data showing and some blank fields so the data entry person
    can click on the page, and then enter the comments, etc for each item in the
    list.

    I have the unit and buyer information forms working fine. But,for the life
    of me, I cannot get the punch full list to show up the way I want it to.
    What I want is the whole list to show and then the data entry person would
    fill in the fields for that unit (and the static data would be there already).

    Then the next unit would have the list again (with blank fields) so they can
    enter the data for that unit.

    I am missing a big step here. I can either get the whole list to show and it
    is the same for all the records (any info entered on one record shows on all
    the records), or there is a blank form to enter all the list items (not 300
    items to fill in blank fields, but one record and you have to enter all 300
    items again).

    Does this make sense? I would LOVE to send this db to someone to take a look
    if it would help.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Form question (2003)

    I think you need to describe the structure of your tables. Do you have more than one table?

    Does your three page form consist of a form and subforms?

    If you can post a cut down version to look at that would help.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form question (2003)

    You could take a look at the (very rough) database attached to <post#=561,993>post 561,993</post#> - perhaps it'll give you some ideas to start with.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> There is no need to break lines in a post manually. The browser will do so automatically. You only need to press Enter to start a new paragraph.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form question (2003)

    Why do you have PunchID in tbl_Owner-Buyer?
    Why do you have PunchID and CSI_ID in tbl_Units?
    Why do you have Owner and CSI in tbl_Punch_List?
    Why do you have UnitID, OwnerID and PunchID in tbl_CSI-List?

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form question (2003)

    Because I don't know what I'm doing! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>. I tried creating a punchlist table that contained the foreign keys for the other tables. That didn't work. Then I tried .. well jeesh. At this point I have turned myself in circles.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form question (2003)

    Image reduced in size by HansV

    Attached is a screen shot of my table relationships. Hans' link did give me an idea. The form currently does not have a subform. I would like to have the CIS List (the walk through list) as a subform/page for the data entry.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form question (2003)

    Can you explain a bit about your tables and their relationships? In particular, what is the purpose of tbl_PunchList and tbl_CSI-List?

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form question (2003)

    The tbl_CSI-List is the list of items on the walkthrough checklist - the 300 items that are checked off when the house is sold. The list has the description of the item, with fields for who the subcontractor is, comments for improvements, etc. Originally, I thought I'd call it a punch list and have a punch list table that would link the csi list to the unit and owner. Each unit will have one owner. Each unit will have one checklist (CSI List). We want to be able to run queries by subcontractor (make a list of what each sub needs to fix), lists of items outstanding by unit, and also a list of which subcontractors have the most issues and which items are most prone to needing repair.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form question (2003)

    If I understand your description correctly, you should put the fixed information (fields that cannot be entered/edited by the user) in tbl_CSI-List, and the fields that will be entered/modified by the user in tbl_PunchList. For example, the description of an item belongs in tbl_CSI-List, but user comments belong in tbl_PunchList.

    Will there be one set of records for a given Unit in tbl_PunchList, or can there be multiple sets, for example because different people each fill in a complete list?

    (It's very late for me, so I'm knocking off for now. Hopefully someone else will jump in)

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form question (2003)

    oh! That makes sense. Ok. There will be one set of records for each unit. One complete list for each unit. Thanks for your help so far. I will fix the fields as you suggested. It's not too late here (8pm), but I'm ready for a beer so I'm done for tonight. I'll tackle this more tomorrow in the cold light of day. Thank you.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form question (2003)

    I think that your data design will be OK once you have
    - moved the "fixed" fields to tbl_CSI-List
    - moved the "variable" fields to tbl_PunchList
    - removed the superfluous ID fields from the tables.
    - set Enforce Referential Integrity for the relationships.

    You don't really need the PunchID field; you can set the combination of UnitID and CSI as primary key in tbl_PunchList.

    See screenshot below (I used slightly different names, but the idea is the same)

    The central form would be a single form based on tbl_Units.
    This form would have Allow Additions and Allow Deletions set to No.
    This form would have a continuous subform based on a query that combines tbl_PunchList and tbl_CSI-List.
    You'd have a locked and disabled text box bound to the description field from tblCSI_List, formatted to look like a label.
    The subform would also have Allow Additions and Allow Deletions set to No.

    You'd have a separate form for unit management, with command buttons for adding a unit and deleting a unit.
    When the "Add Unit" button is clicked, a new record in tbl_Units is created, and a set of new records in tbl_PunchList, with the UnitID of the new record and all CSIIDs from tbl_CSI-List. You can model this code on the code in the sample database from the post I referred to in my first reply.
    When the "Delete Unit" button is clicked, the Unit record is deleted and the associated PunchList records. If you have set Cascade Delete Related Records for the relationship between tbl_Units and tbl_PunchList, this will be automatic, otherwise you'll have to delete the records in tbl_PunchList before you can delete the record in tbl_Units.

  12. #12
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form question (2003)

    Thank you so much, Hans. I will make these changes and will let you know how it goes. Your patience is soooo appreciated!

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form question (2003)

    I guess I don't understand the Allow Deletions/Additions being set to No. When I did that on the single form based on tbl_Units, the form fields don't show at all in the form view. I followed your directions Hans, but I must still be missing something. When I tried to make the text box bound to the description field from tbl_CSIList, the subform fields also disappeared. I haven't gotten to making the separate unit management form with command buttons because I'm still stuck on the first part. Sigh. I feel so dense!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Form question (2003)

    Don't worry - we'll get there.

    If there are no records yet, a form will be blank if Allow Additions is set to No, so it's not surprising that you don't see any fields. For the moment, you can set Allow Additions to Yes to see if the form looks OK.

    The reason I suggested to set Allow Additions to No is that when a new record in tbl_Units is created, you want to create the corresponding records in tbl_PunchList at the same time. And you don't want the user to be able to add records to tbl_PunchList manually. This is easiest if you handle creating a new record separately, although it could be done on the form itself - the example I originally referred to demonstrates how to do that.

  15. #15
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form question (2003)

    Ok. That make sense. Thanks. I'll keep plugging away at it.

Page 1 of 2 12 LastLast

Posting Permissions

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