Results 1 to 9 of 9
  1. #1
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unable to enter data in a subform (Access 2003)

    Hi

    Am having trouble in being able to enter data in a subform within a mainform. The subform is based on a query which in turn is linked to table2. Table 2 is linked to Table 1. The mainform is based on Table 1. The relationship between Table1 and Table2 is one-to-many. I am able to set the form with subform up OK and the fields are showing in design view just none of the boxes appear in the form view to allow me to enter data. Any clues?

    Cheers

    Cathy

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

    Re: Unable to enter data in a subform (Access 2003)

    Welcome to Woody's Lounge.

    if the subform remains blank, that usually indicates that it's not possible to add new records. Try the following in a copy of the database.
    If you open the query on which the subform is based, can you add new records, and edit existing records?
    If you open the subform by itself, from the database window, can you view / edit /add records?

  3. #3
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unable to enter data in a subform (Access 2003)

    Thanks for getting back. Have tried to add/edit records from the query and subform but am unable to do so. I can view the existing records in the subform but cannot add or edit. (the existing records were entered into the original table). What would be the reason for this and what is the solution?

    Thanks again!

    Cathy

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

    Re: Unable to enter data in a subform (Access 2003)

    Is the query based on more than one table? If so, the tables should be joined, and there should be a unique index on the field on the "one" side of the join.

  5. #5
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unable to enter data in a subform (Access 2003)

    Hi

    The query is based on 1 table (Employee Sales Target). The Employee Sales Target table (composite keys set as EmployeeID and Month) is linked to the Employee Details table (primary key set as EmployeeID), the relationship is many-to-one. The mainform that the subform sits in is based on the Employee Details table. I'm sure it's something obvious I've missed but can't think what!

    Cathy

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

    Re: Unable to enter data in a subform (Access 2003)

    Just to make sure: you can't edit or add records in the query, can you? Could you do the following:
    - Open the query in design view.
    - Select View | SQL.
    - Copy the text you see to the clipboard, and paste it into a reply here.
    Thank you.

  7. #7
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unable to enter data in a subform (Access 2003)

    Hi
    No am unable to edit/add records into the query. Below is the SQL
    Cheers! Cathy

    SELECT tblEmployeeSalesTarget.EmployeeID, tblEmployeeSalesTarget.Month, tblEmployeeSalesTarget.Target AS [Monthly Target], tblEmployeeSalesTarget.Sales AS [Sales Achieved], tblEmployeeSalesTarget.[Commission%] AS [Commission in %], IIf(([Sales]-[Target])>0,[Commission%]*([Sales]-[Target]),"0") AS [Commission Due

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

    Re: Unable to enter data in a subform (Access 2003)

    A Totals query (with Group By etc.) is never editable, because records are aggregated together. Open the query in design view, and toggle off the Totals button on the toolbar, or select View | Totals to remove the check mark from the menu option. Then save the query. You should now be able to edit / add records in the query, and hopefully in the subform as well.

  9. #9
    New Lounger
    Join Date
    Jan 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Unable to enter data in a subform (Access 2003)

    THANK YOU SO MUCH!!

    All works brilliantly now and should have been pretty obvious but sometimes can't see the wood for the trees!

    Much appreciated.


    Cathy

Posting Permissions

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