not able to put data in a sub form in access 2010

Posted on 2012-09-12
Last Modified: 2012-09-12
i have some problem with a sub form that i don't able to put data in one field,  it's locked.

this is the way how i build the database

i have a category Table that have this Fields

i have a product list table
that have this fields

i have a table invoice
that have this fields

and i have another table for the lines items in every invoice
and it's include this fields
InvoiceDetailsCategory(based on A ID in CategoryT)

and i make for invoiceID a sub form that based on the invoiceId In the Invoice table
and its based on a query that the sql code is like this
"SELECT InvoiceDetailsT.InvoiceDetailsID, InvoiceDetailsT.InvoiceID, InvoiceDetailsT.Product, InvoiceDetailsT.Qty, InvoiceDetailsT.Price, InvoiceDetailsCategoryT.InvoiceDetailsCategory
FROM InvoiceDetailsT INNER JOIN InvoiceDetailsCategoryT ON InvoiceDetailsT.InvoiceDetailsID = InvoiceDetailsCategoryT.InvoiceDetails"

and in the query result i'm able to type a num in the InvoiceDetailsCategory field but in the form this field is closed. why is this?

thanks a lot
Question by:bill201
    LVL 47

    Accepted Solution

    What you need to do is get rid of the INNER JOIN (and everything after it) in the query that is the recordsource for your form.

    Then change the control fo your Category from a textbox to a combo box, and set the RowSource of that combo box to something like:

    SELECT CategoryID, CategoryName from tblCategories  ORDER BY CategoryName

    Then set the ColumnCount to 2, and column widths to 0 and 1 (or whatever it takes to display the CategoryName field).  The controlSource of the combo should be the [InvoiceDetailsCategory] field

    Now, not only will that field be updateable, but you will be able to see the category name, rather than having to memorize the category numbers.

    Author Comment

    good advice thanks a lot

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
    A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now