Results 1 to 13 of 13
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Table over several forms (2000)

    Hi,
    Seem to be having a bad day with Access (again!).
    I've got a relatively 2 dimensional form that I'm trying to replicate into Access from Excel.
    It has quite a lot of data to fill so I've placed it within two forms, with the idea that the user can go effectively from top to bottom of the form on one screen using an open form control, without having to scroll down. The data to be entered is entirely at the users discretion and therefore a limited amout of it is going to be compulsory.
    The idea is that the use fills out this form and then clicks 'DONE' and a report is printed in the same format as the original excel sheet, obviously redesigned in Access.
    Not too sure whether I've got this one right. The relationships I suspect are wrong.................probably one of many things <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30>
    Attached Files Attached Files

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

    Re: Table over several forms (2000)

    Your database contains lots of tables and lots of forms/subforms. It's a bit much to expect us to analyze the entire database. So could you try to narrow it down to one or two specific questions? Thank you.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table over several forms (2000)

    Oops <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> ,
    I've worked out that the relationships seem to be the problem.
    I have a Main Form, say FormA which links to FormB on a One to One relationships. From FormB I need to link to 8 subforms with a One to Many relationship.
    All tables have an IDNumber (Autonumber) as a primary key, instead of specific data (i.e. Reference numbers)
    This seem to be where the problem lays, and I suspect I've set my tables up incorrectly. Going from previous databases that you've helped me set up, I believe that I need to link the tables to the main table(FormB) through a common expression. With all of the tables having an autonumber as a primary is this going to be difficult? I'm not too sure of the best way to link 8 tables to 1.
    The annoying thing is, its a relatively simple form and I literally need to fill out data with no complex reports. I will be producing a report displaying the data in a format that has been standardised nationally, taking this from the data entered on the main forms.

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

    Re: Table over several forms (2000)

    You should not link the tables on an AutoNumber field on both sides. The AutoNumber field FISTID in TblFistMain is OK, that is the one that will be used to link to all the other tables.

    All of the subordinate tables (that is, the tables used for the multi-record subforms: TblMainHearing, TblFixedFees, TblConferences, TblMedia, TblTravelDate) should have a field FISTID of type Number (Long Integer). This must NOT be an AutoNumber field, and it must NOT be the primary key. You can keep the AutoNumber fields in these tables, if you like. Remove all the existing links, and link TblFistMain to the subordinate tables on FISTID.

    You don't need a field FISTID in the lookup tables (that is, the tables used to populate combo boxes: TblCourt, TblCounsel, TblNumbers (?), TblCodes, TblCaseType, TblOffenceClass). These tables should be linked by their primary key to TblFistMain, on the appropriate field, i.e. Court to Court, AdvocateID to Advocate (should be a Number field), etc.

    Another remark: several of your tables have the primary key and another non-primary index on the same field. Having two indexes on the same field is superfluous and takes up unnecessary space in the database. So you should remove the non-primary index.

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table over several forms (2000)

    Sorry Hans, still having trouble with this one.
    I now have:
    TblStartMain with CrestID as Primary key, linked to TblFISTMain (enforced referential) which also has CrestID (not primary). tblFISTMain also has a primary FISTID(Autonumber) linked to TblMainHearing (enforced referential) to FISTID in this table (non primary).
    TblFistMain is also linked to TblConference, TbleFixedFees, TblMedia, TblTravelDate, each of which have their own FISTID. I've attached a zipped image for reference.
    The forms still are not flowing smoothly (they don't work!)..............presumably I've messed up the relationships again!
    Attached Files Attached Files

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

    Re: Table over several forms (2000)

    This is what catches my eye:
    1. The Advocate field in TblFistMain should be a number field, and joined to AdvocateID in TblCounsel, not to Name.
    2. The CaseType field in TblMainHearing should be a number field, and joined to CodeType in TblCaseType, not CaseType.
    3. FeeReference and FeeRef are the primary keys in TblFixedFees and TblCodes. You now have a one-to-one relationship on these fields. If you want that, you should combine the tables into one table; otherwise the join is incorrect. I cannot judge that, since I don't know what the purpose of these tables is.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table over several forms (2000)

    I seem to be having another issue at the moment, now that this bit is completed.
    When linking master fields with child fields so that all the forms and subforms relate, is it necessary to display the actual linking field in the form?
    I seem to be having a problem when you go into the properties of the form and select the master & child fields that link the sub to the main. The FISTID that links alot of the tables is not displayed (as I haven't displayed it on the form, although it is in the related table for the form), however if I actually select the field from the list (in the relating table) and place it into the form, then I am able to select the field to link in the child to master. I think that makes sense! Anyway, this way works with no problems and all the forms & subforms run correctly.
    I know that I could insert this field and then format this to appear the same colour as the background, but this seems a bit of an odd thing to do in access.

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

    Re: Table over several forms (2000)

    It shouldn't be necessary to have a control bound to the link field on either main form or subform, as long as the field is part of the record source of each.

    However, there are situations in which you do need such a control. It isn't necessary to fiddle with background colours etc. if you don't want to see the control. Just set its Visible property (in the Format tab of the Properties window) to No.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Table over several forms (2000)

    When linking fields are in the form's underlying recordsets, it is not necessary for them to be controls on the forms. If you do choose to put the fields on the forms, you can set their visible property to No, which hides them completely. I generally prefer this option so that I can keep track of what I'm working with and so that I can manipulate those hidden controls and use their methods and properties in code. I generally set the backcolor of my "hidden" controls to yellow so that I can see in design view whether I have any such things on the form.
    Charlotte

  10. #10
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table over several forms (2000)

    When setting the Master and child links I get the message 'Object Variable or With block variable not set'. What does this mean?

    I have changed the relationships as detailed in the attachment. This is so that each entry in TblStartMain can have multiple advocates. Each advocate can then have multiple Main Hearings, and each advocate can also have multiple conferences, media, etc that relate solely to them.

    The majority of tables are now linked via AdvocateID, and not FISTID.
    FISTID is only used to link TblStartMain and TblFISTMain.

    I'm at a loss as to where I've gone wrong! <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15>
    Attached Files Attached Files

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

    Re: Table over several forms (2000)

    If I understand correctly, you have a many-to-many relationship between TblFistMain and TblCounsel: one advocate can participate in several FistIDs and several advocates can participate in one FistID. (What do you call these things? Fistings?)
    A many-to-many relationship is implemented by creating an intermediate table tblFistCounsel, with fields FistID and AdvocateID (number, long integer); the combination of these fields is the primary key. Each record in this table represents an advocate participating in a FistID. The intermediary table may contain more fields if necessary - fields that are unique to the FistID / AdvocateID combination.
    The intermediary table will be linked to TblMainHearing, so that each hearing is linked to both a FistID and to an AdvocateID. Similarly for the other four subordinate tables (TblConferences etc.)

    Note: I don't understand your link between TblFixedFees and TblCodes on FistID. Which role does FistID play here?

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Table over several forms (2000)

    FIST stands for Fees Information Sheet. Its one of those horrible corporate acronyms!
    What I am trying to do is:
    For each record in TblStartMain there can be multiple advocates, each advocate can then have a multiple FISTS (TblFistMain), and for each FIST, that advocate can have multiple Main hearings, Conferences, Fixed Fees, Media & Travel dates (all with corresponding tables). I suspect that I should have the relationship of TblStartMain One-to-Many to TblAdvocate.
    Original TblCounsel was solely a look up table, similar TblCaseType etc. I suspect that I shouldn't have it involved in the relationships as it is. Rather linking all the Tables to TblFistMain via an additional AdvocateID. But I might be wrong!
    The Advocate must have each of the above relating solely to him/her, and all of these relating to TblStartMain through CrestID.
    Eventually the idea is that a report will be filled out detailing what fields have been filled and reproducing a ghastly looking excel workbook that's overly complicated. This is what is called the FIST <img src=/S/wink.gif border=0 alt=wink width=15 height=15>

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

    Re: Table over several forms (2000)

    I think it would be a good idea to take a step back. Stop designing tables, queries, forms and reports for the moment. Instead, go to ACC2000: "Understanding Relational Database Design" Document Available in Download Center, download the document and study it. Then, take a large sheet of paper and start listing the data you need in your database. Use the methods from the document to design the data structure. When you're finished, start implementing it.

Posting Permissions

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