Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2009
    Posts
    13

    Unanswered: Error msg when trying to enter new data, relationships messed up? (Pics Inside)

    Hello all,

    So this is the first access DB i've ever worked on and im sure i messed up the relationships between each table. What this access DB will do is provide a way for my agents to communicate with clients through access. I want it so that if an entry is entered into clients table, all other tables are updated by adding a new field for that client account number. Here are pics of the error and relationship table.

    Note: I tried removing primary keys on some tables and changeing the relationship but after doing that, my main form wont show anything(text Boxes), just unrelated graphics.

    Click image for larger version. 

Name:	error.png 
Views:	20 
Size:	10.3 KB 
ID:	9303
    Click image for larger version. 

Name:	Relationships.png 
Views:	28 
Size:	49.5 KB 
ID:	9304

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you understand the nature of the realtionships and what they mean
    all your realtionships in this model are one to one.

    IE there can only be one one payment
    there can only be one collection, so your proposed tblCollectors makes no sense.. date of next call, last invoice etc are meaningless.
    you can only have one note,

    I think you re need to think out what your model is trying to represetn, and what you are trying to achieve.

    in the mean time I'd suggest you have a read of the following which should give you some good pointers to revise your data model
    Fundamentals of Relational Database Design -- r937.com
    The Relational Data Model, Normalisation and effective Database Design
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2009
    Posts
    13
    I figured i royally messed up the relationships. Is there a way i can redo the whole database and keep the same form? I dont want to redesign the whole thing again.. If so how would i do that?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I think your problesm are more fundamental than can you kjeep the exisiting form(s)
    I think you need to do a basic redesign of the data model
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    13
    I've read most of the two links you've provided, thanks for the help. Since, i ran a few tests and came up with a relationship between tables that i think will work. Now im facing a problem with the Form. When i have contents of 3 tables on the form, It will not show up. But when i have contents of only two tables on the form, everything shows up fine. I have attached pictures of the new relationships i made, including what the form looks like in form view and design view.

    Note: This form was made from scratch so i did nothing but add the fields from 3 different tables. I did not mess around with the record source of the form.
    However here is the sql code for the form:

    Code:
    SELECT tblClients.[Account Number], tblClients.[Company Name], tblClients.Address, tblClients.City, tblClients.State, tblClients.Zip, tblClients.[Contact First Name], tblClients.[Contact Last Name], tblClients.[Contact Position], tblClients.[Phone Number], tblClients.[Fax Number], tblClients.[Other Number], tblClients.Website, tblClients.[E-Mail], tblClients.[Categorie 1], tblClients.[Categorie 2], tblClients.Slogan, tblClients.Logo, tblClients.[Date Ordered], tblClients.[Due Date], tblClients.[Sales Person], tblClients.Verifier, tblClients.[Verification ID], tblBilling.[Invoice Ammount], tblBilling.Adjustments, tblBilling.[Administration Fee], tblBilling.[Total Due], tblBilling.[Reason for Adjustments], tblBilling.[Billing Address], tblBilling.[Billing City], tblBilling.[Billing State], tblBilling.[Billing Zip], tblBilling.[Attention to], tblBilling.[Purchase Order Number], tblCollections.[Date Last Accessed], tblCollections.[Date of Promise to Pay], tblCollections.[Date of Next Call], tblCollections.[Date of Last Invoice], tblCollections.[Date of Last Fax], tblCollections.[Collection Status], tblCollections.[Date of Invoice Deliquency], tblCollections.[Important Notes]
    FROM (tblClients LEFT JOIN tblBilling ON tblClients.[Account Number] = tblBilling.[Account Number]) LEFT JOIN tblCollections ON tblClients.[Account Number] = tblCollections.[Account Number];
    Click image for larger version. 

Name:	RelationshipsNew.png 
Views:	32 
Size:	48.0 KB 
ID:	9308
    Click image for larger version. 

Name:	FrmShows.png 
Views:	24 
Size:	63.3 KB 
ID:	9309
    Click image for larger version. 

Name:	FrmSupposeTo.png 
Views:	19 
Size:	131.3 KB 
ID:	9310

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Again Im not surprised you are having problems

    traditionally in the Access world you would have one table per form (there are specials cases where you may have a joined table.. however you need to make sure that all columns are mapped to controls assuming you are using bound columns. if you don't know what unbound/bound controls means you are usign bound controls) you can be a bit devious and hide the duplicate controls and use a bit of VBA to set the values as required.

    you would have sub forms for any tables which are children of the master table

    ferinstance if you were modelling a production control system
    to make a part there are numerous steps
    each step requires 3 things other parts, labour & equipment

    so when modelling that you would have a parts table, a steps (or partsteps) table (with a FK [foreign key to parts on part number/partid]), a labour table, an equipment table
    a stepParts table which has a fk to partteps and parts
    a steplabour table which has a fk to partsteps and labour
    a stepequipment table which has a fk to partsteps and and equipment

    you would have a master (top level) form for parts
    a sub form for steps (required to make a part)
    ..3 sub forms hanging off steps for each of the stepsxxxx tables.

    I still think you are a bit to premature to start designing forms. do you need to allocate payments to a specific invoice
    have you defined primary keys for each of your tables
    categorie 1 categorie 2 are suggestions your model is not well thought out, what happens if someone wants categorie3
    ideally the categories should be FK to a category table
    same as sales person

    does a company have an email account or does a person within that company have an email account.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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