Add records in a query-based datasheet form

Posted on 2006-05-15
Last Modified: 2008-02-26
In an Access project (Access front-end, SQL Server back-end)...

I have created a datasheet form based on a query that queries two tables.
This datasheet form is then used as a subform on another form.
I have designated one of the two tables as the Unique Table so that users can edit data within the subform.

Is there a way to allow users to also create new Unique Table records via the subform?

The subform's properties are set to allow additions, deletions, and edits, and the subform type is set to Updateable Snapshot.  Thought I'd done this before, but just can't duplicate it...

Question by:Galisteo8
    LVL 34

    Accepted Solution

    It sounds like you are trying to create what SQL refers to as an updatable view.  This is only possible under certain conditions.  If you look up "CREATE VIEW" in books online and find "updatable" you will find the restrictions.

    ... it is updatable only if the following conditions are satisfied:

    The select_statement has no aggregate functions in the select list and does not contain the TOP, GROUP BY, UNION (unless the view is a partitioned view as described later in this topic), or DISTINCT clauses. Aggregate functions can be used in a subquery in the FROM clause as long as the values returned by the functions are not modified. For more information, see Aggregate Functions.

    select_statement has no derived columns in the select list. Derived columns are result set columns formed by anything other than a simple column expression, such as using functions or addition or subtraction operators.

    The FROM clause in the select_statement references at least one table. select_statement must have more than non-tabular expressions, which are expressions not derived from a table. For example, this view is not updatable:
    SELECT GETDATE() AS CurrentDate,
           @@LANGUAGE AS CurrentLanguage,
           CURRENT_USER AS CurrentUser
    LVL 8

    Author Comment

    I need to re-phrase the way I have this subform set up...

    Specifically, I have a query (or view) called qryBrandLots, and the datasheet subform's RecordSource is
    SELECT * FROM qryBrandLots ORDER BY LotNum

    Here is what qryBrandLots looks like:

        charindex('-', dbo.lot.lotnum + '-') - 1) AS Shipment,


        Payback = CASE WHEN dbo.LOT.Payback = 0 THEN NULL
        ELSE dbo.LOT.Payback END,

        CheckDate = CASE WHEN dbo.AUCTION.RelistedYN = 1 THEN NULL
         ELSE dbo.LOT.RinnerCkDate END,

        CheckNum = CASE WHEN dbo.AUCTION.RelistedYN = 1 THEN NULL
         ELSE dbo.LOT.RinnerCkNum END,


        PaidStatus = CASE WHEN dbo.AUCTION.PaidStatus = 1 THEN 'Yes'
         ELSE '' END,

        Refund = CASE WHEN dbo.AUCTION.RefundYN = 1 THEN 'Yes' ELSE
         '' END,

        Relist = CASE WHEN dbo.AUCTION.RelistedYN = 1 THEN 'Yes' ELSE
         '' END

        dbo.AUCTION ON
        dbo.LOT.LotNum = dbo.AUCTION.LotNum

    Is CASE considered an aggregate function -- Would the presence of CASE statements in the query prevent updating?

    OR... Would the fact that the subform queries qryBrandLots for its RecordSource, instead of just tapping qryBrandLots directly, be a problem that prevents updating?
    LVL 34

    Expert Comment

    by:Brian Crowe
    I believe that CASE would present a problem since there would be no way to determine how to update the source.
    LVL 8

    Author Comment

    I am thinking, then, that the main form should have a command button ("Add Lot") that will open a popup or modal form tied directly to the Lot table in which the user actually creates the new Lot record.  Upon closing this modal form, a) a new Lot record is created in the Lot table, and b) the main form and subform are updated.

    Can you think of a simpler way?  I'm trying to make this as streamlined as possible for the users (who want this subform to look-and-feel like an Excel spreadsheet), and as non-code-heavy as I can for me.
    LVL 8

    Author Comment

    I've proceeded with the modal form approach, rather than adding records directly in a row of the datasheet, since that does not seem possible in my situation.  BriCrow, I tend to believe you are correct -- that the use of CASE in the query does prevent me from doing that.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    728 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

    19 Experts available now in Live!

    Get 1:1 Help Now