Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22

    Unanswered: Editing data in a form (linked to a query)

    Hi All,

    In trying to follow the principles of good database design, I have inadvertantly caused problems for myself. He he - best way to learn, I say!

    OK... I have split out a single table (containing duplicate info e.g. multiple employees for same company). I now have 3 tables: contact table, company table and agenda table (where individual contacts choose and rate the agenda options for an upcoming event I am running).

    My principal form now is a query based on all 3 tables.

    Everything displays as it should - but I now can't edit the records in the form or query.

    Is there a way around this? When it was all one table, no probs. Have I over complicated what, in essence, seems to be a fairly simple process?

    Look forward to your comments.

    John

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Big John
    Hi All,

    In trying to follow the principles of good database design, I have inadvertantly caused problems for myself. He he - best way to learn, I say!

    OK... I have split out a single table (containing duplicate info e.g. multiple employees for same company). I now have 3 tables: contact table, company table and agenda table (where individual contacts choose and rate the agenda options for an upcoming event I am running).

    My principal form now is a query based on all 3 tables.

    Everything displays as it should - but I now can't edit the records in the form or query.

    Is there a way around this? When it was all one table, no probs. Have I over complicated what, in essence, seems to be a fairly simple process?

    Look forward to your comments.

    John

    Generally speaking that is because when attempting to add/update records Access cannot EXACTLY determine which records to update (and how to add new records) because of key ambiguities ... Look at redesigning your key structure or perhaps do the updates/adds yourself thru VBA code ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    In order to be able to update a row, or rows, in a query then the SQL engine needs to be able to uniqeuly identify the original row. To do that ensure the primary key for each table in the query in which you want to make changes is in the query.

    BTW I'd of thought you probably need at least another table to handle what agenda itesm a user has selcted, and possibly another to store how the users has rated each agenda item.

    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    I'm a bit of a newby and have only just got my head around the basics of Access at the moment - though I would love to get into VB etc. in time.

    For now, as a quick fix, am I better off placing all the data in one table? It's just that I need the database to be functional fairly quickly. It wasn't until I tried to be too clever that I encountered any problems. ;-)

    Cheers,

    John

  5. #5
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    The main table is the contacts as they individually are my primary concern - i.e. I want them as individuals to attend my event rather than the company.

    So, I have a relationship from the ID in my contact table (primary key) linked to a number field in the company table, based on a one-to-many relationship (one company per contact).

    Likewise, I have also linked my contact to the agenda table, again by the same contact ID (primary key) to the agenda table by an agenda field (number). The agenda table contains a series of fields where the contact prioritises their choices of potential agenda topics based on a score of 1 to 6 (so the fields are number fields). This link is a one-to-one i.e. one contact will have one set of agenda choices.

    Is my thinking sound so far?

    My aim was to provide a form that would contain all of the info for all 3 tables so that other people could update records when our contacts respond. To do this, I set up a query which pulled all of this info together. However, now I can't edit it in query or form view and have to go back into the main tables - very time consuming as I have to load the various subdatasheets to enter data.

    There has got to be a better way, but I have no idea whether I am on the right lines or not.

    Any help is greatly appreciated.

    John

  6. #6
    Join Date
    Jan 2004
    Location
    Berkshire, UK
    Posts
    22
    BTW - I've just been looking at other threads/forums for inspiration. I just wanted to clarify, I'm a 33 year old marketing manager running an event... this database is NOT homework.

    On closer inspection, I think I'm getting some of the fundamentals wrong. But my head is in such a spin, I can't see the woods for the trees.

    If anyone actually wants to see the database structure (with data removed for obvious reasons), I have no problem sending it to you. In the meantime, I'm going to by myself a book and go back to the beginning.

    All the best.

    John

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Have a gander here while you're at it. I'm kind of surprised this link didn't pop up in this thread yet...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  8. #8
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Teddy
    Have a gander here while you're at it. I'm kind of surprised this link didn't pop up in this thread yet...
    Ted,

    I just don't have those links handy ... If I need a reminder, I just look over my shoulder to the 5 rules of data normalization ... Hint: What a great FAQ link ...

    John,

    I'm not qualified to help you on this problem cause I do multi-user disconnected DBs where the forms have tons of ADO manipulation code behind them (including saving) and for that matter all my reports (except for 2 or 3) pull their own data and print ... No stored procs/queries here ...
    Last edited by M Owen; 02-15-06 at 16:06.
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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