Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inputting Data Basics (XP-SP2)

    Hello all. I am a complete novice with access and I am trying to create my own database for training and also for room data sheets. I don't understand how I make a form for inputting data that will put duplicated information into both the parent and child reference.

    I know this is probably a very fundamental issue, but I spend most of yesterday playing around and can't figure it out. Can anyone point me in the right direction?

    Thanks

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

    Re: Inputting Data Basics (XP-SP2)

    In general, you want to avoid duplicating information in a relational database. For instance, if several persons share the same address, you would create two tables:
    - tblAddresses, with a primary key AddressID (for example an AutoNumber field), plus fields necessary to store the address.
    - tblPersons, with a primary key PersonID, fields describing the person, and an AddressID field (number), linked to the AddressID field in tblAddresses.
    Now, if three persons (say, a family) have the same address, they would each have a different PersonID, but the same AddressID. If the family moves, you only have to change the address in the address table once, instead of for each person. If one of the persons moves, that person would get a new AddressID.

    If you provide details of what you want to accomplish, we might be able to gice more specific help.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    As a simplification I need data sheets listing mechanical and electrical equipment for each room in a building. So I have the following tables set up: tblProject - contains project details; tblRoomDetail - contains general information about each room, ie, name, number, area, function, etc; and then several tables for mechanical and electrical equipment. The most troublesome of which will be tblGeneralElect and tblMiscElec. I have linked the Projet table to the RoomDetails table as a 1-many relationship and in turn use the roomID to link the RoomDetails table to each of the Mechanical and Electrical forms. I use the MiscElec table as a subform to my form.

    What I don't understand is how I create a data entry form to input the data so that the information only has to be entered once.

    For example, I would like a form where I enter the room number or room name at the top and then enter the electrical equipment required on the bottom of the form.

    Forgive my stupidity, I understand how to create queries and play with data (at a basic level) once data is in a form, I don't understand how to enter the data.

    I appreciate any light you can shed!

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

    Re: Inputting Data Basics (XP-SP2)

    You can use cascading combo boxes to do this.

    - On the main form, put a combo box whose row source is based on tblProject.
    - Below this, place a combo box that lists the rooms associated with the project selected in the first combo box.
    - Below this, place a subform based on tblGeneralElect, and/or a subform based on tblMiscElec, linked to the second combo box.

    You need a bit of Visual Basic code to tie the combo boxes and subform(s) together, but not much.

    Microsoft has some nice sample databases that demonstrates this together with many other useful techniques. You can download them from Microsoft Access 2000: Building Applications with Forms and Reports - Sample Applications Download and ACC2000: Microsoft Access 2000 Sample Forms Database Available in Download Center.

    Related Knowledge Base articles:
    How to Synchronize Two Combo Boxes on a Form
    ACC2000: Four Ways to Move to a Record from a Combo Box Selection

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    Thanks - you're a hero. I'll try to digest this over the next couple of days.

    I also downloaded your smaple database illustrating many to many relationships! Really helped to clarify things for me.

    Thanks.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    I'm really frustrated. I'm obviously not able to understand the 'convenience' of normalising databases, because I don't understand how to input data into the tables that contain linked information. I have tried the combo boxes and subforms, but don't seem to have them linked correctly. My next hurdle will also be overcoming the many to many relationship linkage as resolved with the intermediate table. I've tried to strip my database down and saved it in Access 97 format, but it is still 101kB stripped down - There is basically nothing but three forms in it and skeleton tables with most fields removed and no records. I don't know how I can get it just that little bit thinner so you can see where I am going wrong.

    Any suggestions?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    Finally managed to strip it enough to get it under 100kB. Any thoughts as to what I am doing wrong?
    Attached Files Attached Files

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

    Re: Inputting Data Basics (XP-SP2)

    See attached version in Access 2000 format, zipped to only 21K. I removed the background graphics from the forms, they take up an enormous amount of space.

    I created two subforms to enter data for electrical and miscellaneous equipment.
    Attached Files Attached Files

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    I forgot about the backgrounds! Told you I'm just learning - there's nothing like drinking from the fire hose.

    From what I gather you have done, the linking of the forms is done using event procedures and VBA code, which I will need to digest. Am I at least understanding the method you're using to link the fields? I'll have to digest the VBA code to understand what is happening. My knowledge of VBA is even less than access, but I am trying to learn.

    Thanks for your help and patience with me!

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

    Re: Inputting Data Basics (XP-SP2)

    You had already created frmProject and frmRoomDetails through the Wizard, I assume. They are bound together through VBA code created by the wizard.

    I only created two subforms sbfRoomMiscEquip and sbfElectrical and placed them on frmRoomDetails. These subforms are linked to frmRoomDetails through their Link Child Fields and Link Master Fields properties, there is no code involved.

    You could, in fact, do it all with subforms, without any code at all. See attached version.
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    I thought I already replied to this post, but it doesn't appear to be here. Anyway, as you rightly figured, I used the wizards to try and link my forms. The problem I am still having is -

    say I am in the project form and add a new project. I then click on the room details form, but I get an error trying to enter a new room as it says that I can't add or change any records because a related record is required in tblProject. Similarly, when I am in my electrical equipment form (as existing) I can add records, but they do not seem to be linked to the room that is selected from the combo box.

    I like the look of your forms with all subforms, so will try to understand it further tonight. Can you explain why my forms are behaving as I've explained above so I can try to understand what I am doing a bit better?

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

    Re: Inputting Data Basics (XP-SP2)

    The forms as created by the Wizard are linked together by code, but that code is incomplete; adding new records is insufficiently taken into account. You would have to add code to set the default value of the link field in the form being opened.

  13. #13
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    If I change the relationship so that it is set to show all records from the Room table and only those records of the electrical table where the fields are equal it seems to make my forms work. Is this just a coincidence that will cause me problems later?

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

    Re: Inputting Data Basics (XP-SP2)

    Try adding new records in the forms, then go back to the tables to see if the appropriate fields have been filled in. Another check is to close, then reopen the form. If the records you entered are still visible, it should be OK.

  15. #15
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Fishburn, Durham, England
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inputting Data Basics (XP-SP2)

    Didn't work. I'll go back to trying to develop the subforms the way you had a few posts ago! I'm becoming obsessed with this. I can't believe I can't make it work!

Posting Permissions

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