1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007 - multi item form (continuous form) trouble adding records

Discussion in 'Business Applications' started by draytond, Aug 8, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    My multi item form isn't letting me add new records, only update and delete current ones.

    My guess it that this is because the form is based on a multi-table query. That's fine, I can make a new form specifically for adding new records, but I'd like to be able to salvage this form if possible. Is there any way to either force this form to accept new records in the bottom row, or is there any way to get rid of the "add record" bottom row completely so it's not misleading users into thinking they can add records here?

    Thanks in advance.
     

    Attached Files:

  2. Rockn

    Rockn

    Joined:
    Jul 29, 2001
    Messages:
    21,334
    Make the form so it is not updateable in the form properties.
     
  3. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Why create another form and make users open & close it?
    Why not modify the current form's Recordset so that you can add records
     
  4. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Ah ha! Found it - turned off "Allow Additions". Thanks again Rockn!
     
  5. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Well OBP that would be ideal, but this form is based on a query that's pulling data from like four tables. So I'm not sure how to allow users to add records using this form unless I do a total redesign of my underlying tables. Of course, I'm relatively new at this, so maybe I'm missing something? I'd be psyched if I could get this form to allow me to edit, delete, AND add records.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Why is it taking data from 4 tables?
    If it is using those tables to store ID fields you should use Combo boxes to select the data from the other tables, not include them in the query.
    If the data is actually going in to 4 separate tables the form should be split up in to Form/subform to allow adding data.
     
  7. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I have to go now, I am sure Rockn can explain how the form should work.
     
  8. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Ok, it's actually just taking it from 3 places: one TABLE that lists out all the data on the individual funders funding the projects, one QUERY that is a "funders full name builder" so my combo box displays the funder's full name and their "list as" name, and a linking TABLE that is where I keep track of the amounts of money being associated with each project, which has a foreign key to who's providing that money.

    So I guess it's more accurate to say this form is based on a query that's based on another query and two tables, mostly as a result of wanting my funders to be selectable by their full names + their "list as" name in a combo box. It would be easier if I wasn't having to pull the first and last names into a "full name" using a query, but I was told not to do this kind of thing in a table, and instead use queries to perform such tasks.
     
  9. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    here are some pictures to illustrate what I'm trying to say
     

    Attached Files:

  10. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Ah, I think I see what you're getting at. An additional complicating factor is that I need the form to be filtered down to specific records, hence the need for a query in the first place. This form is displaying funding sources for one specific project at a time.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    How is the "Project" selected for filtering?

    Can you have more than one funder per Project?

    Can you have more than one Project per Funder?

    Which do you consider first, the Project or the Funder?
     
  12. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    This is a project database, so the first thing the user sees after the switch board is the project form, which acts as sort of a menu to the different info that can access on that project. I haven't gotten around to building in search/filter capabilities yet, but that's in the works.

    Each project can indeed have multiple funders, and each funder can indeed fund more than one project. I made a linking table to get the two to cooperate.

    What do you mean when you ask "which do you consider first?"

    And don't worry, I'm fairly satisfied with having a button at the top of the continuous form to add a funding line item (see screen shot), so if you want to just drop this you want. Although it would be good to know how to handle this situation in the future, if you do decide to pursue it. Otherwise, my work-around method seems to do the trick.
     

    Attached Files:

    • add.JPG
      add.JPG
      File size:
      148.6 KB
      Views:
      652
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    "Each project can indeed have multiple funders", in this case you should have a Many to Many table for the Funds, which your image in post #9 does not have.
    I would actually used a Tabbed Mainform for your Projects, I have participated in quite a few project databases and tabbed forms are very efficient.
     
  14. draytond

    draytond Thread Starter

    Joined:
    Jul 20, 2011
    Messages:
    220
    Tabbed form? I had no idea Access had tabbed forms!!!! That would have been perfect!! Arrrrgh... Thanks for bringing this to my attention.

    But since when are many to many relationships ok? I thought the whole point of a relational database and having things like primary keys was to break down everything into a one to many relationship, or else you'll end up with all sorts of terrible data troubles?
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You are likely to have problems with your current setup.
    The point of a Many to Many table is to contain data from 2 One to Many tables, in your case
    ProjectID
    FunderID
    Fund data fields
    In that way you can have as many Funders funding as many Projects as you like and those projects can have as many funders as you like.
    To see a simple tabbed form look at this thread
    http://forums.techguy.org/business-applications/1011147-solved-access-database-design-form.html
    Post #3.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1011446

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice